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

# はじめに
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](https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-PATH)

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

PostgreSQLのJSON PATHは、単なる文字列としてではなく、1つの独立した型([jsonpath Type](https://www.postgresql.org/docs/12/datatype-json.html#DATATYPE-JSONPATH))として管理される。

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

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

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

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

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

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

```sql
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](https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-PATH)を見てもらうとして、では、これを実際にどうJSON文書に使っていけばいいのか。

# JSON文書へのJSON PATHの適用

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

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

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

```sql
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値の項目を返すかどうかを確認します。<br>→ `@?`演算子はこの関数のラッパー演算子なんだろう。たぶん。|
|jsonb_path_match(target jsonb, path jsonpath [, vars jsonb, silent bool])|boolean|指定されたJSON値に対するJSONパスの述語結果を返します。 最初の結果項目のみが考慮されます。 結果がない、または最初の結果項目がブール値ではない場合、nullが返されます。<br>→ `@@`演算子はこの関数のラッパー演算子なんだろう。たぶん。|

### 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を指定する。

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

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

```sql
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引数の変数を使って書き換えてみる。

```sql
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()とだいたい同じ。

```sql
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つのみの結果を返却する関数である。

```sql
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)<br>たぶん、jsonb_path_exists()関数のラップ比較演算子。|
|@@|jsonb|jsonpath|boolean|こっちの説明も良くわからん・・・<br>たぶん、jsonb_path_match()関数のラップ比較演算子。|

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

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

```sql
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となる。

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