PostgreSQLのjsonb型でJSONパス式(JSONPath)を使う

本記事は PostgreSQLのカレンダー | Advent Calendar 2023 - Qiita の10日目の記事です。

はじめに

PostgreSQLのjsonb型では、SQL/JSONパス式による操作が行えます。

JSONパス式(JSONPath)はJSONをXPathのように操作するためのものです。
下記のようなスペックが定義されています。

• JSONPath - XPath for JSON

PostgreSQLでは全部に対応しているわけではない(例えば..はPostgreSQLでは利用できない)ので、ご注意ください。
なお、PostgreSQLのドキュメントには、JSONパス式としてどういったものが使えるか、詳しく記載されています。(ドキュメントほんと素晴らしいです。日本語訳もありがとうございます！)

• PostgreSQL 15 文書 - 8.14.7. jsonpath型
• PostgreSQL 15 文書 - 9.16. JSON関数と演算子

PostgreSQL以外の主要なデータベースでも、SQL/JSONパス式として、同じような機能が実装されています。

• SQL Server : JSON パス式 - SQL Server | Microsoft Learn
• MySQL : MySQL :: MySQL 8.0 リファレンスマニュアル :: 11.5 JSON データ型 - JSON パス構文
• Oracle : 第IV部 JSONデータの問合せ - SQL/JSONパス式

PostgreSQLにおけるJSONパス式は、jsonpath型として実装されています。
SQLでは文字列リテラルとしてシングルクォートで囲んで記述することになります。

'$.groups[1].name'

サンプルデータ

下記のようなJSONを例として利用します。

{
  "id": 1,
  "name": "Taro",
  "email": "[e-mail]",
  "groups": [
    {
      "id": 1,
      "name": "GroupA"
    },
    {
      "id": 2,
      "name": "GroupB"
    }
  ]
}

CREATE TABLE jsons(
  json jsonb
);

INSERT INTO jsons VALUES ('{
  "id": 1,
  "name": "Taro",
  "email": "[e-mail]",
  "groups": [
    {
      "id": 1,
      "name": "GroupA"
    },
    {
      "id": 2,
      "name": "GroupB"
    }
  ]
}');

PostgreSQL 15.5 で確認しています。

JSONパス式

JSONパス式の例ではjsonb_path_query関数を利用します。

jsonb_path_query(target jsonb, path jsonpath) → setof jsonb

この関数では、対象のJSONに対するJSONパスによって返されるすべてのJSON項目を返します。

アクセサ

JSON自体を指定する変数として$変数があります。
jsonpathとして$を指定すると、JSON全体が返却されます。

test=> SELECT jsonb_path_query(json, '$') FROM jsons;
                                                       jsonb_path_query
------------------------------------------------------------------------------------------------------------------------------
 {"id": 1, "name": "Taro", "email": "[e-mail]", "groups": [{"id": 1, "name": "GroupA"}, {"id": 2, "name": "GroupB"}]}
(1 row)

$を起点として、.でキー名を指定して、各要素を参照できます。
nameの値を取得する場合には、$.nameと書きます。

test=> SELECT jsonb_path_query(json, '$.name') FROM jsons;
 jsonb_path_query
------------------
 "Taro"
(1 row)

配列は[]で参照できます。

groupsの2番目のnameを取得する際には、$.groups[1].nameと書きます。
アクセサ周りは、JSONオブジェクトのプロパティを参照しているのと同じ感じになるので、イメージしやすいと思います。

test=> SELECT jsonb_path_query(json, '$.groups[1].name') FROM jsons;
 jsonb_path_query
------------------
 "GroupB"
(1 row)

ワイルドカート(*)を利用すると、複数の要素が取得できます。
.* とすると、その階層のメンバの値を全て返します。

test=> SELECT jsonb_path_query(json, '$.*') FROM jsons;
                      jsonb_path_query
------------------------------------------------------------
 1
 "Taro"
 "[e-mail]"
 [{"id": 1, "name": "GroupA"}, {"id": 2, "name": "GroupB"}]
(4 rows)

[*] とすると、配列内の全ての要素を返します。

