私は次のようなテーブルを持っています:
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
Postgres9.4以降新しいバイナリJSONデータ型jsonb
により、Postgres9.4では大幅に改善されたインデックスオプションが導入されました。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
GINインデックスを使用できる新しい「contains」演算子です。(タイプjson
ではなく、のみjsonb
!)
またはjsonb_path_ops
、インデックスに、より特殊化されたデフォルト以外のGIN演算子クラスを使用します。
CREATE INDEX tracks_artists_gin_idx ON tracks
USING gin (artists jsonb_path_ops);
同じクエリ。
現在jsonb_path_ops
、@>
オペレーターのみをサポートしています。しかし、通常ははるかに小さく、高速です。より多くのインデックスオプション、マニュアルの詳細があります。
artists
例に示されている名前のみを保持している場合は、最初に冗長性の低いJSON値を格納する方が効率的です。テキストプリミティブとしての値と冗長キーのみを列名に含めることができます。
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
Postgres9.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インデックスをサポートするには、配列演算子を使用する必要があります。オペレータ「に含まれる」このケースでは。<@
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
。
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加