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

はじめに

にゃーん。
今回はPostgreSQL 13の新機能のうち、SQL関数に関するものを試してみましょ。2020-05-13時点のRelease noteから調べてみました。

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文書があるとする。¹

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"という値を与える。

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を追加している。

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には、各値の具体的な挙動が書かれていない。

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文書

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エラーとなる。

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と設定される。

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}'のパスで示された要素が削除される。

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文書をそのまま返却する。

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

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()の使用例を示す。

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)

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

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とPostgreSQL 13のSQL/JSON Path Operators and Methodsの表形式がまったく別物になっている！
この変更に関しては、現時点のRelease Notesには書いてないな・・・。

PostgreSQL 12の表

PostgreSQL 13の表

おわりに

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

1. jsonb_pretty()はJSON文書内容を階層化して表示するSQL関数。 ↩