JSON データ・タイプ演算子

json および jsonb データ・タイプで使用できる演算子について説明します。

json タイプと jsonb タイプの両方に、演算子の並列バリアントがあります。 フィールド/要素/パス抽出演算子は、左入力と同じ型('jsonまたは'jsonb)を返しますが、'text返すと指定された演算子は例外です。 フィールド/要素/パス抽出演算子は、JSON入力がリクエストにマッチする適切な構造を持っていない場合、失敗するのではなく、NULLを返します。 整数 JSON 配列添字を受け入れるフィールド/要素/パスの抽出演算子はすべて、配列の末尾からの負の添字付けをサポートします。

注:'#>および '#>>演算子は、JSONB 配列を右オペランドとして使用する。

json および jsonb で使用可能な演算子

->
右オペランド・タイプ: int
JSON 配列要素 (ゼロから索引付けされ、負の整数は末尾からカウントされる) を取得します。
例:
  • SELECT '["football", "volleyball", "soccer"]'::JSONB -> 0;
      ?COLUMN?  
    ------------
     "football"
    (1 row)
  • SELECT '["football", "volleyball", "soccer"]'::JSONB -> -1;
     ?COLUMN? 
    ----------
     "soccer"
    (1 row)
->
右オペランド・タイプ: text
キーによってファイルされた JSON オブジェクトを取得します。
例:
  • SELECT '{"name": "Joe Smith", "age": 28}'::JSONB -> 'name';
      ?COLUMN?   
    -------------
     "Joe Smith"
    (1 row)
  • SELECT '{"name": "Joe Smith", "age": 28}'::JSONB -> 'age';
     ?COLUMN? 
    ----------
     28
    (1 row)
  • SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football", "volleyball", "soccer"]}'::JSONB -> 'sports' -> 0;
      ?COLUMN?  
    ------------
     "football"
    (1 row)
->>
右オペランド・タイプ: int
JSON配列の要素を'nvarcharとして取得する。
例:
  • SELECT '["football", "volleyball", "soccer"]'::JSONB ->> 0;
     ?COLUMN? 
    ----------
     football
    (1 row)
  • SELECT '["football", "volleyball", "soccer"]'::JSONB ->> -1;
     ?COLUMN? 
    ----------
     soccer
    (1 row)
->>
右オペランド・タイプ: text
JSONオブジェクト・フィールドを'nvarcharとして取得する。
例:
  • SELECT '{"name": "Joe Smith", "age": 28}'::JSONB ->> 'name';
     ?COLUMN?  
    -----------
     Joe Smith
    (1 row)
  • SELECT '{"name": "Joe Smith", "age": 28}'::JSONB ->> 'age';
     ?COLUMN? 
    ----------
     28
    (1 row)
    
  • SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football", "volleyball", "soccer"]}'::JSONB -> 'sports' ->> 0;
     ?COLUMN? 
    ----------
     football
    (1 row)
#>
右オペランド・タイプ: JSONB 配列
関数: json_extract_path、jsonb_extract_path
指定されたパスの JSON オブジェクトを取得します。
例:
  • SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football", "volleyball", "soccer"]}'::JSONB #> '["sports", 0]';
      ?COLUMN?  
    ------------
     "football"
    (1 row)
#>>
右オペランド・タイプ: JSONB 配列
関数名: json_extract_path_text、jsonb_extract_path_text
指定したパスにあるJSONオブジェクトを'nvarcharとして取得する。
例:
  • SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football", "volleyball", "soccer"]}'::JSONB #>> '["sports", 0]';
     ?COLUMN? 
    ----------
     football
    (1 row)

この資料の次のセクションで説明するように、jsonb の場合にのみ存在する追加の演算子もあります。

jsonb で使用可能な演算子

注意:一部のNPSドライバーは、クエスチョンマークを含むオペレーターをサポートしていません。 代わりに、関数を使用する必要があります。 また、?| および ?& 演算子は、右オペランド・タイプとして JSONB 配列を使用します。
?
右オペランド・タイプ: text
関数名: jsonb_exists
ストリングは JSON 値内の最上位キーとして存在しますか?
例:
  • SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football"]}'::JSONB ? 'age';
     ?COLUMN? 
    ----------
     t
    (1 row)
  • SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football"]}'::JSONB ? 'address';
     ?COLUMN? 
    ----------
     f
    (1 row)
  • SELECT '{"n":null,"a":1,"b":[1,2],"c":{"1":2},"d":{"1":[2,3]},"一":"二"}'::jsonb ? 'e';
     ?COLUMN? 
    ----------
     f
    (1 row)
  • SELECT jsonb_exists('{"一":"null", "二":"qq"}', '三');
     JSONB_EXISTS 
    --------------
     f
    (1 row)
