PolarDB:JSON関数と演算子

JSONデータの処理と作成

次の表は、JSONデータ型で使用できる演算子を示しています。 さらに、表9.1に示す通常の比較演算子はjsonbに使用できますが、jsonには使用できません。 比較演算子は、Bツリー演算の順序付け規則に従う。

jsonおよびjsonb演算子

以下の表に示すように、jsonbにのみ存在する演算子もあります。

追加jsonb演算子

次の表は、json値とjsonb値の作成に使用できる関数を示しています。

JSON作成関数

1. 任意のSQL値をjsonまたはjsonbに変換します。 配列とコンポジットは、配列とオブジェクトに再帰的に変換されます (多次元配列はJSONの配列の配列になります) 。 それ以外の場合、SQLデータ型からjsonへのキャストがある場合は、キャスト関数を使用して変換を実行します。それ以外の場合は、スカラーJSON値が生成されます。 数値、ブール値、またはnull値以外のスカラーでは、テキスト表現が使用され、必要に応じてエスケープして有効なJSON文字列値にします。

to_json ( anyelement ) → json
to_jsonb ( anyelement ) → jsonb
to_json('Fred said "Hi." '::text) → "Fred said \" Hi.\""
to_jsonb(row(42、'Fred said "Hi." '::text)) → {"f1": 42、"f2": "Fred said \" Hi.\""} 

2. SQL配列をJSON配列に変換します。 動作は _jsonと同じですが、オプションのbooleanパラメーターがtrueの場合、ラインフィードが最上位の配列要素の間に追加されます。

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

3. SQLコンポジット値をJSONオブジェクトに変換します。 動作は _jsonと同じですが、オプションのbooleanパラメーターがtrueの場合、ラインフィードが最上位要素間に追加されます。

row_to_json ( record [, boolean ] ) → json
row_to_json(row(1,'foo')) → {"f1":1,"f2":"foo"} 

4. variadic引数リストから、異種型である可能性のあるJSON配列を構築します。 各引数は、_jsonまたは _jsonbに変換されます。

json_build_array ( VARIADIC "any" ) → json
jsonb_build_array ( VARIADIC "any" ) → jsonb
json_build_array(1, 2, 'foo', 4,5) → [1, 2, "foo", 4,5] 

5. variadic引数リストからJSONオブジェクトをビルドします。 慣例により、引数リストはキーと値が交互になっています。 キー引数はテキストに強制され、値引数は _jsonまたは _jsonbに変換されます。

json_build_object ( VARIADIC "any" ) → json
jsonb_build_object ( VARIADIC "any" ) → jsonb
json_build_object('foo', 1,2, row(3,'bar')) → {"foo" : 1, "2" : {"f1":3,"f2":"bar"}} 

6. テキスト配列からJSONオブジェクトをビルドします。 配列は、偶数のメンバーを持つ正確に1つの次元 (この場合、それらは交互のキー /値のペアと見なされる) 、または各内部配列が正確に2つの要素を持つような2つの次元 (キー /値のペアと見なされる) のいずれかを持つ必要があります。 すべての値はJSON文字列に変換されます。

json_object ( text[] ) → json
jsonb_object ( text[] ) → jsonb
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"} 

7. この形式のjson_objectは、別々のテキスト配列からキーと値をペアで受け取ります。 それ以外の場合は、1引数形式と同じです。

json_object ( keys text[], values text[] ) → json
jsonb_object ( keys text[], values text[] ) → jsonb
json_object('{a,b}', '{1,2}') → {"a": "1", "b": "2"} 

[a] たとえば、hstore拡張はhstoreからjsonへのキャストを持つため、JSON作成関数を介して変換されたhstore値は、プリミティブ文字列値ではなくJSONオブジェクトとして表されます。

JSON処理関数

1. 最上位のJSON配列をJSON値のセットに展開します。

json_array_elements ( json ) → setof json
jsonb_array_elements ( jsonb ) → setof jsonb
select * from json_array_elements('[1,true, [2,false]]') →
   値
-----------
 1
 true
 [2,false] 

2. 最上位のJSON配列をテキスト値のセットに展開します。

json_array_elements_text ( json ) → setof text
jsonb_array_elements_text ( jsonb ) → setof text
select * from json_array_elements_text('["foo", "bar"]') →
   値
-----------
 foo
 バー 

3. トップレベルのJSON配列の要素数を返します。

json_array_length ( json ) → integer
jsonb_array_length ( jsonb ) → 整数
json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') → 5 

