他のバージョンの文書 ： 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2
PostgreSQL 12.4文書
第9章 関数と演算子
前へ 上へ9.15. JSON関数と演算子 次へ

9.15. JSON関数と演算子

この節では次のことを説明します。

SQL/JSON標準を更に学ぶためには、[sqltr-19075-6]をご覧ください。 PostgreSQLでサポートされているJSON型の詳細に関しては、8.14をご覧ください。

9.15.1. JSONデータの処理と生成

表 9.44にJSONデータ型(8.14を参照)で使用可能な演算子を示します。

表9.44 jsonとjsonb演算子

演算子右オペランド型戻り値型説明例例の結果
->intjson or jsonbJSON配列要素を取得（添字はゼロから、負数なら後ろから数える）'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2{"c":"baz"}
->textjson or jsonbキーに依るJSONオブジェクトフィールド取得'{"a": {"b":"foo"}}'::json->'a'{"b":"foo"}
->>inttextJSON 配列要素をtextとして取得'[1,2,3]'::json->>23
->>texttextJSON オブジェクトフィールドをtextとして取得'{"a":1,"b":2}'::json->>'b'2
#>text[]json or jsonb指定されたパスにあるJSONオブジェクトを取得'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'{"c": "foo"}
#>>text[]text指定されたパスにあるJSONオブジェクトをtextとして取得'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'3

注記

jsonとjsonb型の両方に対して、以上の演算子の対応するものがあります。 フィールド/要素/パス抽出演算子は左辺の入力と同じ型（jsonまたはjsonb）を返します。 ただし、textを返すと明示してあるものは、値をテキストに変換して返します。 JSON入力が要求と一致する正しい構造をしていなければ、フィールド/要素/パス抽出演算子は失敗するのではなくNULLを返します。例えばそのような要素が存在しない場合です。 JSON配列の添字を整数で受け取り、フィールド、要素、パスを返す演算子はすべて、配列の終わりから数える負の添字をサポートします。

表 9.1に示されている標準の比較演算子がjsonbで利用可能ですが、jsonではそうではありません。 それらは8.14.4で概略を述べたB-tree演算子の順序規則に従います。

ほかにjsonbだけで利用可能な演算子もいくつか存在します。 それらを表 9.45に示します。 これらのうち多くの演算子はjsonb演算子クラスでインデックス付けすることが可能です。 jsonbの包含と存在の意味に関する完全な記述は8.14.3を参照してください。 8.14.4には、jsonbデータを効率的にインデックス付けするためにこれらの演算子をどのように利用できるかについて書いてあります。

表9.45 追加jsonb演算子

演算子右オペランド型説明例
@>jsonb左のJSON値はトップレベルにおいて右のJSONパスまたは値を包含するか'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@jsonb左のJSONパスまたは値は右のJSON値にトップレベルで包含されるか'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
?text文字列はJSON値のトップレベルのキーとして存在するか'{"a":1, "b":2}'::jsonb ? 'b'
?|text[]配列の文字列のいずれかがトップレベルのキーとして存在するか。'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']
?&text[]配列の文字列のすべてがトップレベルのキーとして存在するか。'["a", "b"]'::jsonb ?& array['a', 'b']
||jsonb2つのjsonb値を結合して、新しいjsonb値を作る'["a", "b"]'::jsonb || '["c", "d"]'::jsonb
-text左のオペランドからキー／値のペア、あるいは文字列要素を削除する。 キー／値のペアはキーの値に基づいてマッチされる。'{"a": "b"}'::jsonb - 'a'
-text[]左のオペランドから複数のキー／値のペア、あるいは文字列要素を削除する。 キー／値のペアはキーの値に基づいてマッチされる。'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]
-integer指定した添字の配列要素を削除する（負数の場合は後ろから数えます）。 トップレベルのコンテナが配列でないときはエラーが発生する。'["a", "b"]'::jsonb - 1
#-text[]指定したパスのフィールドまたは要素を削除する（JSON配列では、負数は後ろから数えます）'["a", {"b":1}]'::jsonb #- '{1,b}'
@?jsonpathJSONパスが指定したJSON値に対応する要素を返すか？'{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)'
@@jsonpath指定したJSON値のJSONパス述語チェックの結果を返す。 結果の最初の項目だけが考慮されます。 結果がBooleanでないなら、nullが返ります。'{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2'

