PostgreSQL で JSON データ内の配列要素を効率的にクエリする方法

PostgreSQL の JSON データから配列要素を取得する

PostgreSQL の JSON データ型 (バージョン 9.3 で導入) により、複雑なデータ構造の保存と管理が簡素化されます。 ただし、JSON 配列内の特定の要素を効率的にクエリするのは難しい場合があります。 このガイドでは、特にオブジェクトの配列内の 'src' フィールドに焦点を当てて、配列要素を取得するための効果的な戦略を示します。

PostgreSQL 9.4 以降向けに最適化されたアプローチ (jsonb)

PostgreSQL 9.4 以降のバージョンは、優れた jsonb データ型を提供し、強化されたパフォーマンスと機能を提供します。 最も効率的な方法は、@> 演算子と jsonb_array_elements():

を利用する方法です。

<code class="language-sql">SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]';</code>

この簡潔なクエリは、'objects' 配列に、指定された基準に一致する要素が含まれているかどうかを直接チェックします。

PostgreSQL 9.3 以降のソリューション (json)

PostgreSQL 9.3 以降 (json タイプを使用) の場合、json_array_elements() 関数は横結合内で使用されます。

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

このアプローチでは配列のネストが解除され、個々の要素を検査できるようになります。

重要な注意事項:

• これらの例は、JSON 配列内の単一レベルのネストを想定しています。 より深くネストされた配列には、追加の unnest 呼び出しまたは横方向結合が必要になります。
• 大量のデータセットの場合、クエリのパフォーマンスを最適化するには、「オブジェクト」式にインデックスを作成することが重要です。 これにより、検索プロセスが大幅に高速化されます。

以上がPostgreSQL で JSON データ内の配列要素を効率的にクエリする方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。