4. トップレベルのJSONオブジェクトを一連のキーと値のペアに展開します。

json_each ( json ) → setof record ( key text, value json)
jsonb_each ( jsonb ) → setof record (キーテキスト, value jsonb)

select * from json_each('{"a":"foo", "b":"bar"}') →
 キー | 値
----- -------
 a | "foo"
 b | "バー" 

5. トップレベルのJSONオブジェクトを一連のキーと値のペアに展開します。 返される値sはtext型になります。

json_each_text ( json ) → setofレコード (キーテキスト, 値テキスト)
jsonb_each_text ( jsonb ) → setofレコード (キーテキスト, 値テキスト)

select * from json_each_text('{"a":"foo", "b":"bar"}') →
 キー | 値
----- -------
 a | foo
 b | バー 

6. 指定したパスでJSONサブオブジェクトを抽出します。 (これは #> 演算子と機能的に同等ですが、場合によっては、パスを可変リストとして書き出す方が便利です。)

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

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

7. 指定したパスのJSONサブオブジェクトを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_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6') → foo 

8. トップレベルのJSONオブジェクトのキーのセットを返します。

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

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

9. トップレベルのJSONオブジェクトを、base引数の複合型を持つ行に展開します。 JSONオブジェクトは、名前が出力行タイプの列名と一致するフィールドについてスキャンされ、その値は出力の列に挿入されます。 (出力列名に対応しないフィールドは無視されます。) 通常の使用では、baseの値はNULLにすぎません。つまり、オブジェクトフィールドと一致しない出力列はNULLで埋められます。 ただし、baseがNULLでない場合は、一致しない列に含まれる値が使用されます。

   JSON値を出力列のSQL型に変換するには、次のルールが順番に適用されます。

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

   • 出力列のタイプがjsonまたはjsonbの場合、JSON値は正確に再現されます。

   • 出力列がコンポジット (行) 型で、JSON値がJSONオブジェクトの場合、オブジェクトのフィールドは、これらのルールを再帰的に適用することにより、出力行型の列に変換されます。

   • 同様に、出力列が配列型で、JSON値がJSON配列の場合、JSON配列の要素は、これらの規則の再帰的適用によって出力配列の要素に変換されます。

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

   • それ以外の場合は、JSON値の通常のテキスト表現が、列のデータ型の入力変換関数に送られます。

   以下の例では、定数JSON値を使用しますが、通常は、クエリのfrom句内の別のテーブルから横方向にjsonまたはjsonb列を参照することになります。 抽出されたすべての列は重複する関数呼び出しなしで使用できるため、FROM句にjson_populate_recordを記述することをお勧めします。

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

create type subrowtype as (d int, e text); create type myrowtype as (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") 

10. オブジェクトのトップレベルのJSON配列を、base引数の複合型を持つ行のセットに展開します。 JSON配列の各要素は、上記のjson[b]_populate_recordの説明に従って処理されます。

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

タイプtwointsを (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 

11. トップレベルのJSONオブジェクトを、AS句で定義された複合型を持つ行に展開します。 (レコードを返すすべての関数と同様に、呼び出しクエリはAs句を使用してレコードの構造を明示的に定義する必要があります。) 出力レコードは、上記のJSON [b]_populate_recordと同じ方法で、jsonオブジェクトのフィールドから入力されます。 入力レコード値がないので、一致しない列は常にヌルで満たされる。

json_to_record ( json ) → レコード
jsonb_to_record ( jsonb ) → レコード

作成するタイプmyrowtypeとして (int、bテキスト);
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テキスト、r myrowtype) →

 a | b | c | d | r
--- -------- ------------ --------------------------
 1 | [1,2,3] | {1,2,3} | | (123,"a b c") 

12. オブジェクトのトップレベルのJSON配列を、AS句で定義された複合型を持つ行のセットに展開します。 (レコードを返すすべての関数と同様に、呼び出しクエリはAs句を使用してレコードの構造を明示的に定義する必要があります。) JSON配列の各要素は、上記のjson[b]_populate_recordの説明に従って処理されます。

json_to_recordset ( json ) → setof record
jsonb_to_recordset ( jsonb ) → setof 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 | 

12. pathで指定された項目をnew_valueに置き換えたtargetを返します。create_if_missingがtrue (デフォルト) で、pathで指定された項目が存在しない場合は、new_valueを追加します。 パス内の以前のすべてのステップが存在する必要があります。そうしないと、targetは変更されずに返されます。 パス指向演算子と同様に、パスに表示される負の整数は、JSON配列の末尾からカウントされます。 最後のパスステップが範囲外の配列インデックスで、create_if_missingがtrueの場合、インデックスが負の場合は配列の先頭に、正の場合は配列の末尾に新しい値が追加されます。

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

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] 

13. new_valueがNULLでない場合、jsonb_setと同じように動作します。 それ以外の場合は、'raise_exception' 、'use_json_null '、'delete_key' 、または 'return_target' のいずれかである必要があるnull_value_treatmentの値に従って動作します。 デフォルトは 'use_json_null 'です。

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

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] 

