[解決済み] JSON配列の要素を検索するためのインデックス

質問

以下のようなテーブルがあります。

CREATE TABLE tracks (id SERIAL, artists JSON);

INSERT INTO tracks (id, artists) 
  VALUES (1, '[{"name": "blink-182"}]');

INSERT INTO tracks (id, artists) 
  VALUES (2, '[{"name": "The Dirty Heads"}, {"name": "Louis Richards"}]');

この質問には関係ありませんが、他にもいくつかのカラムがあります。それらをJSONとして保存する理由があるのです。

私がやろうとしていることは、特定の アーティスト名 を持つトラックを検索することです (完全一致)。

このクエリで

SELECT * FROM tracks 
  WHERE 'ARTIST NAME' IN
    (SELECT value->>'name' FROM json_array_elements(artists))

例えば

SELECT * FROM tracks
  WHERE 'The Dirty Heads' IN 
    (SELECT value->>'name' FROM json_array_elements(artists))

しかし、これはフルテーブルスキャンを行うので、あまり高速ではありません。関数を使ってGINインデックスを作成してみました。 names_as_array(artists) を使用し、そして 'ARTIST NAME' = ANY names_as_array(artists) を使用した場合は、インデックスが使用されず、クエリの実行速度が大幅に低下します。

どのように解決するのですか？

jsonb Postgres 9.4+で

バイナリJSONデータ型 jsonb は、インデックスのオプションを大きく改善しました。GIN インデックスを jsonb 配列に直接 GIN インデックスを持てるようになりました。

CREATE TABLE tracks (id serial, artists jsonb);  -- !
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);

配列を変換する関数は必要ありません。これならクエリに対応できる。

SELECT * FROM tracks WHERE artists @> '[{"name": "The Dirty Heads"}]';

@> である。 jsonb "contains"演算子です。 で、GINインデックスを使用することができます。(ただし json だけです。 jsonb !)

または を使う場合は、より特殊でデフォルトではない GIN 演算子クラスである jsonb_path_ops をインデックスとして使用します。

CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (artists jsonb_path_ops);  -- !

同じクエリで

現在 jsonb_path_ops のみをサポートしています。 @> 演算子のみをサポートします。しかし、一般的にはるかに小さく、高速です。インデックスのオプションはもっとあります。 マニュアルに詳細があります。 .

もし 列 artists がこの例で表示されているような名前だけを保持するのであれば、単に 値 をJSONテキストとして保存する方が効率的です。 プリミティブ と、冗長な キー はカラム名とすることができます。

JSONオブジェクトとプリミティブ型の違いに注意してください。

• PostgreSQLでjson配列にインデックスを使用する。

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

クエリを実行します。

SELECT * FROM tracks WHERE artistnames ? 'The Dirty Heads';

? はオブジェクトに対して動作しません の値です。 は、単に キー と 配列要素 .

または

CREATE INDEX tracks_artistnames_gin_idx ON tracks
USING  gin (artistnames jsonb_path_ops);

クエリ

SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"'::jsonb;

名前の重複が多い場合、より効率的です。

json Postgres 9.3+では

これは IMMUTABLE 機能 :

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)';

これを作成する 機能的 インデックス :

CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (json2arr(artists, 'name'));

そして クエリ のようにします。の中の式は WHERE 句の式はインデックスの式と一致しなければなりません。

SELECT * FROM tracks
WHERE  '{"The Dirty Heads"}'::text[] <@ (json2arr(artists, 'name'));

コメントでのフィードバックを受けて更新しました。私たちは 配列演算子 を使用して、GINインデックスをサポートします。

は "is contained by" 演算子 <@ をこの場合

機能揮発に関する注意点

関数を宣言する際に IMMUTABLE であっても json_array_elements() はなく はなかったことに。

最も JSON 関数は、以前は STABLE でなく IMMUTABLE . を変更するようにハッカーリストで議論されていました。 ほとんどは IMMUTABLE になっています。で確認してください。

SELECT p.proname, p.provolatile
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'pg_catalog'
AND    p.proname ~~* '%json%';

ファンクショナルインデックスでは IMMUTABLE 関数でのみ動作します。