注記

||演算子は、両オペランドのトップレベルで要素を結合します。 再帰的には作用しません。 例えば、両方のオペランドが同じキーフィールド名のオブジェクトの場合、そのフィールドの値は、単に右側のオペランドの値になります。

注記

@?および@@演算子は以下のエラーを抑止します。 オブジェクトフィールドあるいは配列要素の欠如、期待しないJSON要素型、数値エラー。 この振る舞いは、異なる構造のJSON文書集合を検索する際に役に立つかも知れません。

表 9.46に、JSON値およびjsonb値を作成するために利用可能な関数を示します。 （row_to_json関数およびarray_to_json関数と同等なjsonbの関数はありません。 しかし、to_jsonbを使えば、これらの関数と同じ機能を提供できるでしょう。）

表9.46 JSON作成関数

関数説明例例の結果

to_json(anyelement)

to_jsonb(anyelement)

jsonあるいはjsonbとして値を返す。 配列と複合型は（再帰的に）配列とオブジェクトに変換される。 あるいは、その型からjsonにキャストがあれば、キャスト関数が変換のために用いられる。 そうでなければ、スカラ値が生成される。 数値、論理値、またはNULL値以外のスカラ型に対しては、有効なjson値あるいはjsonb値となる形式の文字列表現が使用される。 to_json('Fred said "Hi."'::text)"Fred said \"Hi.\""
array_to_json(anyarray [, pretty_bool]) 配列をJSON配列として返す。 PostgreSQLの多次元配列はJSON配列の配列となる。 pretty_boolが真の場合、次元数-1の要素の間に改行が加えられる。 array_to_json('{{1,5},{99,100}}'::int[])[[1,5],[99,100]]
row_to_json(record [, pretty_bool]) 行をJSONオブジェクトとして返す。 pretty_boolが真の場合、レベル-1の要素の間に改行が加えられる。 row_to_json(row(1,'foo')){"f1":1,"f2":"foo"}

json_build_array(VARIADIC "any")

jsonb_build_array(VARIADIC "any")

異なる型から構成される可能性のあるJSON配列をvariadic引数リストから作成。 json_build_array(1,2,'3',4,5)[1, 2, "3", 4, 5]

json_build_object(VARIADIC "any")

jsonb_build_object(VARIADIC "any")

variadic引数リストからJSONオブジェクトを作成。 慣例により引数リストはキーと値が交互に並んだもの。 json_build_object('foo',1,'bar',2){"foo": 1, "bar": 2}

json_object(text[])

jsonb_object(text[])

テキスト配列からJSONオブジェクトを作成。 配列は、偶数個の要素からなる1次元（キー／値の対が交互に並んでいるものと扱われる)）あるいは内側の配列が2つの要素を持つ2次元（2つの要素がキー／値の対として扱われる）のいずれかでなければならない。

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

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

{"a": "1", "b": "def", "c": "3.5"}

json_object(keys text[], values text[])

jsonb_object(keys text[], values text[])

この形のjson_objectは2つの別々の配列からキーと値の対を取る。 他の点ではすべて、引数1つの形と同じ。 json_object('{a, b}', '{1,2}'){"a": "1", "b": "2"}

注記

array_to_jsonとrow_to_jsonは表示を整えるオプションを提供する以外はto_jsonと同様の振舞いをします。 同様にto_jsonに書かれた振舞いはJSON作成関数により変換された個々の値に適用されます。

注記

hstore拡張はhstoreからjsonへのキャストを含みます。従って、JSON作成関数で変換されたhstore値は元の文字列値ではなくJSONオブジェクトとして示されます。

表 9.47にjsonとjsonb値を処理するのに使える関数を示します。

表9.47 JSON処理関数

関数戻り値型説明例例の結果

json_array_length(json)

jsonb_array_length(jsonb)

int JSON配列の最も外側の要素の数を返す。 json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')5

json_each(json)

jsonb_each(jsonb)

setof key text, value json

