Indeks do znajdowania elementu w tablicy JSON

Mam stół, który wygląda tak:

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"}]');

Jest kilka innych kolumn, które nie dotyczą tego pytania. Jest powód, aby przechowywać je w formacie JSON.

Próbuję wyszukać utwór o określonej nazwie wykonawcy (dopasowanie ścisłe).

Używam tego zapytania:

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

na przykład

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

Jednak powoduje to pełne skanowanie tabeli i nie jest zbyt szybkie. Próbowałem utworzyć indeks GIN za pomocą funkcji names_as_array(artists)i użyłem 'ARTIST NAME' = ANY names_as_array(artists), jednak indeks nie jest używany, a zapytanie jest w rzeczywistości znacznie wolniejsze.

jsonb w Postgres 9.4+

Wraz z nowym binarnym typem danych JSON jsonb, Postgres 9.4 wprowadził znacznie ulepszone opcje indeksowania . Możesz teraz mieć indeks GIN jsonbbezpośrednio w tablicy:

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

Nie ma potrzeby używania funkcji do konwersji tablicy. To obsługiwałoby zapytanie:

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

@>będący nowym jsonboperatorem „zawiera” , który może używać indeksu GIN. (Nie dla typu json, tylko jsonb!)

Lub możesz użyć bardziej wyspecjalizowanej, niedomyślnej klasy operatora GIN jsonb_path_opsdla indeksu:

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

To samo zapytanie.

Obecnie jsonb_path_opsobsługuje tylko @>operatora. Ale zazwyczaj jest znacznie mniejszy i szybszy. Istnieje więcej opcji indeksu, szczegóły w instrukcji .

Jeśli artists przechowuje tylko nazwy wyświetlane w przykładzie, bardziej wydajne byłoby przechowywanie mniej nadmiarowych wartości JSON na początku: tylko wartości jako prymitywy tekstowe i nadmiarowy klucz mogą znajdować się w nazwie kolumny.

Zwróć uwagę na różnicę między obiektami JSON a typami pierwotnymi:

• Używanie indeksów w tablicy json w PostgreSQL

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

Pytanie:

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

?nie działa dla wartości obiektów , tylko klucze i elementy tablicy .
Lub (bardziej efektywne, jeśli nazwy są często powtarzane):

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

Pytanie:

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

json w Postgres 9.3+

Powinno to działać z IMMUTABLE funkcją :

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

Utwórz ten indeks funkcjonalny :

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

I użyj takiego zapytania . Wyrażenie w WHEREklauzuli musi być zgodne z tym w indeksie:

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

Zaktualizowano o opinie w komentarzach. Musimy użyć operatorów tablicowych do obsługi indeksu GIN.
W tym przypadku operator „jest zawarty przez”<@ .

Uwagi dotyczące zmienności funkcji

Możesz zadeklarować swoją funkcję, IMMUTABLEnawet jeśli json_array_elements() nie jest.
Większość JSONfunkcji była tylko STABLE, a nie IMMUTABLE. Na liście hakerów odbyła się dyskusja, aby to zmienić. Większość jest IMMUTABLEteraz. Sprawdź z:

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

Indeksy funkcjonalne działają tylko z IMMUTABLEfunkcjami.