ヒント疑問符(?)は、ODBCとOLEDBドライバのパラメータ・プレースホルダとして使用されます。 キーの存在をチェックするには、「jsonb_exists()使わなければならない:
例:
  • SELECT jsonb_exists('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', 'age');
     JSONB_EXISTS 
    --------------
     t
    (1 row)
    
  • SELECT jsonb_exists('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', 'address');
     JSONB_EXISTS 
    --------------
     f
    (1 row)
?|
右オペランド・タイプ: JSONB 配列
関数: jsonb_exists_any

オーバーロード関数の項も参照のこと。

これらの配列ストリングのいずれかが最上位キーとして存在しますか?
例:
  • SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football"]}'::JSONB ?| '["age", "address"]';
     ?COLUMN? 
    ----------
     t
    (1 row)
  • SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football"]}'::JSONB ?| '["mobile", "address"]';
     ?COLUMN? 
    ----------
     f
    (1 row)
    ヒント疑問符(?)は、ODBCとOLEDBドライバのパラメータ・プレースホルダとして使用されます。 キーの存在をチェックするには、「jsonb_exists_any()使わなければならない:
    例:
    • SELECT jsonb_exists_any('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', '["age", "address"]');
       JSONB_EXISTS_ANY 
      ------------------
       t
      (1 row)
    • SELECT jsonb_exists_any('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', '["mobile", "address"]');
       JSONB_EXISTS_ANY 
      ------------------
       f
      (1 row)
?&
右オペランド・タイプ: JSONB 配列
関数: jsonb_exists_all

オーバーロード関数の項も参照のこと。

これらの配列ストリングのすべてが最上位キーとして存在しますか?
例:
  • SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football"]}'::JSONB ?& '["age", "address"]';
     ?COLUMN? 
    ----------
     f
    (1 row)
  • SELECT '{"name": "Joe Smith", "age": 28, "sports": ["football"]}'::JSONB ?& '["age", "sports"]';
     ?COLUMN? 
    ----------
     t
    (1 row)
    ヒント疑問符(?)は、ODBCとOLEDBドライバのパラメータ・プレースホルダとして使用されます。 キーの存在をチェックするには、「jsonb_exists_all()使わなければならない:
    例:
    • SELECT jsonb_exists_all('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', '["age", "address"]');
       JSONB_EXISTS_ALL 
      ------------------
       f
      (1 row)
    • SELECT jsonb_exists_all('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', '["age", "sports"]');
       JSONB_EXISTS_ALL 
      ------------------
       t
      (1 row)
@?
右オペランド型: jsonpath
関数: jsonb_path_exists()
jsonpathが、指定されたjsonb値に対して少なくとも1つの項目を返す場合、trueを返します。
例:
  • SELECT '2'                @? '$ > 1'::JSONPATH;
     ?COLUMN? 
    ----------
     t
    (1 row)
  • select jsonb '{"a": 12}' @? '1';
     ?COLUMN? 
    ----------
     t
    (1 row)
  • select jsonb '["1",2,0,3]' @? '-$[*]';
     ?COLUMN? 
    ----------
     t
    (1 row)
@@
右オペランド型: jsonpath
関数: jsonb_path_match
指定されたJSON値に対するJSONパス述語チェックの結果を返します。 結果の最初の項目だけが考慮される。 結果がブール値でない場合は、NULLが返される
例:
  • SELECT '2'::NCHAR(100)    @@ '$ > 1'::JSONPATH;
     ?COLUMN? 
    ----------
     t
    (1 row)
  • SELECT '2'::JSONB @@ '$ > 1'::JSONPATH;
     ?COLUMN? 
    ----------
     t
    (1 row)
  • SELECT '2'::JSONB @@ '1'::INT;
    ERROR:  Cannot convert 'INT4' to 'JSONB'
    SELECT '2'::JSONB @@ '1'::JSONB;
    ERROR:  Unable to identify an operator '@@' for types 'JSONB' and 'JSONB'
    	You will have to retype this query using an explicit cast
    SELECT '2'::JSONB @@ 'true'::BOOL ;
    ERROR:  Cannot convert 'BOOL' to 'JSONB'