test=> SELECT jsonb_path_query(json, '$.groups[*]') FROM jsons;
      jsonb_path_query
-----------------------------
 {"id": 1, "name": "GroupA"}
 {"id": 2, "name": "GroupB"}
(2 rows)

• PostgreSQL 15 文書 - 8.14.7. jsonpath型 - 表8.25 jsonpath Accessors

演算子とメソッド

jsonpathでは、演算子とメソッドも使えます。

演算子の例として+演算子を使ってみます。+演算子は加算を行う演算子となります。
たとえばidの値に10加算する際には + 10 で加算できます。

test=> SELECT jsonb_path_query(json, '$.id + 10') FROM jsons;
 jsonb_path_query
------------------
 11
(1 row)

メソッドの例としてsizeメソッドを使ってみます。sizeメソッドは、配列の要素数を返すメソッドです。

test=> SELECT jsonb_path_query(json, '$.groups.size()') FROM jsons;
 jsonb_path_query
------------------
 2
(1 row)

もうひとつ変わったメソッドとしてkeyvalueメソッドを紹介しておきます。
このメソッドは、JSONオブジェクトの各フィールドを、"key"、"value"、"id"といったフィールドを持ったオブジェクトとして返してくれます。"id"はキー/値ペアが属するオブジェクトのユニーク識別子です。

test=> SELECT jsonb_path_query(json, '$.keyvalue()') FROM jsons;
                                        jsonb_path_query
-------------------------------------------------------------------------------------------------
 {"id": 0, "key": "id", "value": 1}
 {"id": 0, "key": "name", "value": "Taro"}
 {"id": 0, "key": "email", "value": "[e-mail]"}
 {"id": 0, "key": "groups", "value": [{"id": 1, "name": "GroupA"}, {"id": 2, "name": "GroupB"}]}
(4 rows)

これを使うと、オブジェクトのキー一覧を取り出すことが出来ます。

test=> SELECT jsonb_path_query(json, '$.keyvalue().key') FROM jsons;
 jsonb_path_query
------------------
 "id"
 "name"
 "email"
 "groups"
(4 rows)

他にも様々な演算子とメソッドがあります。

• ostgreSQL 15 文書 - 9.16.2.2. SQL/JSONパス演算子とメソッド - 表9.49 jsonpath演算子とメソッド

フィルター式

フィルター式では、パスとして対象となった要素に対して、一致した条件のものに絞り込むことができます。

「パス ? (条件)」といった形式で書きます。
条件では@が現在の項目となるので、それに対して条件を記載していくことになります。

たとえば、groupsのidが2の項目を取り出したい場合には、下記のように書きます。

test=> SELECT jsonb_path_query(json, '$.groups[*] ? (@.id == 2)') FROM jsons;
      jsonb_path_query
-----------------------------
 {"id": 2, "name": "GroupB"}
(1 row)

like_regexで正規表現を条件にできます。
groupsのnameで、末尾にaまたはAが付くものを取得する場合には、下記のように書きます。

test=> SELECT jsonb_path_query(json, '$.groups[*] ? (@.name like_regex "a$" flag "i")') FROM jsons;
      jsonb_path_query
-----------------------------
 {"id": 1, "name": "GroupA"}
(1 row)

フィルター式の条件は、他にも様々なものがあります。

• ostgreSQL 15 文書 - 9.16.2.2. SQL/JSONパス演算子とメソッド - 表9.50 jsonpathフィルター式要素

非厳密(lax)モードと厳密(strict)モード

対象のJSONデータ構造に対して一致しないJSONパス式を指定した場合の動作として、非厳密(lax)モードと厳密(strict)モードがあります。
JSONパス式の先頭に lax と付けると非厳密モード、strictとつけると厳密モードになります。
デフォルトは非厳密(lax)モードです。

厳密(strict)モードだと、構造と異なるJSONパスを指定した場合エラーとなります。

test=> SELECT jsonb_path_query(json, 'strict $.xxxx') FROM jsons;
ERROR:  JSON object does not contain key "xxxx"

非厳密(lax)モードだと、構造上のエラーは無視されて、空のセットを返します。