setof key text, value jsonb

JSONオブジェクトの最も外側をkey/valueの組み合わせに拡張する。 select * from json_each('{"a":"foo", "b":"bar"}') 
 key | value
-----+-------
 a   | "foo"
 b   | "bar"

json_each_text(json)

jsonb_each_text(jsonb)

setof key text, value text JSONオブジェクトの最も外側をkey/valueの組み合わせに拡張する。返り値は型text。 select * from json_each_text('{"a":"foo", "b":"bar"}') 
 key | value
-----+-------
 a   | foo
 b   | bar

json_extract_path(from_json json, VARIADIC path_elems text[])

jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])

json

jsonb

path_elemsで示されたJSONオブジェクトを返す(#>と同じ)。 json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4'){"f5":99,"f6":"foo"}

json_extract_path_text(from_json json, VARIADIC path_elems text[])

jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])

text path_elemsで示されたJSONオブジェクトをtextとして返す(#>>演算子と同じ)。 json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')foo

json_object_keys(json)

jsonb_object_keys(jsonb)

setof text 最も外側のJSONオブジェクトの中のキーの集合を返す。 json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') 
 json_object_keys
------------------
 f1
 f2

json_populate_record(base anyelement, from_json json)

jsonb_populate_record(base anyelement, from_json jsonb)

anyelement from_json内のオブジェクト行をbaseで定義されたレコード型に一致する列に拡張する(以下の注意書きを参照)。 select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}') 
 a |   b       |      c
---+-----------+-------------
 1 | {2,"a b"} | (4,"a b c")

json_populate_recordset(base anyelement, from_json json)

jsonb_populate_recordset(base anyelement, from_json jsonb)

setof anyelement from_jsonにおけるオブジェクトの最も外側の配列をbaseで定義されたレコード型に一致する列を持つ行の集合に展開する(以下の注意書き参照)。 select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]') 
 a | b
---+---
 1 | 2
 3 | 4

json_array_elements(json)

jsonb_array_elements(jsonb)

setof json

setof jsonb

JSON配列をJSON値の集合に展開する。 select * from json_array_elements('[1,true, [2,false]]') 
   value
-----------
 1
 true
 [2,false]

json_array_elements_text(json)

jsonb_array_elements_text(jsonb)

setof text JSON配列をtext値の集合に展開する。 select * from json_array_elements_text('["foo", "bar"]') 
   value
-----------
 foo
 bar

json_typeof(json)

jsonb_typeof(jsonb)

text 最も外側のJSON値の型をテキスト文字列として返す。 取りうる型はobject、 array、 string、 number、boolean、nullである。 json_typeof('-123.4')number

json_to_record(json)

jsonb_to_record(jsonb)

record JSONオブジェクトから任意のレコードを作成する（下記の注釈を参照）。 recordを返す関数すべてと同様、呼び出し側がAS句でレコードの構造を明示的に決める必要がある。 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(json)

jsonb_to_recordset(jsonb)

setof record オブジェクトの配列のJSONから任意のレコードの集合を作成する（下記の注釈を参照）。 recordを返す関数すべてと同様、呼び出し側がAS句でレコードの構造を明示的に決める必要がある。 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_strip_nulls(from_json json)

jsonb_strip_nulls(from_json jsonb)

json

jsonb

 NULL値を持つオブジェクトフィールドを削除したfrom_jsonを返す。 他のNULL値はそのまま残る。 json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')[{"f1":1},2,null,3]

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

jsonb

 pathで指定した部分をnew_valueで置換、 あるいはcreate_missingが真（デフォルトはtrue）でpathで指定した項目が存在しないならnew_valueを追加して、 targetを返す。 パスの位置についての演算子について、pathの中にある負の整数はJSON配列の終わりから数える。

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

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

[{"f1":[2,3,4],"f2":null},2,null,3]

[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]

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

jsonb

 new_valueを挿入してtargetを返す。 pathで指定したtargetの部分がJSONB配列内にある場合は、new_valueはtargetの前に挿入されるか、あるいは、insert_afterが真であれば（デフォルトはfalse）、後に挿入される。 pathで指定したtargetの部分がJSONBオブジェクト内にある場合は、new_valueはtargetが存在しない場合にのみ挿入される。 pathについての演算子について言うと、new_value内の負の整数はJSON配列の終わりから数える。

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

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

