PolarDB:JSON 型

コンテインメント

@> 演算子は、ある jsonb ドキュメントが別のドキュメントに含まれているかどうかをテストします。含まれるオブジェクトは、構造とデータにおいて包含するオブジェクトと一致する必要がありますが、包含するオブジェクト内の一致しない配列要素やキー/値のペアは無視されます。配列要素の順序は重要ではなく、重複する配列要素は一度だけ考慮されます。

-- 単純なスカラー値は、同一の値のみを含みます
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;  -- true

-- 右側の配列は左側の配列に含まれています
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;  -- true

-- 配列要素の順序は重要ではありません
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;  -- true

-- 重複する配列要素は問題になりません
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;  -- true

-- 右側の単一ペアのオブジェクトは左側のオブジェクトに含まれています
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;  -- true

-- ネストされた配列は、外側のレベルでは含まれているとは見なされません
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- false

-- しかし、ネストレベルを一致させると機能します
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;  -- true

-- コンテインメントはオブジェクトのネスト境界を越えません
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- false

-- 空のオブジェクトを持つトップレベルのキーは含まれています
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;  -- true

特別な例外として、配列はプリミティブ値を含むことができます (逆はできません)：

-- 配列はプリミティブな文字列値を含みます
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;  -- true

-- 非コンテインメント：プリミティブは配列を含みません
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- false

コンテインメントはネストされているため、クエリは明示的なサブオブジェクトの選択をスキップできます。例えば、tags 配列に "term":"paris" と "term":"food" の両方を持つサブオブジェクトが含まれているドキュメントを見つけるには、次のようにします：

SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';

存在

? 演算子は、文字列が jsonb 値のトップレベルでオブジェクトキーまたは配列要素として出現するかどうかをテストします。存在はネストされません。

-- 文字列は配列要素として存在します
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';  -- true

-- 文字列はオブジェクトキーとして存在します
SELECT '{"foo": "bar"}'::jsonb ? 'foo';  -- true

-- オブジェクトの値はチェックされません
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- false

-- 存在はトップレベルでのみ一致します
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar';  -- false

-- 文字列はプリミティブな JSON 文字列と一致します
SELECT '"foo"'::jsonb ? 'foo';  -- true

JSON オブジェクトは、内部でキー検索用に最適化されており、線形検索を必要としないため、コンテインメントおよび存在テストには配列よりも適しています。

GIN 演算子クラスの選択

クエリが ?、?&、または ?| を使用する場合は jsonb_ops を使用します。クエリが @>、@?、または @@ のみを使用する場合は jsonb_path_ops を使用します。これは、特にクエリされたキーがデータ内に頻繁に出現する場合に、より小さいインデックスとより良い検索の特異性を生み出します。

jsonb_path_ops は、値を含まない JSON 構造 (例：{"a": {}}) のインデックスエントリを作成しません。このような構造のクエリには、全インデックススキャンが必要です。

GIN インデックスの作成

デフォルトの演算子クラス (jsonb_ops):

CREATE INDEX idxgin ON api USING gin (jdoc);

jsonb_path_ops 演算子クラス:

CREATE INDEX idxginp ON api USING gin (jdoc jsonb_path_ops);

サブキーの式インデックス:

CREATE INDEX idxgintags ON api USING gin ((jdoc -> 'tags'));

クエリが一貫して特定のキーを対象とする場合は、式インデックスを使用します。jdoc -> 'tags' 式インデックスは、jdoc -> 'tags' ? 'qui' のようなクエリに対して、フルドキュメント GIN インデックスよりも小さく高速です。

クエリの例

テーブル api の jsonb 列 jdoc について:

-- キー "company" の値が "Magnafone" であるドキュメントを検索
-- jdoc の GIN インデックスを使用
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

-- "tags" にキーまたは配列要素 "qui" が含まれるドキュメントを検索
-- jdoc のプレーンな GIN インデックスは使用できません。(jdoc -> 'tags') の式インデックスが必要です
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

-- GIN インデックスをサポートする jsonpath マッチング
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';

-- 上記の存在クエリの代替としてのコンテインメント
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

GIN インデックスは、@? および @@ 演算子を用いた jsonpath マッチングもサポートしています。これらの演算子を処理する際、GIN インデックスは、jsonpath パターンから accessors_chain = 定数 の形式の句を抽出し、それらの句で言及されているキーと値に基づいて検索を行います。アクセサーチェーンには、.key、[*]、および [index] アクセサーが含まれる場合があります。jsonb_ops 演算子クラスでは、.* および .** アクセサーもサポートされていますが、jsonb_path_ops 演算子クラスではサポートされていません。

B ツリーインデックスとハッシュインデックス

jsonb は、B ツリーおよびハッシュインデックスもサポートしており、主に完全な JSON ドキュメントに対する等価性チェックに使用されます。

jsonb 値の B ツリー順序は：

オブジェクト > 配列 > ブール値 > 数値 > 文字列 > Null

n 個のペアを持つオブジェクト > n - 1 個のペアを持つオブジェクト

n 個の要素を持つ配列 > n - 1 個の要素を持つ配列

ペアの数が等しいオブジェクトは、次の順序で比較されます：

キー-1, 値-1, キー-2 ...

短いキーは長いキーの前に格納されるため、直感的でない順序付けの結果が生じることがある点に注意してください：

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

要素数が等しい配列は、要素ごとに比較されます：

要素-1, 要素-2 ...

プリミティブな JSON 値は、基になる PostgreSQL 型と同じルールを使用して比較されます。文字列は、デフォルトのデータベース照合順序を使用します。

構文の規則

SQL/JSON パスのセマンティクスは SQL に従いますが、構文は JavaScript の規則を使用します：

• . メンバーへのアクセス

• [] は配列アクセス用

• 配列は 0 から始まります (1 から始まる SQL 配列とは異なります)

パス式は、シングルクォートで囲まれた SQL 文字列リテラルとして記述されます。値の中のシングルクォートは、二重にする必要があります。パス式内の埋め込み文字列リテラルは、JavaScript/ECMAScript の規則に従います。つまり、ダブルクォートを使用し、バックスラッシュエスケープがサポートされます。

サポートされているエスケープシーケンス: \b、\f、\n、\r、\t、\v（ASCII コントロール文字用）; \u*NNNN*（Unicode コードポイント用）。標準の JSON には含まれていないが、以下の 2 つの追加シーケンスもサポートされています: \x*NN*（2 桁の 16 進数字）および \u{*N...*}（1～6 桁の 16 進数字）。

jsonpath 変数

jsonpath アクセサ演算子

パス式の要素

パス式は、次の要素のシーケンスです：

• JSON プリミティブ リテラル: Unicode テキスト、数値、true、false、または null

• パス変数

• アクセサー演算子

• jsonpath の演算子とメソッド

• フィルター式または評価順序を定義するための括弧