PostgreSQL で JSON 配列内の特定の要素をクエリするにはどうすればよいですか?

PostgreSQL での JSON 配列要素のクエリ

PostgreSQL 9.3 以降は JSON データ型をサポートしており、JSON データをデータベースに保存して操作できるようになります。この機能により、複雑なデータ構造 (オブジェクトの配列など) を 1 つの列に格納できます。ただし、これらの複雑な構造内の特定の要素または値をクエリするのは困難な場合があります。

「reports」テーブルの「data」という名前の列に次の JSON データが格納されていると仮定します。

<code class="language-json">{
  "objects": [
    {"src":"foo.png"},
    {"src":"bar.png"}
  ],
  "background":"background.png"
}</code>

一般的なタスクは、「object」配列の「src」フィールドから特定の値を含むすべてのレポートを取得することです。 「背景」のようなフラットな JSON フィールドのクエリは簡単ですが、配列内の要素を抽出するには別のアプローチが必要です。

次のクエリは、指定された「背景」値に一致するすべてのレポートを正常に取得します:

<code class="language-sql">SELECT data AS data FROM reports WHERE data->>'background' = 'background.png'</code>

ただし、同様の構文 (たとえば) を使用して、「objects」配列内の値をクエリしてみてください:

<code class="language-sql">SELECT data AS data FROM reports WHERE data->>'objects' = 'foo.png'</code>

「オブジェクト」は単純なフィールドではなく配列であるため、

は期待した結果を生成しません。

JSON 配列内の要素を正常にクエリするには、水平結合で json_array_elements() 関数を使用します。この関数は配列から単一の要素を抽出し、各要素を個別にテストできるようにします。

次のクエリは、横結合を使用して「objects」配列を展開し、特定の「src」値の存在をテストします。

<code class="language-sql">SELECT data AS data
FROM reports r, json_array_elements(r.data#>'{objects}') obj
WHERE obj->>'src' = 'foo.png';</code>

単一レベルのネストの場合は、クエリの簡略化されたバージョンを使用することもできます。

<code class="language-sql">SELECT *
FROM reports r, json_array_elements(r.data->>'objects') obj
WHERE obj->>'src' = 'foo.png';</code>

これらのクエリは、#>、->、および ->> 演算子を使用して JSON 構造を走査します。これらの演算子の詳細については、PostgreSQL のドキュメントを参照してください。

さらに、配列を含む複雑な JSON クエリのパフォーマンスを最適化するには、関連する JSON 式に GIN インデックスを追加することを検討してください。これにより、特に大規模なデータ セットの場合、検索速度が大幅に向上します。

以上がPostgreSQL で JSON 配列内の特定の要素をクエリするにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。