はじめに

PostgreSQLでは9.2の頃からJSON型のサポートをしており、バージョンアップ毎に少しづつ機能を追加している。
現在開発中のPostgreSQL 12でも、JSON型に関する大幅な強化が行われた。

これまでのPostgreSQLでのJSON対応（概要）

PostgreSQL 9.2からPostgreSQL 11までのPostgreSQLにおけるJSON型の取り組みの概要はこんな感じ。

Ver.	主な改善内容
9.2	JSON型の初導入。
9.3	JSON型に対する演算関数・演算子の追加。
9.4	データをバイナリ形式で管理するJSONB型の追加。JSONB型独自の演算子追加
9.5	JSON/JSONB型向け関数・演算子の追加
9.6	jsonb_insert()の追加
10	テキスト検索関数ts_headline()とto_tsvector()のJSONB対応。JSON/JSONB型の追加
11	テキスト検索関数json(b)_to_tsvector()のJSONB対応、PL/Pytho, PL/PerltoJSONBのバインディング拡張機能追加

PostgreSQL12でのJSON関連の機能強化

JSONB型が登場したPostgreSQL 9.4からPostgreSQL 11までの機能強化というのは、関数/演算子の強化であったり、テキスト検索関数との連携といったPostgreSQL固有機能の拡張、という内容が多かったが、PostgreSQL 12でのJSON型拡張は、SQL/JSON featureの項目の一つであるJSON PATHに対応したものになっている。
→ SQL/JSON Path Expressions

JSON PATH

JSON PATHはその名前の通り、JSON文書内のパスを指定するものである。

PostgreSQLのJSON PATHは、単なる文字列としてではなく、1つの独立した型(jsonpath Type)として管理される。

例えばJSON PATHは以下のような文字列で表現される。

jp=# SELECT '$.path[*].cost ? (@ < 220)';
          ?column?
----------------------------
 $.path[*].cost ? (@ < 220)
(1 row)

これは文字列型としてそのままJSON PATH形式の文字列を出力しただけだが、この文字列は、jsonpathとして型変換できる。

jp=# SELECT '$.path[*].cost ? (@ < 220)'::jsonpath;
           jsonpath
------------------------------
 $."path"[*]."cost"?(@ < 220)
(1 row)

単に文字列として出力されているのではなく、いったんjsonpath型として内部的に格納され、それの外部表現の文字列が出力されている。このへんは、JSONB型と少し似ている。

さて、JSON PATH文字列をjsonpath型に格納する場合には、JSON PATHとしての構文が正しいかをチェックしている。JSON PATHとして妥当でない形式の文字列の場合、以下のような構文エラーとなる。

jp=# SELECT '$.path[*].cost ? (@ = 220)'::jsonpath;
ERROR:  syntax error, unexpected $undefined at or near "=" of jsonpath input
LINE 1: SELECT '$.path[*].cost ? (@ = 220)'::jsonpath;
               ^
jp=#

(JSON PATHの比較演算子として=はない。==が正しい演算子となるので、上記のようなエラーとなる）。

JSON PATHの形式については、PostgreSQL文書のSQL/JSON Path Expressionsを見てもらうとして、では、これを実際にどうJSON文書に使っていけばいいのか。

JSON文書へのJSON PATHの適用

JSON PATHはそれだけでは、それほど意味のあるものではない。これをJSONB型と組み合せることで初めて役に立つものになる。
PostgreSQL 12では、JSONB型、JSON PATHを組み合わせたSQL関数と、JSONB型に対してJSON PATHを与えて比較演算を行う演算子がサポートされた。

とりあえずサンプルを使って試してみる。

とりあえず、こんな感じのN段の階層を持つようなJSON文書を登録しておく。文書のイメージとしては、出張時の旅費や移動開始点/移動終了地点/経由地(複数)を管理するようなものを想定している。

jp=# SELECT jsonb_pretty(data) FROM test;
          jsonb_pretty
--------------------------------
 {                             +
     "id": 101,                +
     "date": "2019-07-29",     +
     "path": [                 +
         {                     +
             "end": "品川",    +
             "cost": 288,      +
             "start": "横浜"   +
         },                    +
         {                     +
             "end": "市ヶ谷",  +
             "cost": 200,      +
             "start": "品川",  +
             "transit": [      +
                 "秋葉原"      +
             ]                 +
         },                    +
         {                     +
             "end": "横浜",    +
             "cost": 464,      +
             "start": "市ヶ谷",+
             "transit": [      +
                 "溜池山王",   +
                 "新橋"        +
             ]                 +
         }                     +
     ]                         +
 }
(1 row)
jp=#

jsonpath型を用いるSQL関数

SQL関数	返却型	説明
jsonb_path_query(target jsonb, path jsonpath [, vars jsonb, silent bool])	setof jsonb	指定されたJSON値のJSONパスによって返されたすべてのJSON項目を取得します。
jsonb_path_query_array(target jsonb, path jsonpath [, vars jsonb, silent bool])	jsonb	指定されたJSON値のJSONパスによって返されたすべてのJSON項目を取得し、結果を配列にラップします。
jsonb_path_query_first(target jsonb, path jsonpath [, vars jsonb, silent bool])	jsonb	指定されたJSON値のJSONパスによって返される最初のJSON項目を取得します。結果がない場合はNULLを返します。
jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb, silent bool])	boolean	JSONパスが指定されたJSON値の項目を返すかどうかを確認します。 → `@?`演算子はこの関数のラッパー演算子なんだろう。たぶん。
jsonb_path_match(target jsonb, path jsonpath [, vars jsonb, silent bool])	boolean	指定されたJSON値に対するJSONパスの述語結果を返します。最初の結果項目のみが考慮されます。結果がない、または最初の結果項目がブール値ではない場合、nullが返されます。 → `@@`演算子はこの関数のラッパー演算子なんだろう。たぶん。