test=> SELECT jsonb_path_query(json, 'lax $.xxxx') FROM jsons;
 jsonb_path_query
------------------
(0 rows)

test=> SELECT jsonb_path_query(json, '$.xxxx') FROM jsons;
 jsonb_path_query
------------------
(0 rows)

JSONパス式が利用できる演算子と関数

JSONパス式が利用できる演算子と、関数は下記の通りです。

• @?
• @@
• jsonb_path_query()
• jsonb_path_query_array()
• jsonb_path_query_first()
• jsonb_path_exists()
• jsonb_path_match()
• jsonb_path_query_tz()
• jsonb_path_query_array_tz()
• jsonb_path_query_first_tz()
• jsonb_path_exists_tz()
• jsonb_path_match_tz()

PostgreSQLのドキュメントでは、下記が該当箇所となります。

• PostgreSQL 15 文書 - 9.16. JSON関数と演算子 - 表9.46 追加jsonb演算子
• PostgreSQL 15 文書 - 9.16. JSON関数と演算子 - 表9.48 JSON処理関数

ここからは、演算子と関数の利用例を記載していきます。

@? 演算子

jsonb @? jsonpath → boolean

指定したJSONパスが値を返すかをチェックします。
存在するパスだとtrue、存在しないパスだとfalseです。

nameプロパティはあるのでtrueですが、xxxxプロパティは存在しないのでfalseになります。

test=> SELECT json @? '$.name' FROM jsons;
 ?column?
----------
 t
(1 row)

test=> SELECT json @? '$.xxxx' FROM jsons;
 ?column?
----------
 f
(1 row)

フィルタ式も入れれば、値も含めて確認できます。
groupsのidが2のものは存在するのでtrue、3は存在しないのでfalseになります。

test=> SELECT json @? '$.groups[*] ? (@.id == 2)' FROM jsons;
 ?column?
----------
 t
(1 row)

test=> SELECT json @? '$.groups[*] ? (@.id == 3)' FROM jsons;
 ?column?
----------
 f
(1 row)

@@ 演算子

jsonb @@ jsonpath → boolean

指定したJSONパス述語チェックの結果を返します。booleanじゃない場合には、NULLが返ります。
結果は最初の項目だけが考慮されます。

nameプロパティはTaroなので、Taroの場合はtrue、それ以外はfalseが返ります。

test=> SELECT json @@ '$.name == "Taro"' FROM jsons;
 ?column?
----------
 t
(1 row)

test=> SELECT json @@ '$.name == "Hanako"' FROM jsons;
 ?column?
----------
 f
(1 row)

booleanじゃないパス式を指定すると、NULLになります。

test=> SELECT json @@ '$.name' FROM jsons;
 ?column?
----------

(1 row)

jsonb_path_query 関数

jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb

JSONパスによって返される全てのJSON項目を返します。

test=> SELECT jsonb_path_query(json, '$.groups.id') FROM jsons;
 jsonb_path_query
------------------
 1
 2
(2 rows)

JSONパスの中に名前付きのパラメータを埋め込むことができ、そのパラメータを第3引数として指定することができます。

test=> SELECT jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}');
 jsonb_path_query
------------------
 2
 3
 4
(3 rows)

第4引数のsilentはtrueを指定するとエラーを無視してくれます。JSONパスの構文エラーではなく、数値などの変換エラーに関するものです。
これは各レコードで異なる形式のJSONを扱う場合に役に立ちます。

たとえば文字列に対してabs()を呼び出した場合、silentを指定しない場合(デフォルトfalse)はエラーになります。

test=> SELECT jsonb_path_query('{"id": "a"}', '$.id.abs()');
ERROR:  jsonpath item method .abs() can only be applied to a numeric value

silentでtrueを指定するとエラーを無視してくれます。

test=> SELECT jsonb_path_query('{"id": "a"}', '$.id.abs()', '{}', true);
 jsonb_path_query
------------------
(0 rows)

jsonb_path_query_array 関数

jsonb_path_query_array ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

JSONパスによって返される全てのJSON項目をJSON配列として返します。

