PolarDB:JSON 関数および演算子

json および jsonb で共用される演算子

フィールド、要素、およびパス抽出演算子は、JSON 入力が要求された構造と一致しない場合（たとえば、キーまたは配列要素が存在しない場合など）にエラーを発生させるのではなく、NULL を返します。

jsonb 専用の追加演算子

@? および @@ 演算子は、以下のエラーを抑制します：オブジェクトのフィールドまたは配列要素の欠落、予期しない JSON アイテムの型、日時および数値に関するエラー。この動作は、構造が異なる JSON ドキュメントのコレクションを照会する際に有用です。

to_json および to_jsonb

to_json ( anyelement ) → json
to_jsonb ( anyelement ) → jsonb

任意の SQL 値を json または jsonb に変換します。配列および複合型は、JSON 配列およびオブジェクトに再帰的に変換されます。SQL 型から json へのキャストが存在する場合は、そのキャスト関数が使用されます。それ以外の場合、スカラー値は適切な形式（エスケープを含む）で JSON 文字列、ブール値、数値、または null として表現されます。

to_json('Fred said "Hi."'::text)
-- → "Fred said \"Hi.\""

to_jsonb(row(42, 'Fred said "Hi."'::text))
-- → {"f1": 42, "f2": "Fred said \"Hi.\""}

array_to_json

array_to_json ( anyarray [, boolean ] ) → json

SQL 配列を JSON 配列に変換します。省略可能なブール値パラメーターが true の場合、最上位レベルの配列要素間に改行が挿入されます。

array_to_json('{{1,5},{99,100}}'::int[])
-- → [[1,5],[99,100]]

row_to_json

row_to_json ( record [, boolean ] ) → json

SQL 複合値を JSON オブジェクトに変換します。省略可能なブール値パラメーターが true の場合、最上位レベルの要素間に改行が挿入されます。

row_to_json(row(1,'foo'))
-- → {"f1":1,"f2":"foo"}

json_build_array および jsonb_build_array

json_build_array ( VARIADIC "any" ) → json
jsonb_build_array ( VARIADIC "any" ) → jsonb

可変長引数リストから、異種型の JSON 配列を構築します。各引数は to_json または to_jsonb を使用して変換されます。

json_build_array(1, 2, 'foo', 4, 5)
-- → [1, 2, "foo", 4, 5]

json_build_object および jsonb_build_object

json_build_object ( VARIADIC "any" ) → json
jsonb_build_object ( VARIADIC "any" ) → jsonb

可変長引数リスト（交互にキーと値を指定）から JSON オブジェクトを構築します。キー引数は text 型に強制変換され、値は to_json または to_jsonb を使用して変換されます。

json_build_object('foo', 1, 2, row(3,'bar'))
-- → {"foo" : 1, "2" : {"f1":3,"f2":"bar"}}

json_object および jsonb_object

json_object ( text[] ) → json
jsonb_object ( text[] ) → jsonb

テキスト配列から JSON オブジェクトを構築します。配列は、偶数個のメンバーを持つ 1 次元配列（交互にキー／値ペアとして扱われる）または、各内側配列が正確に 2 つの要素を持つ 2 次元配列のいずれかである必要があります。すべての値は JSON 文字列に変換されます。

json_object('{a, 1, b, "def", c, 3.5}')
-- → {"a" : "1", "b" : "def", "c" : "3.5"}

json_object('{{a, 1}, {b, "def"}, {c, 3.5}}')
-- → {"a" : "1", "b" : "def", "c" : "3.5"}

2 つの引数を取る形式では、別々のテキスト配列からキーと値を取得します。

json_object ( keys text[], values text[] ) → json
jsonb_object ( keys text[], values text[] ) → jsonb

json_object('{a,b}', '{1,2}')
-- → {"a": "1", "b": "2"}

拡張機能がその型から json へのキャスト（たとえば、hstore 拡張機能）を提供する場合、これらの作成関数によって変換された値は、プリミティブな文字列ではなく、そのキャストを使用します。

JSON の展開および照会

json_array_elements および jsonb_array_elements

json_array_elements ( json ) → setof json
jsonb_array_elements ( jsonb ) → setof jsonb

最上位レベルの JSON 配列を JSON 値のセットに展開します。

select * from json_array_elements('[1,true, [2,false]]');
--    value
-- -----------
--  1
--  true
--  [2,false]

json_array_elements_text および jsonb_array_elements_text

json_array_elements_text ( json ) → setof text
jsonb_array_elements_text ( jsonb ) → setof text

最上位レベルの JSON 配列を text 値のセットに展開します。

select * from json_array_elements_text('["foo", "bar"]');
--    value
-- -----------
--  foo
--  bar

json_array_length および jsonb_array_length

json_array_length ( json ) → integer
jsonb_array_length ( jsonb ) → integer

