PostgreSQL で JSON 配列内の要素を効率的に検索するにはどうすればよいですか?

PostgreSQL で JSON 配列要素を効率的に検索するためのヒント

JSON 配列内の特定の要素の検索を最適化するには、PostgreSQL のバージョンとデータ構造を必ず考慮してください。

PostgreSQL 9.4 と JSONB

PostgreSQL 9.4 以降では、バイナリ JSON データ型 jsonb により、インデックス作成機能が大幅に向上しました。 jsonb 配列に GIN インデックスを直接作成できます:

<code class="language-sql">CREATE TABLE tracks (id serial, artists jsonb);  -- !
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);</code>

このインデックスにより、@> 演算子を使用した効率的なクエリが可能になります:

<code class="language-sql">SELECT * FROM tracks WHERE artists @> '[{"name": "The Dirty Heads"}]';</code>

または、特殊な jsonb_path_ops GIN 演算子クラスを使用することもできます:

<code class="language-sql">CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (artists jsonb_path_ops);  -- !</code>

クエリステートメントは変更されません。

PostgreSQL 9.3 と JSON

PostgreSQL 9.3 の場合は、IMMUTABLE 関数を使用して JSON 配列の要素をテキスト値の配列に抽出することを検討してください。

<code class="language-sql">CREATE OR REPLACE FUNCTION json2arr(_j json, _key text)
  RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT elem->>_key FROM json_array_elements(_j) elem)';</code>

次に、抽出された配列に関数 GIN インデックスを作成します。

<code class="language-sql">CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (json2arr(artists, 'name'));</code>

クエリでは、配列演算子を使用してインデックス式を一致させます:

<code class="language-sql">SELECT * FROM tracks
WHERE  '{"The Dirty Heads"}'::text[] <@ (json2arr(artists, 'name'));</code>

関数のインデックス付けは

関数でのみ使用できることに注意してください。 IMMUTABLE 関数と使用されるその他の JSON 関数の変更可能性を検証します。 json_array_elements()

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