PostgreSQL で JSON 配列に効率的にインデックスを作成し、要素をより速く取得するにはどうすればよいですか?

PostgreSQL で JSON 配列のインデックスを効率的に作成し、要素の取得を高速化します

PostgreSQL テーブルの JSON 配列内の要素を迅速に識別して取得するのは難しい場合があります。最適なパフォーマンスを得るには、特定のデータとクエリの要件に適したインデックス作成テクノロジを理解することが重要です。

PostgreSQL 9.4 での JSONB の使用

PostgreSQL 9.4 ではバイナリ JSON データ型 jsonb が導入され、インデックス作成の可能性が大幅に強化されています。 jsonb を使用して、JSON 配列に GIN インデックスを直接作成します。

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

これにより、GIN インデックスを使用できるクエリが有効になります。例:

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

ここで、@> は jsonb の「contains」演算子を表します。

代わりに、jsonb_path_ops GIN 演算子クラスを使用することもできます (デフォルトではなく、通常は小さくて高速です)。

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

一意の名前値を最適化します

artists 列に一意の名前が付けられた値の配列のみが含まれている場合は、オブジェクトではなく JSON テキスト プリミティブとして値を格納する方が効率的です。これにより、冗長なキーが不要になります。

<code class="language-sql">CREATE TABLE tracks (id serial, artistnames jsonb);
INSERT INTO tracks VALUES (2, '["The Dirty Heads", "Louis Richards"]');
CREATE INDEX tracks_artistnames_gin_idx ON tracks USING gin (artistnames);</code>

クエリの例:

<code class="language-sql">SELECT * FROM tracks WHERE artistnames ? 'The Dirty Heads';
SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"'::jsonb;</code>

PostgreSQL 9.3 での json の処理

9.4 より前のバージョンの PostgreSQL では、不変関数と GIN インデックスを使用できます。

関数の作成:

<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>

次に、関数インデックスを作成します。

<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[]</code>

関数のインデックス作成を有効にするには、インデックスで使用される関数が不変であることを常に確認してください。キーオペレーター...

この記事は、より簡潔な言葉を使用して元のテキストを書き直し、いくつかの文章を調整していますが、元のテキストの核となる内容と写真の位置は保持しています。

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