{"a": [0, "new_value", 1, 2]}

{"a": [0, 1, "new_value", 2]}

jsonb_pretty(from_json jsonb)

text

 from_jsonをインデントしたJSON文字列にして返す。 jsonb_pretty('[{"f1":1,"f2":null},2,null,3]') 
[
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    3
]

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

boolean JSONパスが指定したJSON値に対して項目を返すかどうかをチェックする。

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

true

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

boolean 指定したJSON値のJSONパス述語チェックの結果を返す。 結果の最初の項目だけが考慮されます。 結果がBooleanでないなら、nullが返ります。

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

true

jsonb_path_query(target jsonb, path jsonpath [, vars jsonb [, silent bool]])

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(target jsonb, path jsonpath [, vars jsonb [, silent bool]])

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(target jsonb, path jsonpath [, vars jsonb [, silent bool]])

jsonb 指定したJSON値に対するJSONパスが返す最初のJSON項目を得る。 結果がなければNULLを返す。

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

2


注記

これらの関数や演算子の多くは、JSON文字列のUnicodeのエスケープを適切な一文字に変換します。 これは入力がjsonb型であれば、変換は既に行なわれていますので、重要な問題ではありません。しかし、jsonの入力に対しては、8.14で言及したようにこれはエラーを発生させる結果になるかもしれません。

注記

関数json[b]_populate_record、json[b]_populate_recordset、json[b]_to_record、json[b]_to_recordsetはJSONオブジェクトあるいはJSONオブジェクトの配列を操作し、出力行型の列名と一致する名前を持つキーに関連付けられた値を取り出します。 出力列名と関連付けられないオブジェクトのフィールドは無視され、どのオブジェクトフィールドとも一致しない出力列はNULLで満たされます。 JSON値を出力列のSQL型に変換する際に以下のルールが順番に適用されます。

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

  • 出力列がjson型あるいはjsonb型なら、JSON値は単にそのまま複製されます。

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

  • 同様に、出力行が配列型でJSON値がJSON配列なら、これらのルールを再帰的に適用することによって、JSON配列の要素が出力配列の要素に変換されます。

  • それ以外の場合で、JSON値が文字列リテラルなら、その文字列の内容が列のデータ型に対応する入力変換関数に送られます。

  • さもなければ、通常のJSON値のテキスト表現が列のデータ型に対応する入力変換関数に送られます。

これらの関数の例では定数を使用していますが、典型的な使用法はFROM句でテーブルを参照し、そのjsonまたはjsonb列を関数の引数として使用することです。 WHERE句やターゲットリストのように、抽出されたキー値はクエリの他の部分で参照することができます。 この方法で複数の値を抽出すると、キー単位の演算子で個別に抽出するよりもパフォーマンスが向上します。

注記

jsonb_setおよびjsonb_insertのpathパラメータは、最後の項目を除き、すべての項目がtarget内に存在しなければなりません。 create_missingが偽なら、jsonb_setのpathパラメータのすべての項目が存在しなければなりません。 これらの条件が満たされなければ、targetが変更されずに返されます。

パスの最後の要素がオブジェクトのキーの場合、それが存在しなければ作成されて、新しい値が与えられます。 パスの最後の項目が配列の添字の場合、それが正ならセットすべき項目は左から数えられ、負なら右から数えられます（-1は最も右側の要素を指す、という具合です）。 項目が[-array_length .. array_length -1]の範囲外にあり、かつcreate_missingが真なら、新しい値が配列の先頭（項目が負数のとき）あるいは配列の終わり（項目が正数のとき）に追加されます。

注記

json_typeofのnull戻り値をSQLのNULLと混同してはいけません。 json_typeof('null'::json)を呼び出すとnullが返りますが、json_typeof(NULL::json)を呼び出すとSQLのNULLが返ります。

注記

json_strip_nullsの引数で、オブジェクト内にフィールド名が重複しているものがある場合、それが発生している順序によって、結果は意味的にやや異なるものになることがあります。 jsonb値はオブジェクトのフィールド名が重複することがないので、jsonb_strip_nullsにはこのような問題はありません。