最上位レベルの JSON 配列の要素数を返します。

json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')
-- → 5

json_each および jsonb_each

json_each ( json ) → setof record ( key text, value json )
jsonb_each ( jsonb ) → setof record ( key text, value jsonb )

最上位レベルの JSON オブジェクトをキー／値ペアのセットに展開します。

select * from json_each('{"a":"foo", "b":"bar"}');
--  key | value
-- -----+-------
--  a   | "foo"
--  b   | "bar"

json_each_text および jsonb_each_text

json_each_text ( json ) → setof record ( key text, value text )
jsonb_each_text ( jsonb ) → setof record ( key text, value text )

最上位レベルの JSON オブジェクトをキー／値ペアのセットに展開し、値を text 型として返します。

select * from json_each_text('{"a":"foo", "b":"bar"}');
--  key | value
-- -----+-------
--  a   | foo
--  b   | bar

json_object_keys および jsonb_object_keys

json_object_keys ( json ) → setof text
jsonb_object_keys ( jsonb ) → setof text

最上位レベルの JSON オブジェクトのキーのセットを返します。

select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');
--  json_object_keys
-- ------------------
--  f1
--  f2

サブオブジェクトの抽出

json_extract_path および jsonb_extract_path

json_extract_path ( from_json json, VARIADIC path_elems text[] ) → json
jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb

指定されたパスにある JSON サブオブジェクトを抽出します。#> 演算子と同等ですが、パスを可変長リストとして記述することで、特定のケースではより便利になります。

json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')
-- → "foo"

json_extract_path_text および jsonb_extract_path_text

json_extract_path_text ( from_json json, VARIADIC path_elems text[] ) → text
jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] ) → text

指定されたパスにある JSON サブオブジェクトを text 型として抽出します。#>> 演算子と同等です。

json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')
-- → foo

JSON からの複合型へのポピュレート

json_populate_record および jsonb_populate_record

json_populate_record ( base anyelement, from_json json ) → anyelement
jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement

最上位レベルの JSON オブジェクトを、base の複合型に一致する行に展開します。出力カラム名と一致するフィールド名は対応するカラムに挿入され、一致しないフィールドは無視されます。base が NULL の場合、一致しないカラムは null で埋められます。それ以外の場合、一致しないカラムは base の値を保持します。

以下の型変換ルールが順に適用されます。

• JSON の null は、すべての場合において SQL の NULL に変換されます。

• 出力カラムが json または jsonb 型の場合、JSON 値はそのまま再現されます。

• 出力カラムが複合型（行）であり、JSON 値が JSON オブジェクトの場合、フィールドは再帰的に変換されます。

• 出力カラムが配列型であり、JSON 値が JSON 配列の場合、要素は再帰的に変換されます。

• JSON 値が文字列の場合、その内容はカラムのデータ型の入力変換関数に渡されます。

• それ以外の場合、JSON 値のテキスト表現が入力変換関数に渡されます。

json_populate_record を FROM 句で使用すると、重複した関数呼び出しをせずにすべての抽出カラムを利用できます。

create type subrowtype as (d int, e text);
create type myrowtype as (a int, b text[], c subrowtype);

select * from json_populate_record(null::myrowtype,
  '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}, "x": "foo"}');
--  a |   b       |      c
-- ---+-----------+-------------
--  1 | {2,"a b"} | (4,"a b c")

json_populate_recordset および jsonb_populate_recordset

json_populate_recordset ( base anyelement, from_json json ) → setof anyelement
jsonb_populate_recordset ( base anyelement, from_json jsonb ) → setof anyelement

最上位レベルの JSON 配列（オブジェクトの配列）を、base の複合型に一致する行のセットに展開します。各要素は、json_populate_record で説明した方法で処理されます。

create type twoints as (a int, b int);

select * from json_populate_recordset(null::twoints, '[{"a":1,"b":2}, {"a":3,"b":4}]');
--  a | b
-- ---+---
--  1 | 2
--  3 | 4

json_to_record および jsonb_to_record

json_to_record ( json ) → record
jsonb_to_record ( jsonb ) → record

最上位レベルの JSON オブジェクトを、AS 句で定義された複合型の行に展開します。呼び出し元のクエリでは、AS 句でレコード構造を明示的に定義する必要があります。一致しないカラムは常に null で埋められます。

create type myrowtype as (a int, b text);

select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}')
  as x(a int, b text, c int[], d text, r myrowtype);
--  a |    b    |    c    | d |       r
-- ---+---------+---------+---+---------------
--  1 | [1,2,3] | {1,2,3} |   | (123,"a b c")

json_to_recordset および jsonb_to_recordset

json_to_recordset ( json ) → setof record
jsonb_to_recordset ( jsonb ) → setof record