test=> SELECT jsonb_path_query_array(json, '$.groups.id') FROM jsons;
 jsonb_path_query_array
------------------------
 [1, 2]
(1 row)

jsonb_path_queryが集合を返すのに対して、jsonb_path_query_arrayはJSON配列を返すといった違いだけになります。

jsonb_path_query_first 関数

jsonb_path_query_first ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

JSONパスによって返される最初のJSON項目を返します。

test=> SELECT jsonb_path_query_first(json, '$.groups.id') FROM jsons;
 jsonb_path_query_first
------------------------
 1
(1 row)

JSONパスが何も返さない場合、NULLが返ります。

test=> SELECT jsonb_path_query_first(json, '$.xxx') FROM jsons;
 jsonb_path_query_first
------------------------

(1 row)

jsonb_path_exists 関数

jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

指定したJSONパスが値を返すかをチェックします。
@?演算子と同じです。

test=> SELECT jsonb_path_exists(json, '$.name') FROM jsons;
 jsonb_path_exists
-------------------
 t
(1 row)

test=> SELECT jsonb_path_exists(json, '$.xxx') FROM jsons;
 jsonb_path_exists
-------------------
 f
(1 row)

jsonb_path_match 関数

指定したJSONパス述語チェックの結果を返します。booleanじゃない場合には、NULLが返ります。
結果は最初の項目だけが考慮されます。
@@演算子と同じです。

test=> SELECT jsonb_path_match(json, '$.name == "Taro"') FROM jsons;
 jsonb_path_match
------------------
 t
(1 row)

test=> SELECT jsonb_path_match(json, '$.name == "Hanako"') FROM jsons;
 jsonb_path_match
------------------
 f
(1 row)

jsonb_path_query_tz、jsonb_path_query_array_tz、jsonb_path_query_first_tz、jsonb_path_exists_tz、jsonb_path_match_tz 関数

末尾に_tzが付いた関数は、時間帯を考慮する日時値の比較をサポートするための関数になります。
_tzが付いた関数では、タイムゾーンが省略されているデータに対してdatetimeメソッドを使うと、実行環境のタイムゾーンで処理されます。

なお、_tzがついていない関数では、タイムゾーンが省略されているデータに対してdatetimeメソッドを利用するとエラーとなります。

test=> SELECT jsonb_path_exists('["2023-12-01 00:00:00-00"]', '$[*] ? (@.datetime() < "2023-12-01".datetime())');
ERROR:  cannot convert value from date to timestamptz without time zone usage
HINT:  Use *_tz() function for time zone support.

下記は_tzが付いているものが、実行環境のタイムゾーンが効いていることがわかる例となります。

America/Los_Angeles はオフセットが-8です。
2023-12-01 00:00:00+00 より 2023-12-01 00:00:00-08 の方が大きくなるので、trueとなります。

test=> SET TIMEZONE TO 'America/Los_Angeles';
SET
test=> show timezone;
      TimeZone
---------------------
 America/Los_Angeles
(1 row)

test=> SELECT jsonb_path_exists_tz('["2023-12-01 00:00:00+00"]', '$[*] ? (@.datetime() < "2023-12-01".datetime())');
 jsonb_path_exists_tz
----------------------
 t
(1 row)

Asia/Tokyo はオフセットが+9です。
2023-12-01 00:00:00+00 より 2023-12-01 00:00:00+09 の方が小さくなるので、falseとなります。

test=> SET TIMEZONE TO 'Asia/Tokyo';
SET
test=> show timezone;
  TimeZone
------------
 Asia/Tokyo
(1 row)

test=> SELECT jsonb_path_exists_tz('["2023-12-01 00:00:00+00"]', '$[*] ? (@.datetime() < "2023-12-01".datetime())');
 jsonb_path_exists_tz
----------------------
 f
(1 row)

おわりに

簡単なものはJSONパスを使わない他の演算子(->や->>、?など)でも十分ですが、複雑なものはJSONパスを使わないと難しい場合も多いかなと思っています。

jsonbから値を取り出すときに、そういやJSONパスもあったな、、と思い出してもらうきっかけになったら幸いです。