注記

jsonb_path_exists、jsonb_path_match、jsonb_path_query, jsonb_path_query_array、jsonb_path_query_first関数はオプションのvarsとsilent引数を持ちます。

vars引数が指定されると、jsonpath式に変換できる名前付きの変数を含むオブジェクトを関数は返します。

silent引数が指定され、それが値trueなら、これらの関数は@?と@@演算子と同じエラーを抑止します。

レコードの値をJSONに集約するjson_agg集約関数、値の対をJSONオブジェクトに集約するjson_object_agg集約関数、およびそれらのjsonb版のjsonb_aggとjsonb_object_aggについては9.20を参照して下さい。

9.15.2. SQL/JSONパス言語

SQL/JSONパス式は、XMLへのSQLアクセスで使用されるXPath同様、JSONデータから取り出す項目を指定します。 PostgreSQLではパス式はjsonpathデータ型として実装されており、8.14.6で説明されているすべての要素を使うことができます。

JSON問い合わせ関数と演算子は与えられたパス式をpath engineに渡して評価します。 式が問い合わせ対象のJSONデータにマッチすれば、関連するSQL/JSON項目が返却されます。 パス式はSQL/JSONパス言語で書かれ、算術式と関数を含むことができます。 問い合わせ関数は与えられた式をテキスト文字列として扱うので、単一引用符で括らなければなりません。

パス式はjsonpathデータ型で認められた一連の要素からなります。 パス式は左から右へと評価されますが、括弧を使って演算の順序を変更することができます。 評価が成功すれば、一連のSQL/JSON項目（SQL/JSON sequence）が生成され、評価結果が指定した計算を完了したJSON問い合わせ関数に戻されます。

問い合わせ対象（context item）のJSONデータを参照するには、パス式内で$記号を使います。 複数のアクセサ演算子をその後に記述することもできます。 それによって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
      }
    ]
  }
}

存在するトラックセグメントを取り出すには、.keyアクセサ演算子をすべての先行するJSONオブジェクトに使用する必要があります。 

'$.track.segments'

取得したい項目が配列要素なら、[*]演算子を使って非配列化（unnest）する必要があります。 たとえば次のパスはすべての存在するトラックセグメントの位置座標を返します。 

'$.track.segments[*].location'

最初のセグメントの座標だけを返すには、[]アクセサ演算子の中で対応する添え字を指定することができます。 SQL/JSON配列は0スタートであることに注意してください。 

'$.track.segments[0].location'

各段階でのパス評価結果は9.15.2.3に列挙されている一つ以上のjsonpath演算子とメソッドで処理することができます。 各々のメソッド名の前にピリオドを付けなければなりません。 たとえば配列の大きさを得ることができます。 

'$.track.segments.size()'

パス式内のjsonpath演算子とメソッドについては9.15.2.3を参照してください。

パスを定義する際にはSQLのWHERE節のように働く一つ以上のフィルター式が利用できます。 フィルター式はクェスチョンマークで始まり、カッコ内に条件を記述します。 

? (condition)

フィルター式はそれを適用するパス評価段階の直後に指定しなければなりません。 この段階の結果は、指定した条件を満たす項目だけが含まれるようにフィルターされます。 SQL/JSONは3値論理を定義しており、条件はtrue、false、unknownのどれかです。 unknownは値はSQLのNULLと同じ役割を果たし、is unknown述語で評価できます。 その後の評価段階ではtrueを返すフィルター式に対応する項目だけが使われます。

フィルター式内で利用できる関数と演算子は表 9.49にリストされています。 フィルターする必要のあるパス評価結果は@変数で示します。 下位の入れ子レベルに格納されているJSON要素を参照するには、一つ以上のアクセサ演算子を@の後に追加してください。

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標準と次の点が異なります。

  • .datetime()項目メソッドは、主に不揮発性jsonpath関数と演算子が日付時刻操作で使用されているセッション時間帯を参照できないという理由でまだ実装されていません。 将来のPostgreSQLバージョンではjsonpathのサポートが追加される予定です。

  • SQL/JSON標準ではフィルター内でのみ述語が使えますが、パス式はBoolean述語でも構いません。 これは@@演算子を実装するために必要です。 たとえば、次のjsonpath式はPostgreSQLでは有効です。 

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

  • 9.15.2.2で述べるように、like_regexフィルターで使用される正規表現パターンの解釈には些細な違いがあります。