14. new_valueが挿入されたtargetを返します。 pathで指定された項目が配列要素の場合、insert_afterがfalse (デフォルト) の場合はその項目の前にnew_valueが挿入され、insert_afterがtrueの場合はその項目の後に挿入されます。 pathで指定された項目がオブジェクトフィールドの場合、オブジェクトにそのキーがまだ含まれていない場合にのみnew_valueが挿入されます。 パス内の以前のすべてのステップが存在する必要があります。そうしないと、targetは変更されずに返されます。 パス指向演算子と同様に、パスに表示される負の整数は、JSON配列の末尾からカウントされます。 最後のパスステップが範囲外の配列インデックスである場合、インデックスが負の場合は配列の先頭に、正の場合は配列の末尾に新しい値が追加されます。

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

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]} 

15. 指定されたJSON値からnull値を持つすべてのオブジェクトフィールドを再帰的に削除します。 オブジェクトフィールドでないヌル値はそのままです。

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

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

16. JSONパスが指定されたJSON値の項目を返すかどうかを確認します。 vars引数が指定されている場合、それはJSONオブジェクトでなければならず、そのフィールドはjsonpath式に代入される名前付き値を提供します。 silent引数が指定され、trueの場合、関数は @? および @ @ 演算子と同じエラーを抑制します。

jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean
jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → t 

17. 指定されたJSON値のJSONパス述語チェックの結果を返します。 結果の最初の項目のみが考慮されます。 結果がブール値でない場合、NULLが返されます。 オプションのvarsおよびsilent引数は、jsonb_path_existsの場合と同じように機能します。

jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean
jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max) ', '{"min":2, "max":4}') → t 

18. 指定されたJSON値のJSONパスによって返されたすべてのJSONアイテムを返します。 オプションのvarsおよびsilent引数は、jsonb_path_existsの場合と同じように機能します。

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

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

19. 指定されたJSON値のJSONパスによって返されたすべてのJSONアイテムをJSON配列として返します。 オプションのvarsおよびsilent引数は、jsonb_path_existsの場合と同じように機能します。

jsonb_path_query_array ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb
jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → [2, 3, 4] 

20. 指定されたJSON値のJSONパスによって返される最初のJSONアイテムを返します。 結果がない場合はNULLを返します。 オプションのvarsおよびsilent引数は、jsonb_path_existsの場合と同じように機能します。

jsonb_path_query_first ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb
jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → 2 

21. これらの関数は、タイムゾーン対応の変換を必要とする日付 /時刻値の比較をサポートすることを除いて、_tzサフィックスのない上記の関数と同様に機能します。 次の例では、日付のみの値2015-08-02をタイムゾーンのタイムスタンプとして解釈する必要があるため、結果は現在のTimeZone設定に依存します。 この依存関係により、これらの関数は安定しているとマークされます。つまり、これらの関数はインデックスでは使用できません。 それらの対応物は不変であるため、インデックスで使用できます。ただし、そのような比較を行うように求められると、エラーが発生します。

jsonb_path_exists_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → ブール値
jsonb_path_match_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → ブール
jsonb_path_query_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb
jsonb_path_query_array_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb
jsonb_path_query_first_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

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

22. 指定されたJSON値をきれいに印刷されたインデントテキストに変換します。

jsonb_pretty ( jsonb ) → テキスト

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

23. 最上位のJSON値の型をテキスト文字列として返します。 可能な型は、object、array、string、number、boolean、およびnullです。 (nullの結果をSQL NULLと混同しないでください。例を参照してください。)

json_typeof ( json ) → テキスト
jsonb_typeof ( jsonb ) → テキスト

json_typeof('-123.4') → 番号
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に相当します。

SQL/JSONパス言語

SQL/JSONパス式は、XMLへのSQLアクセスに使用されるXPath式と同様に、JSONデータから取得する項目を指定します。 InPostgreSQLでは、パス式はjsonpathデータ型として実装され、任意の要素を使用できます。