について @? @@演算子は抑制する:
  • オブジェクト・フィールドまたは配列要素がありません。
  • 予期しないJSON項目タイプ、および数値エラー。
これは、さまざまな構造のJSONドキュメント・コレクションを検索する場合に役立つだろう。

包含および存在

隔離

包含のテストは、jsonb データ・タイプの重要な機能です。 json タイプには、このような機能の並列セットはありません。 包含は、jsonb 文書に別の文書が含まれているかどうかをテストします。

含まれているオブジェクトは、構造およびデータの内容に関して含んでいるオブジェクトと一致している必要があります (含んでいるオブジェクトから、一致しない配列要素またはオブジェクト・キー/値のペアを廃棄した後でもかまいません)。 包含マッチングを実行する場合、配列要素の順序は重要ではなく、重複する配列要素は事実上 1 回のみ考慮されます。

例:
  • 単純なスカラー/プリミティブ値には、同じ値のみが含まれます。
    SELECT '"foo"'::jsonb @> '"foo"'::jsonb;
    ?COLUMN? 
    ----------
     t
    (1 row)
  • 右側の配列は、左側の配列内に含まれます。
    SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;
    ?COLUMN? 
    ----------
     t
    (1 row)
  • 配列要素の順序は重要ではないため、以下のことも当てはまります。
    SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;
    ?COLUMN? 
    ----------
     t
    (1 row)
  • 配列要素の重複は、以下のいずれにも関係ありません。
    SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;
    ?COLUMN? 
    ----------
     t
    (1 row)
  • 右側に単一のペアがあるオブジェクトは、左側のオブジェクト内に含まれます。
    SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;
    ?COLUMN? 
    ----------
     t
    (1 row)
  • 右側の配列は、同様の配列がネストされていても、左側の配列内に含まれているとは見なされません。
    SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;
    ?COLUMN? 
    ----------
     f
    (1 row)
    しかし、ネストのレイヤーでは、次に含まれます。
    SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;
    ?COLUMN? 
    ----------
     t
    (1 row)
    同様に、包含はここでは報告されません。
    SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;
    ?COLUMN? 
    ----------
     f
    (1 row)
  • 最上位キーと空のオブジェクトが含まれています。
    SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;
    ?COLUMN? 
    ----------
     t
    (1 row)

配列にはプリミティブ値を含めることができます。プリミティブ値は、構造が一致しなければならないという原則に対する例外です。

例:
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;
?COLUMN? 
----------
 t
(1 row)
この例外は逆数ではありません。非包含はここで報告されます。
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;
?COLUMN? 
----------
 f
(1 row)

存在

jsonb には、存在演算子もあります。 存在演算子は、ストリング (text 値として指定) が、jsonb 値の最上位でオブジェクト・キーまたは配列要素として表示されるかどうかをテストします。 多数のキーまたは要素が含まれている場合、JSON オブジェクトは、包含または存在をテストするのに配列よりも適しています。 配列とは異なり、内部的に検索用に最適化されており、線形に検索する必要はない。

例:
  • ストリングが配列要素として存在します。
    SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';
    ?COLUMN? 
    ----------
     t
    (1 row)
  • ストリングがオブジェクト・キーとして存在します。
    SELECT '{"foo": "bar"}'::jsonb ? 'foo';
    ?COLUMN? 
    ----------
     t
    (1 row)
  • オブジェクト値は考慮されません。
    SELECT '{"foo": "bar"}'::jsonb ? 'bar';
    ?COLUMN? 
    ----------
     f
    (1 row)
  • 包含と同様に、存在が最上位で一致する必要があります。
    SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar';
    ?COLUMN? 
    ----------
     f
    (1 row)
  • ストリングは、プリミティブ JSON ストリングと一致する場合に存在すると見なされます。
    SELECT '"foo"'::jsonb ? 'foo';
    ?COLUMN? 
    ----------
     t
    (1 row)