9.15.2.1. 厳密モードと非厳密モード

JSONデータを問い合わせる際、パス式は実際のJSONデータ構造に一致しないかも知れません。 存在しないオブジェクトのメンバーあるいは配列要素にアクセスしようとすると、構造上のエラーとなります。 SQL/JSONパス式には構造上のエラーを扱うための2つのモードがあります。

  • 非厳密(lax)モード（デフォルト）— パスエンジンは指定したパスを問い合わせデータに暗黙的に適合させます。 構造上のエラーは抑止され、空のSQL/JSONシーケンスへと変換されます。

  • 厳密(strict)モード — 構造上のエラーがあるとエラーが発生します。

非厳密モードは、JSONデータが期待されるスキーマに沿わないときにJSON文書構造とパス式のマッチングを助けます。 あるオペランドが操作の要件に合わないときにはそれをSQL/JSON配列にまとめたり、あるいは操作を行う前にそれをSQL/JSONシーケンスに展開することもできます。 また非厳密モードにおいては、比較演算子は自動的にオペランドを展開し、SQL/JSON配列をそのまま比較することができます。 大きさ1の配列はその単独要素と同じものとして扱われます。 自動展開は以下の場合にのみ行われません。

  • それぞれ配列の型、要素数を返すtype()、size()をパス式が含む。

  • 問い合わせ対象のJSONデータが入れ子の配列を含む。 この場合はもっとも外側の配列のみが展開され、内側の配列は変わりません。 ですから、それぞれの評価段階において1レベルのみに暗黙的な展開が行われます。

たとえば、上述のGPSデータに問い合わせする際、非厳密モードでは配列のセグメントを含んでいることを抽象化できます。 

'lax $.track.segments.location'

厳密モードでは、指定したパスはSQL/JSON項目を返す問い合わせ対象のJSON文書の構造に正確に一致していなければなりません。ですから、このパス式を使うとエラーになります。 非厳密モードと同じ結果を得るためには、segments配列を明示的に展開する必要があります。 

'strict $.track.segments[*].location'

9.15.2.2. 正規表現

SQL/JSONパス式ではlike_regexフィルターを使ってテキストを正規表現にマッチさせることができます。 たとえば、次のSQL/JSONパス式問い合わせは、英語の母音で始まる配列内のすべての文字列に大文字小文字を無視してマッチするでしょう。 

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

オプションのflag文字列は一つ以上の文字を含むことができます。 iは大文字小文字を無視したマッチ、mは^と$で改行にマッチ、sは.が改行にマッチ、qはパターン全体を参照します。（振る舞いを単純な部分文字列マッチとします）

SQL/JSON標準は正規表現の定義を、XQuery標準を使用するLIKE_REGEX演算子から借りています。 PostgreSQLは今の所LIKE_REGEX演算子をサポートしていません。 ですから、like_regexフィルターは9.7.3で説明されているPOSIX正規表現で実装されています。 このことにより、9.7.3.8で列挙されているSQL/JSON標準の振る舞いとの小さな違いが生じます。 しかし、ここで述べているフラグ文字の非互換性はSQL/JSONには適用されないことに注意してください。SQL/JSONは、XQueryのフラグ文字をPOSIXエンジンが期待するのと一致するように解釈するからです。

like_regexのパターン引数は8.14.6で説明されているルールにしたがって書かれたJSONパス文字列リテラルであることに注意してください。 これは、正規表現で使用するすべてのバックスラッシュを二重に書かなければならないことを意味します。 たとえば、数字のみを含む文字列にマッチさせるには以下のようにします。 

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

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

表 9.48にjsonpathで利用可能な演算子とメソッドを示します。 表 9.49には利用可能なフィルター式要素が示されています。

表9.48 jsonpath演算子とメソッド