JSONクエリ関数と演算子は、指定されたパス式をパスエンジンに渡して評価します。 式がクエリされたJSONデータと一致する場合、対応するJSONアイテムまたはアイテムのセットが返されます。 パス式は、SQL/JSONパス言語で記述され、算術式および関数を含むことができる。

パス式は、jsonpathデータ型で許可されている要素のシーケンスで構成されます。 通常、パス式は左から右に評価されますが、括弧を使用して操作の順序を変更できます。 評価が成功すると、JSONアイテムのシーケンスが生成され、評価結果がJSONクエリ関数に返され、指定された計算が完了します。

クエリ対象のJSON値 (コンテキスト項目) を参照するには、パス式で $変数を使用します。 その後に1つ以上のアクセサー演算子が続き、JSON構造のレベルをレベルごとに下げて、コンテキストアイテムのサブアイテムを取得できます。 後続の各演算子は、前の評価ステップの結果を扱う。

たとえば、次のように、解析したいGPSトラッカーからのJSONデータがあるとします。

    {
      "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
          }
        ]
      }
    }

使用可能なトラックセグメントを取得するには、.キー周囲のJSONオブジェクトを下るアクセサー演算子:

$.track.segments

配列の内容を取得するには、通常、[*] 演算子を使用します。 たとえば、次のパスは、使用可能なすべてのトラックセグメントの位置座標を返します。

$.track.segments[*].location

最初のセグメントの座標のみを返すには、[] アクセサー演算子で対応する添字を指定します。 JSON配列インデックスが0相対であることを思い出してください:

$.track.segments[0].location

各パス評価ステップの結果は、1つ以上のjsonpath演算子およびメソッドによって処理できます。 各メソッド名の前にドットを付ける必要があります。 たとえば、配列のサイズを取得できます。

$.track.segments.size()

パス式内でjsonpath演算子とメソッドを使用するその他の例は、以下のセクション9.16.2.2にあります。

パスを定義するときに、SQLのWHERE句と同様に機能する1つ以上のフィルター式を使用することもできます。 フィルター式は疑問符で始まり、括弧内の条件を提供します。

    ? (条件) 

フィルター式は、適用するパス評価ステップの直後に記述する必要があります。 そのステップの結果は、提供された条件を満たすアイテムのみを含むようにフィルタリングされる。 SQL/JSONは3値ロジックを定義するため、条件はtrue、false、またはunknownになります。 unknown値はSQL NULLと同じ役割を果たし、is unknown述語でテストできます。 さらなるパス評価ステップは、フィルター式がtrueを返した項目のみを使用します。

フィルター式で使用できる関数と演算子を表9.49に示します。 フィルタ式内で、@ 変数はフィルタリングされる値 (すなわち、先行するパスステップの1つの結果) を示す。 @ の後にアクセサー演算子を記述して、コンポーネントアイテムを取得できます。

たとえば、130より高いすべての心拍数値を取得したいとします。 これは、次の式を使用して実現できます。

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

このような値を持つセグメントの開始時間を取得するには、開始時間を返す前に無関係なセグメントを除外する必要があります。そのため、フィルター式は前のステップに適用され、条件で使用されるパスは異なります。

$.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() 

この式は、心拍数の高いセグメントが含まれている場合はトラックのサイズを返し、そうでない場合は空のシーケンスを返します。

PostgreSQLによるSQL/JSONパス言語の実装には、SQL/JSON標準から次のような逸脱があります。

• SQL/JSON標準ではフィルターでのみ述語が許可されていますが、パス式はブール述語にすることができます。 これは、@ @ 演算子の実装に必要です。 たとえば、次のjsonpath式はPostgreSQLで有効です。

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

• regexfiltersで使用される正規発現パターンの解釈にはわずかな違いがあります。

厳密およびLaxモード

JSONデータをクエリすると、パス式が実際のJSONデータ構造と一致しない場合があります。 オブジェクトまたは配列の要素の存在しないメンバーにアクセスしようとすると、構造エラーが発生します。 SQL/JSONパス式には、構造エラーを処理する2つのモードがあります。

• lax (デフォルト)-パスエンジンは、クエリされたデータを指定されたパスに暗黙的に適応させます。 残りの構造エラーは抑制され、空のSQL/JSONシーケンスに変換されます。

• strict-構造エラーが発生すると、エラーが発生します。