jsonb_path_query()

jsonb_path_query()は第1引数にJSONB型を、第2引数にjsonpath型を与えて、jsonpathによって示されたJSONB要素を取得するという関数である。

まずシンプルな例を示す。date列には上に示したJSONデータが格納されている。最初の階層にはid要素とdate要素とpathという配列要素が存在している。
このJSON文書からid要素を値を取得する場合、以下のようにjsonb_path_query()に'$.id'というJSON PATHを指定する。

jp=# SELECT jsonb_path_query(data::jsonb, '$.id') FROM test;
 jsonb_path_query
------------------
 101
(1 row)

次はもう少し複雑な例を。始点(satrt)または終点(end)が"横浜"なpathを選択する。
JSON PATHでは、?演算子の後にconditionを書くことで、条件に合致したjsonb要素のみを取り出すことができる。

jp=# SELECT jsonb_path_query(data, '$.path[*] ? ( @.start == "横浜" || @.end == "横浜")') FROM test;
                                 jsonb_path_query
----------------------------------------------------------------------------------
 {"end": "品川", "cost": 288, "start": "横浜"}
 {"end": "横浜", "cost": 464, "start": "市ヶ谷", "transit": ["溜池山王", "新橋"]}
(2 rows)

上記の例では、1つのJSONB文書からJSON PATHにヒットするJSONB要素が2つ存在したので、結果としては2行返却される。

jsonb_path_query()はオプション引数として第3引数、第4引数を指定することができる。
第3引数には、JSON PATH四季内で使える変数を指定できる。
さっきの問い合わせと同じ意味のJSON PATHを第3引数の変数を使って書き換えてみる。

jp=# SELECT jsonb_path_query(data, '$.path[*] ? ( @.start == $var || @.end == $var )', '{"var":"横浜"}') FROM test;
                                 jsonb_path_query
----------------------------------------------------------------------------------
 {"end": "品川", "cost": 288, "start": "横浜"}
 {"end": "横浜", "cost": 464, "start": "市ヶ谷", "transit": ["溜池山王", "新橋"]}
(2 rows)

varという変数に"横浜"という値を設定し、conditionの中で、$varのように指定することで"横浜"という値が展開され、それによるcondtionの評価が行われる。

第4引数については・・・説明がどこにもない・・・。上記の関数の第4引数にtrue/falseを指定したけど、結果が変わらない。silent_modeという引数のようだがどういうときに挙動が変わるのか・・・。（別途調査）

jsonb_path_query_array()

この関数は簡単にいえば、jsonb_path_query()がsetof jsonbを返却するのに対して、結果をJSONB配列として返却するものである。あとの機能はjsonb_path_query()とだいたい同じ。

jp=# SELECT jsonb_path_query_array(data, '$.path[*] ? ( @.start == $var || @.end == $var )', '{"var":"横浜"}', false) FROM test;
                                                      jsonb_path_query_array
-----------------------------------------------------------------------------------------------------------------------------------
 [{"end": "品川", "cost": 288, "start": "横浜"}, {"end": "横浜", "cost": 464, "start": "市ヶ谷", "transit": ["溜池山王", "新橋"]}]
(1 row)

jsonb_path_query_first()

これは、JSON PATHでヒットする要素が複数存在した場合、最初の1つのみの結果を返却する関数である。

jp=# SELECT jsonb_path_query_first(data, '$.path[*] ? ( @.start == $var || @.end == $var )', '{"var":"横浜"}', false) FROM test;
            jsonb_path_query_first
-----------------------------------------------
 {"end": "品川", "cost": 288, "start": "横浜"}
(1 row)

この例だとJSON PATHにマッチするのは2つのJSONB要素なのだが、そのうち(配列の)先頭にある要素が返却されている。

jsonpath型を用いる演算子

jsonpath型を使う比較演算子は以下の2つ。

演算子	左辺の型	右辺の型	返却型	内容
@?	jsonb	jsonpath	boolean	まだ、PostgreSQL文書もきちんと説明文が書かれていないんだよな・・・(beta2) たぶん、jsonb_path_exists()関数のラップ比較演算子。
@@	jsonb	jsonpath	boolean	こっちの説明も良くわからん・・・たぶん、jsonb_path_match()関数のラップ比較演算子。

@? 演算子

これは、JSON PATHで指定した要素が存在すればtrueを、存在していなければfasleを返却するもののようだ。

id要素は存在するのでtrueを返却する。od要素は存在しないのでfalseを返却する。

jp=# SELECT data @? '$.id' FROM test;
 ?column?
----------
 t
(1 row)

jp=# SELECT data @? '$.od' FROM test;
 ?column?
----------
 f
(1 row)

@@ 演算子

これは指定したJSON PATHの値がconditionと一致する場合にtrueを、一致しない場合にfalseを返却する。
現在格納されているJSON文書のid要素の値は101なので、JSON PATHに'$.id == 101'と書いた場合にはtrueとなる。

jp=# SELECT data @@ '$.id == 101' FROM test;
 ?column?
----------
 t
(1 row)

jp=# SELECT data @@ '$.id == 102' FROM test;
 ?column?
----------
 f
(1 row)

おわりに

とりあえず、簡単にJSON PATH機能を使ってみたが、JSON PATH自体の機能はこんなものではなくて、かなり高機能なので、今後ももう少し調べて置く必要がありそう。

JSON PATH関数
JSON PATH演算子
JSON PATHのlaxモード、strictモードの違いなど

また、既存のJSON/JSONB関数との使い分けについても整理が必要かもしれない。

PostgreSQL 12がやってくる(6) - JSON PATH