演算子/メソッド説明JSONの例問い合わせ例結果
+（単項）SQL/JSONシーケンスに繰り返し適用される加算演算子{"x": [2.85, -14.7, -9.4]}+ $.x.floor()2, -15, -10
-（単項）SQL/JSONシーケンスに繰り返し適用される減算演算子{"x": [2.85, -14.7, -9.4]}- $.x.floor()-2, 15, 10
+（二項）加算[2]2 + $[0]4
-（二項）減算[2]4 - $[0]2
*積算[4]2 * $[0]8
/除算[8]$[0] / 24
%剰余[32]$[0] % 102
type()SQL/JSON項目の型[1, "2", {}]$[*].type()"number", "string", "object"
size()SQL/JSON項目の大きさ{"m": [11, 15]}$.m.size()2
double()SQL/JSONの数字あるいは文字列から変換されたおおよその浮動小数点数{"len": "1.9"}$.len.double() * 23.8
ceiling()SQL/JSON数字以上でもっとも近い整数{"h": 1.3}$.h.ceiling()2
floor()SQL/JSON数字以下でもっとも近い整数{"h": 1.3}$.h.floor()1
abs()SQL/JSON数字の絶対値{"z": -0.3}$.z.abs()0.3
keyvalue() 3つのフィールド（"key"、"value"、"id"）を含む項目の配列で表現されたオブジェクトのキーバリューペアのシーケンス。 "id"はキーバリューペアが所属するオブジェクトのユニーク識別子です。 {"x": "20", "y": 32}$.keyvalue(){"key": "x", "value": "20", "id": 0}, {"key": "y", "value": 32, "id": 0}

表9.49 jsonpathフィルター式要素

値/述語説明JSONの例問い合わせ例結果
==等値演算子[1, 2, 1, 3]$[*] ? (@ == 1)1, 1
!=非等値演算子[1, 2, 1, 3]$[*] ? (@ != 1)2, 3
<>非等値演算子（!=と同じ）[1, 2, 1, 3]$[*] ? (@ <> 1)2, 3
<未満演算子[1, 2, 3]$[*] ? (@ < 2)1
<=以下演算子[1, 2, 3]$[*] ? (@ <= 2)1, 2
>より大きい演算子[1, 2, 3]$[*] ? (@ > 2)3
>=以上演算子[1, 2, 3]$[*] ? (@ >= 2)2, 3
trueJSONのtrueリテラルとの比較に用いられる値[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]$[*] ? (@.parent == true){"name": "Chris", "parent": true}
falseJSONのfalseリテラルとの比較に用いられる値[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]$[*] ? (@.parent == false){"name": "John", "parent": false}
nullJSONのnull値との比較に用いられる値[{"name": "Mary", "job": null}, {"name": "Michael", "job": "driver"}]$[*] ? (@.job == null) .name"Mary"
&&論理AND[1, 3, 7]$[*] ? (@ > 1 && @ < 5)3
||論理OR[1, 3, 7]$[*] ? (@ < 1 || @ > 5)7
!論理NOT[1, 3, 7]$[*] ? (!(@ < 5))7
like_regex 最初のオペランドが2番目のオペランドで与えられる正規表現にマッチするかどうかテストする。 オプションでflag文字列で記述される変更を伴う。（9.15.2.2参照） ["abc", "abd", "aBdC", "abdacb", "babc"]$[*] ? (@ like_regex "^ab.*c" flag "i")"abc", "aBdC", "abdacb"
starts with2番目の文字列が1番目のオペランドの最初の部分文字列かどうかをテストする["John Smith", "Mary Stone", "Bob Johnson"]$[*] ? (@ starts with "John")"John Smith"
existsパス式が少なくとも一つのSQL/JSON項目とマッチするかどうかをテストする{"x": [1, 2], "y": [2, 4]}strict $.* ? (exists (@ ? (@[*] > 2)))2, 4
is unknown論理条件がunknownかどうかをテストする[-1, 2, 7, "infinity"]$[*] ? ((@ > 0) is unknown)"infinity"

前へ 上へ 次へ
9.14. XML関数 ホーム 9.16. シーケンス操作関数