Go to Qiita Advent Calendar 2024 Top
LoginSignup
31
15

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@nuko_yokohama(nuko yokohama)

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

Posted at

はじめに

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関数との使い分けについても整理が必要かもしれない。

31
15
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
31
15

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?