最上位レベルの JSON 配列（オブジェクトの配列）を、AS 句で定義された複合型の行のセットに展開します。各要素は、json_to_record で説明した方法で処理されます。

select * from json_to_recordset('[{"a":1,"b":"foo"}, {"a":"2","c":"bar"}]')
  as x(a int, b text);
--  a |  b
-- ---+-----
--  1 | foo
--  2 |

JSON 値の変更

jsonb_set

jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean ] ) → jsonb

target の path にある項目を new_value で置き換えます。create_if_missing が true（デフォルト）であり、path にある項目が存在しない場合、代わりに new_value が挿入されます。パスの前のすべてのステップが存在する必要があります。そうでない場合は、target が変更されずに返されます。

path 内の負の整数は、JSON 配列の末尾からカウントされます。create_if_missing が true であり、最後のパスステップが範囲外の配列インデックスである場合、新しい値は配列の先頭（負のインデックスの場合）または末尾（正のインデックスの場合）に追加されます。

jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', '[2,3,4]', false)
-- → [{"f1": [2, 3, 4], "f2": null}, 2, null, 3]

jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}', '[2,3,4]')
-- → [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]

jsonb_set_lax

jsonb_set_lax ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean [, null_value_treatment text ]] ) → jsonb

new_value が NULL でない場合、jsonb_set と同一の動作をします。new_value が NULL の場合、null_value_treatment に応じて動作が異なります。

jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', null)
-- → [{"f1":null,"f2":null},2,null,3]

jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null, true, 'return_target')
-- → [{"f1": 99, "f2": null}, 2]

jsonb_insert

jsonb_insert ( target jsonb, path text[], new_value jsonb [, insert_after boolean ] ) → jsonb

target の new_value を path に挿入します。配列要素の場合、new_value は insert_after が false（デフォルト）の場合はターゲット要素の前に、true の場合は後に挿入されます。オブジェクトフィールドの場合、キーが既に存在しない場合にのみ new_value が挿入されます。パスの前のすべてのステップが存在する必要があります。そうでない場合は、target が変更されずに返されます。

path 内の負の整数は、JSON 配列の末尾からカウントされます。範囲外の配列インデックスは、配列の先頭（負のインデックスの場合）または末尾（正のインデックスの場合）に値を追加します。

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"')
-- → {"a": [0, "new_value", 1, 2]}

jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true)
-- → {"a": [0, 1, "new_value", 2]}

クリーニングおよびフォーマット

json_strip_nulls および jsonb_strip_nulls

json_strip_nulls ( json ) → json
jsonb_strip_nulls ( jsonb ) → jsonb

指定された JSON 値から、null 値を持つすべてのオブジェクトフィールドを再帰的に削除します。null 値がオブジェクトフィールドでない場合（たとえば、null の配列要素など）は変更されません。

json_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]')
-- → [{"f1":1},2,null,3]

jsonb_pretty

jsonb_pretty ( jsonb ) → text

指定された JSON 値を、インデント付きの見やすいテキスト形式に変換します。

jsonb_pretty('[{"f1":1,"f2":null}, 2]')
-- → [
--       {
--           "f1": 1,
--           "f2": null
--       },
--       2
--   ]

json_typeof および jsonb_typeof

json_typeof ( json ) → text
jsonb_typeof ( jsonb ) → text

最上位レベルの JSON 値の型をテキスト文字列として返します。可能な型は、object、array、string、number、boolean、および null です。

null の結果は、JSON の値 null を反映しており、SQL の NULL ではありません。SQL の NULL 入力は、文字列 'null' ではなく、SQL の NULL 結果を返します。

json_typeof('-123.4')           -- → number
json_typeof('null'::json)       -- → null
json_typeof(NULL::json) IS NULL -- → t

集約関数

json_agg は、レコード値を JSON 配列に集約します。json_object_agg は、値のペアを JSON オブジェクトに集約します。jsonb 版の関数 — jsonb_agg および jsonb_object_agg — は jsonb の出力を生成します。

json_agg ( anyelement ) → json
jsonb_agg ( anyelement ) → jsonb

json_object_agg ( "any", "any" ) → json
jsonb_object_agg ( "any", "any" ) → jsonb

JSON パス関数

以下の関数は、jsonb 値に対して JSON パス式を評価します。

このグループのすべての関数は、同じ省略可能なパラメーターを受け入れます。

• vars — フィールドが名前付き値を提供する JSON オブジェクトで、jsonpath 式に代入されます

• silent — true の場合、@? および @@ 演算子と同じエラーが抑制されます

jsonb_path_exists

jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

JSON パスが指定された JSON 値に対して何らかの項目を返すかどうかを確認します。

jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')
-- → t

jsonb_path_match

jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

JSON パス述語チェックの結果を返します。結果の最初の項目のみが評価されます。結果がブール値でない場合は NULL を返します。

jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2, "max":4}')
-- → t

jsonb_path_query

jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb

JSON パスが指定された JSON 値に対して返すすべての JSON アイテムを返します。

select * from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}');
--  jsonb_path_query
-- ------------------
--  2
--  3
--  4

jsonb_path_query_array

jsonb_path_query_array ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

JSON パスが返すすべての JSON アイテムを JSON 配列として返します。

jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')
-- → [2, 3, 4]

jsonb_path_query_first

jsonb_path_query_first ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

JSON パスが返す最初の JSON アイテムを返します。結果がない場合は NULL を返します。

jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')
-- → 2

タイムゾーン対応のバリエーション

jsonb_path_exists_tz、jsonb_path_match_tz、jsonb_path_query_tz、jsonb_path_query_array_tz、および jsonb_path_query_first_tz の各関数は、上記の対応する関数と同一の動作をしますが、タイムゾーン対応の変換を必要とする日時値の比較をサポートします。

これらの関数は現在の TimeZone 設定に依存するため、stable としてマークされており、インデックス内で使用できません。_tz が付かないバリアントは immutable であり、インデックス内で使用できますが、タイムゾーン依存の比較を要求された場合にエラーをスローします。

jsonb_path_exists_tz('["2015-08-01 12:00:00 -05"]', '$[*] ? (@.datetime() < "2015-08-02".datetime())')
-- → t

パス式による JSON の走査

$ 変数を使用して、照会対象の JSON 値（コンテキストアイテム）を参照します。$ の後に 1 つ以上のアクセサ演算子を続けて、JSON 構造を走査します。

たとえば、以下の GPS トレーカーのデータがあるとします。

{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}

.key アクセサを使用して、JSON オブジェクトを下方向に走査します。

$.track.segments

[*] を使用して、配列のすべての要素を取得します。

$.track.segments[*].location

数値添字を使用して、特定の要素にアクセスします（JSON 配列のインデックスは 0 から始まります）。

$.track.segments[0].location

パスステップの結果を処理するために、メソッド呼び出し（ドットで始まる）を追加します。

$.track.segments.size()

フィルター式

フィルター式は、パスステップの結果を条件を満たす項目のみに絞り込みます。フィルターは、それが適用されるパスステップの直後に記述します。

? (condition)

SQL/JSON では 3 値論理が使用されます。条件は true、false、または unknown のいずれかに評価されます。フィルターが true を返す項目のみが、以降のパスステップに含まれます。is unknown 述語を使用して、unknown 値をテストできます。

フィルター内では、@ はフィルター対象の値（先行するパスステップの結果）を参照します。

GPS トレーカーのデータを使用した例：

心拍数（HR）が 130 を超える値をすべて取得します。

$.track.segments[*].HR ? (@ > 130)

心拍数（HR）が 130 を超えるセグメントの開始時刻を取得します（フィルターは HR 値ではなくセグメントに適用されます）。

$.track.segments[*] ? (@.HR > 130)."start time"

関連する座標と高い心拍数を持つセグメントを選択するために、複数のフィルターをチェーンします。

$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"

異なるネストレベルでフィルターを適用します — 最初に座標でセグメントをフィルターし、次に高い心拍数の値を返します。

$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)

ネストされたフィルター式を使用して、高い心拍数の値を持つセグメントが存在する場合にのみトラックサイズを返します。

$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()

Strict モードおよび Lax モード

パス式が実際の JSON 構造と一致しない場合、構造エラーが発生します。SQL/JSON パス式では、構造エラーを 2 つのモードで処理します。

• Lax（デフォルト）： パスエンジンは、照会対象のデータを指定されたパスに自動的に適合させます。残りの構造エラーは抑制され、空の SQL/JSON シーケンスに変換されます。比較演算子はオペランドを自動的にアンラップするため、SQL/JSON 配列を直接比較できます。サイズが 1 の配列は、その唯一の要素と等しいと見なされます。パスに type() または size() メソッドが含まれている場合、または JSON データにネストされた配列が含まれている場合（最上位の配列のみがアンラップされます）、自動アンラップは適用されません。

• Strict： 構造エラーが発生した場合、エラーがスローされます。

GPS トレーカーのデータを lax モードで使用すると、明示的な配列アンラップを省略できます。

lax $.track.segments.location

strict モードでは、配列を明示的にアンラップする必要があります。

strict $.track.segments[*].location

SQL/JSON 標準からの逸脱

PolarDB の実装は、SQL/JSON 標準から以下の 2 つの点で異なります。

• パス式は、標準ではフィルター内でのみ許可される述語であっても、ブール値述語となることができます。これは @@ 演算子で必要です。たとえば、以下の jsonpath 式は有効です。

  $.track.segments[*].HR < 70

• like_regex フィルターにおける正規表現パターンの解釈に、わずかな違いがあります。