PostgreSQL 13がやってくる！(7-1) - SQL関数の強化 - JSONB関数

# はじめに
にゃーん。
今回はPostgreSQL 13の新機能のうち、SQL関数に関するものを試してみましょ。[2020-05-13時点のRelease note](https://momjian.us/pgsql_docs/release-13.html)から調べてみました。

# PostgrreSQL 13のSQL関数関連の改善
PostgreSQL 13のSQL関数まわりの改善一覧
PostgreSQL 13のSQL関数周りのリリースノートを見ると、こんだけあります。

* Add alternate version of jsonb_set with special NULL handling (Andrew Dunstan)
* Add jsonpath .datetime() method (Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander Korotkov)
Add SQL functions NORMALIZE() to normalize Unicode strings, and IS NORMALIZED to check for normalization (Peter Eisentraut)
* Allow Unicode escapes, e.g., E'\u####', in clients that don't use UTF-8 encoding (Tom Lane)
* Allow to_date() and to_timestamp() to recognize non-English month/day names (Juan Jose Santamaria Flecha, Tom Lane)
* Add format specifications FF1-FF6 to control display of 1-6 subsecond digits (Alexander Korotkov, Nikita Glukhov, Teodor Sigaev, Oleg Bartunov)
* Add SSSSS time format specification as an SQL standard alias for SSSS (Nikita Glukhov, Alexander Korotkov)
* Add function gen_random_uuid to generate version 4 UUIDs (Peter Eisentraut)
* Previously UUID generation functions were only available external modules uuid-ossp or pgcrypto were installed.
* Add greatest-common-denominator (gcd) and least-common-multiple (lcm) functions (Vik Fearing)
* Improve the performance and accuracy of square root and natural log (ln) output (Dean Rasheed)
* Add function min_scale() that returns the number of digits to the right the decimal point that is required to represent the numeric value with full precision (Pavel Stehule)
* Add function trim_scale() to reduce the scale of a number by * Add commutators of distance operators (Nikita Glukhov)
* Create xid8 versions of all transaction id functions (Thomas Munro)
The old function names were kept for backward compatibility.
* Allow get_bit() and set_bit() to set bits beyond 256MB of bytea data (Movead Li)
* Allow advisory-lock functions to be used in some parallel operations (Tom Lane)
* Add the ability to remove an object's dependency on an extension (Alvaro Herrera)


今回も結構ありますねえ。普段使わない関数に関するものが多いので、確認するのが少々面倒です。
最初は全部一つの記事にまとめようかと思ったけど、思ったよりもJSONB関数周りの記事が長くなりそうなので、まずJSONB関数だけでまとめようと思います。

# JSONB型関連の関数
PostgreSQL 9.4から導入されたJSONを格納するデータ型、JSONBには様々な演算を行う関数があります。PostgreSQL 13では新規SQL関数の追加と、既存のjsonpath関数の変更があるようです。

## jsonb_set_lax()
PostgreSQL 13では従来の`jsonb_set()`に加えて「ゆるふわ」なjsonb_set_lax()というSQL関数が追加されました。

* Add alternate version of jsonb_set with special NULL handling (Andrew Dunstan)

### jsob_set()の挙動（おさらい）
まず、PostgreSQL 12まででサポートされていた`jsonb_set()`のおさらいを（自分も忘れていた）。
jsonb_set()関数の引数は以下のような形式となります。
`jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])`

* `target`は対象となるjsonb文書
* `path`はそのjsonb文書内の位置を示すパスをTEXT配列で指定したもの
* `new_value`は置換するjsonbデータ型の値
* `create_missing`はpathで示した場所が見つからない場合の挙動を指定する。
  * この値がtrueであればそのpathで指定した場所にnew_valueで指定した値を追加する。
  * falseであれば、pathに合致しないときには何も適用しない。
  * デフォルトはtrueとなる。

まー、ややこしいね。例えば、以下のようなJSONB文書があるとする。[^1]

[^1]: `jsonb_pretty()`はJSON文書内容を階層化して表示するSQL関数。

```sql
postgres=# SELECT jsonb_pretty('[{"f1":1,"f2":null},2,null,3]'::jsonb);
    jsonb_pretty
--------------------
 [                 +
     {             +
         "f1": 1,  +
         "f2": null+
     },            +
     2,            +
     null,         +
     3             +
 ]
(1 row)
```
この文書に対して、`[0, f1]`というパスを与え、そこに新しい値`"one"`という値を与える。

```sql
postgres=# SELECT jsonb_pretty(jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','"one"', true));
     jsonb_pretty
----------------------
 [                   +
     {               +
         "f1": "one",+
         "f2": null  +
     },              +
     2,              +
     null,           +
     3               +
 ]
(1 row)
```

パスが見つからない例（かつ`create_missing`がtrueの場合）
→このときには`0:f3`のパスに3を追加している。

```sql
postgres=# SELECT jsonb_pretty(jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f3}','3', true));
    jsonb_pretty
---------------------
 [                  +
     {              +
         "f1": 1,   +
         "f2": null,+
         "f3": 3    +
     },             +
     2,             +
     null,          +
     3              +
 ]
(1 row)
```

### jsonb_set_lax()の挙動
前置きが長くなりましたが、では、PostgreSQL 13で追加された、`jsonb_set_lax()`の引数はどうなっているのかというと、
`jsonb_set_lax ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean [, null_value_treatment text ]] ) `
となっている。
`jsonb_set()`では4つの引数を渡していたが、`jsonb_set_lax()`では5つ目の引数`null_value_treatment text`が追加されている。
PostgreSQL文書(2020-05-16時点のdevel版)をちら見した感じだと、

* new_valueがNULLでなければ、`json_set()`と同じ挙動をする。
* new_valueがNULLの場合、`null_value_treatment`の値によって挙動が変わる。
* `null_value_treatment`の値による挙動の違いは以下参照。ただ、現状(2020-05-17)の[PostgreSQL 13devel DocumentationのJSON Functions and Operators](https://www.postgresql.org/docs/devel/functions-json.html)には、各値の具体的な挙動が書かれていない。

|null_value_treatmentの値|実機検証結果|備考|
|:--|:--|:--|
|raise_exception|SQL ERRORとなる。|誤ってJSON NULLを設定させたくない場合に使うのかな？|
|use_json_null|パスで示された値がJSON NULLと設定される。||
|delete_key|パスで示された要素が削除される。||
|return_target|変更されない。||
|その他の文字列|null_value_treatment指定値誤りのSQL ERRORとなる。|PostgreSQL文書上は記載なし。|

それぞれのモードでjsonb_set_lax()を実行してみた例を示す。

元のJSONB文書

```sql
postgres=# SELECT jsonb_pretty('[{"f1":1,"f2":null, "f3":3},2,null,3]');
    jsonb_pretty
---------------------
 [                  +
     {              +
         "f1": 1,   +
         "f2": null,+
         "f3": 3    +
     },             +
     2,             +
     null,          +
     3              +
 ]
(1 row)
```

#### raise_exception指定時の挙動
`JSON value must not be null`というSQLエラーとなる。

```sql
postgres=# SELECT jsonb_pretty(jsonb_set_lax('[{"f1":1,"f2":null, "f3":3},2,null,3]', '{0,f3}', null, true, 'raise_exception'));
ERROR:  JSON value must not be null
DETAIL:  Exception was raised because null_value_treatment is "raise_exception".
HINT:  To avoid, either change the null_value_treatment argument or ensure that an SQL NULL is not passed.
postgres=#
```

#### use_json_null指定時の挙動
`'{0,f3}'`のパスで示された値がJSON NULLと設定される。

```sql
postgres=# SELECT jsonb_pretty(jsonb_set_lax('[{"f1":1,"f2":null, "f3":3},2,null,3]', '{0,f3}', null, true, 'use_json_null'))
;
    jsonb_pretty
---------------------
 [                  +
     {              +
         "f1": 1,   +
         "f2": null,+
         "f3": null +
     },             +
     2,             +
     null,          +
     3              +
 ]
(1 row)
```
#### delete_key指定時の挙動
`'{0,f3}'`のパスで示された要素が削除される。

```sql
postgres=# SELECT jsonb_pretty(jsonb_set_lax('[{"f1":1,"f2":null, "f3":3},2,null,3]', '{0,f3}', null, true, 'delete_key'));
    jsonb_pretty
--------------------
 [                 +
     {             +
         "f1": 1,  +
         "f2": null+
     },            +
     2,            +
     null,         +
     3             +
 ]
(1 row)
```
#### return_target指定時の挙動
変更されない。
第1引数に指定した、targetのJSONB文書をそのまま返却する。

```sql
postgres=# SELECT jsonb_pretty(jsonb_set_lax('[{"f1":1,"f2":null, "f3":3},2,null,3]', '{0,f3}', null, true, 'return_target'))
;
    jsonb_pretty
---------------------
 [                  +
     {              +
         "f1": 1,   +
         "f2": null,+
         "f3": 3    +
     },             +
     2,             +
     null,          +
     3              +
 ]
(1 row)
```

#### その他の文字列指定時の挙動
`null_value_treatment`の指定値誤りというSQL ERRORになる。
`null_value_treatment`にNULLを設定した場合も同様にSQL ERRORとなる。

```sql
postgres=# SELECT jsonb_pretty(jsonb_set_lax('[{"f1":1,"f2":null, "f3":3},2,null,3]', '{0,f3}', null, true, 'foobar'));
ERROR:  null_value_treatment must be "delete_key", "return_target", "use_json_null", or "raise_exception"
postgres=#
```

## jsonpathでdatetimeを評価可能に
PostgreSQL 12からはjsonpathというJSON文書への高度なアクセスを行うSQL関数が追加されました。PostgreSQL 13ではそのjsonpathへの改善(日付データ型対応)が追加されました。

* Add jsonpath .datetime() method (Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander Korotkov) 

ざっくり言うと、日付型をdatetime(), あるいはdatetime(書式)で評価できるようになった。
以下にJSON Path内でのdatetime()の使用例を示す。

```sql
postgres=# SELECT jsonb_path_query('["2020-Apr-01", "2020-May-12", "2020-Aug-01"]',
  '$[*] ? (@.datetime("YYYY-Mon-DD") >= "2020-04-30".datetime("YYYY-MM-DD"))') ;
 jsonb_path_query
------------------
 "2020-May-12"
 "2020-Aug-01"
(2 rows)
```

以下の例のように、別の形式の日付文字列でも評価することができる。

```sql
postgres=# SELECT jsonb_path_query('["2020-Apr-01", "2020-May-12", "2020-Aug-01"]',
  '$[*] ? (@.datetime("YYYY-Mon-DD") >= "2020-Apr-30".datetime("YYYY-Mon-DD"))') ;
 jsonb_path_query
------------------
 "2020-May-12"
 "2020-Aug-01"
(2 rows)
```

これでJSON文書内の日付を含むデータの評価がかなり柔軟にできそうですね。

## SQL/JSON Path Operators and Methodsの形式変更
あと、JSON Pathまわりを調べていて気づいたけど、何気に[PostreSQL 12のSQL/JSON Path Operators and Methods](https://www.postgresql.org/docs/12/functions-json.html#FUNCTIONS-SQLJSON-PATH-OPERATORS)と[PostgreSQL 13のSQL/JSON Path Operators and Methods](https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-SQLJSON-PATH-OPERATORS)の表形式がまったく別物になっている！
この変更に関しては、現時点のRelease Notesには書いてないな・・・。

PostgreSQL 12の表
<img width="523" alt="jsonb-path-doc-12.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/32626/f56aaec8-1349-1a6f-5393-9b6cd56f44b0.png">

PostgreSQL 13の表
<img width="523" alt="jsonb-path-doc-13.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/32626/1b518845-ec2a-2f9f-467a-11ea65f29e98.png">

# おわりに
今回はPostgreSQL 13で追加された、JSONB関連のSQL関数を使ってみました。
JSONB型自体、実案件で使う機会は少ないかもしれませんが、今後、複雑なJSON文書をPostgreSQLに格納して検索/更新するときに、いろいろ役に立ちそうな関数が追加されたようです。