laxモードは、JSONデータが予想されるスキーマに準拠していない場合、JSONドキュメント構造とパス式のマッチングを容易にします。 オペランドが特定の操作の要件と一致しない場合は、自動的にSQL/JSON配列としてラップするか、この操作を実行する前に要素をSQL/JSONシーケンスに変換することでラップを解除できます。 さらに、比較演算子は緩いモードでオペランドを自動的にアンラップするため、SQL/JSON配列をそのまま比較できます。 サイズ1の配列は、その唯一の要素に等しいと見なされます。 自動アンラッピングは、次の場合にのみ実行されません。

• パス式には、配列内の型と要素数をそれぞれ返すtype() メソッドまたはsize() メソッドが含まれます。

• クエリされたJSONデータには、ネストされた配列が含まれます。 この場合、最も外側の配列のみがアンラップされ、すべての内側の配列は変更されません。 したがって、暗黙のアンラッピングは、各パス評価ステップ内で1レベル下に進むことしかできません。

たとえば、上記のGPSデータをクエリする場合、ラックスモードを使用するときにセグメントの配列が格納されるという事実から抽象化できます。

lax $.track.segments.location

strictモードでは、指定されたパスは、クエリされたJSONドキュメントの構造と完全に一致してSQL/JSON項目を返す必要があるため、このパス式を使用するとエラーが発生します。 laxモードと同じ結果を得るには、segments配列を明示的にアンラップする必要があります。

strict $.track.segments[*].location

SQL/JSONパス演算子とメソッド

次の表に、jsonpathで使用できる演算子とメソッドを示します。 単項演算子およびメソッドは、先行するパスステップから生じる複数の値に適用することができるが、バイナリ演算子 (加算など) は、単一の値にのみ適用することができることに留意されたい。

jsonpath演算子とメソッド

注

datetime() およびdatetime() メソッドの結果タイプは、date、timetz、time、timestamptz、またはtimestampです。 どちらの方法も、結果タイプを動的に決定します。

datetime() メソッドは、入力文字列をdate、timetz、time、timestamptz、timestampのISO形式に一致させようとします。 最初の一致する形式で停止し、対応するデータ型を出力します。

datetime() メソッドは、指定されたテンプレート文字列で使用されるフィールドに従って結果の型を決定します。

datetime() メソッドとdatetime() メソッドは、to_timestamp SQL関数と同じ解析ルールを使用しますが、3つの例外があります。 まず、これらのメソッドでは、一致しないテンプレートパターンが許可されません。 次に、テンプレート文字列では、マイナス記号、ピリオド、solidus (スラッシュ) 、コンマ、アポストロフィ、セミコロン、コロン、スペースの区切り文字のみが使用できます。 第3に、テンプレート文字列の区切り文字は入力文字列と完全に一致する必要があります。

異なる日付 /時刻タイプを比較する必要がある場合は、暗黙のキャストが適用されます。 date値はtimestampまたはtimestamptzにキャストでき、timestampはtimestamptzにキャストでき、timeはtimetzにキャストできます。 ただし、これらの変換の最初の変換はすべて現在のTimeZone設定に依存するため、タイムゾーン対応のjsonpath関数内でのみ実行できます。

jsonpathフィルター式要素

SQL/JSON正規表現

SQL/JSONパス式では、like_regexフィルターを使用してテキストを正規表現に一致させることができます。 たとえば、次のSQL/JSONパスクエリは、英語の母音で始まる配列内のすべての文字列を大文字と小文字を区別しないように照合します。

$[*] ? (@ like_regex "^[aeiou]" flag "i") 

オプションのフラグ文字列は、大文字と小文字を区別しない一致の文字i、改行で ^ と $を一致させるm、改行を一致させるs. 、パターン全体を引用するq (動作を単純な部分文字列一致に減らす) のうちの1つまたは複数を含むことができる。

SQL/JSON標準は、XQuery標準を使用するLIKE_REGEX演算子から正規表現の定義を借用します。 PostgreSQLは現在、LIKE_REGEX演算子をサポートしていません。 したがって、like_regexフィルタは、POSIX正規表現エンジンを使用して実装されます。 これは、カタログ化されている標準SQL/JSON動作からの様々な小さな不一致につながる。 ただし、ここで説明したフラグ文字の非互換性は、XQueryフラグ文字をPOSIXエンジンが期待するものと一致するように変換するため、SQL/JSONには適用されません。

like_regexのパターン引数は、ルールに従って記述されたJSONパス文字列リテラルであることに注意してください。 これは特に、正規表現で使用するバックスラッシュを2倍にする必要があることを意味します。 たとえば、数字のみを含むルートドキュメントの文字列値を一致させるには:

$ ? (@ like_regex "^\\ d +$")