Index zum Suchen eines Elements in einem JSON-Array

Ich habe eine Tabelle, die wie folgt aussieht:

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

Gibt es mehrere andere Spalten sind nicht relevant für diese Frage. Es gibt einen Grund zu haben, Sie als JSON gespeichert.

Was ich versuche zu tun, ist die lookup-ein track, der eine bestimmte Künstler name (genaue übereinstimmung).

Bin ich mit dieser Abfrage:

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

beispielsweise

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

Allerdings bedeutet dies einen full table scan, und es ist nicht sehr schnell. Ich habe versucht, erstellen ein GIN-index mit Hilfe einer Funktion names_as_array(artists)und verwendet 'ARTIST NAME' = ANY names_as_array(artists) allerdings der index nicht verwendet wird und die Abfrage ist tatsächlich deutlich langsamer.

InformationsquelleAutor der Frage JeffS | 2013-08-23

  1. 102

    jsonb in Postgres 9.4+

    Mit der neuen binary-JSON-Datentyp jsonbPostgres 9.4 eingeführt weitgehend verbesserte index-Optionen. Sie können nun eine GIN-index auf ein jsonb array direkt:

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

    Keine Notwendigkeit für eine Funktion zum konvertieren das array. Dies würde die Unterstützung eines Abfrage:

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

    @> mit der neuen jsonb "enthält" - operatorbei denen der GIN-index. (Nicht für Typ json nur jsonb!)

    Oder verwenden Sie die mehr spezialisierten, nicht-Standard-GIN-operator-Klasse jsonb_path_ops für den index:

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

    Gleiche Abfrage.

    Derzeit jsonb_path_ops unterstützt nur die @> Betreiber. Aber es ist in der Regel viel kleiner und schneller. Es gibt mehrere index-Optionen, details im Handbuch.

    Wenn artists enthält nur Namen, wie im Beispiel, es wäre effizienter, speichern Sie eine weniger redundante JSON-Wert zu beginnen mit: nur die Werte als text primitive und die redundante Schlüssel können Sie in der Spalte name.

    Beachten Sie den Unterschied zwischen JSON-Objekten und primitiven Typen:

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

    Abfrage:

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

    ? funktioniert nicht für Objekt Wertenur Schlüssel und array-Elemente.

    Oder (effizienter, wenn Namen Häufig wiederholt):

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

    Abfrage:

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

    json in Postgres 9.3+

    Sollte diese Arbeit mit einem UNVERÄNDERLICH Funktion:

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

    Erstellen dieser funktionale index:

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

    Verwenden und eine Abfrage wie diese. Der Ausdruck in der WHERE - Klausel hat der übereinstimmen, die im index:

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

    Aktualisiert mit feedback in die Kommentare. Wir müssen array Operatoren zur Unterstützung der GIN-index.

    Die "ist enthalten" - operator <@ in diesem Fall.

    Hinweise auf die Funktion der Volatilität

    Können Sie erklären Ihre Funktion IMMUTABLE auch wenn json_array_elements() nicht nicht.

    Die meisten JSON Funktionen verwendet werden, um nur STABLE, nicht IMMUTABLE. Es war eine Diskussion auf der Hacker Liste zu ändern. Die meisten sind IMMUTABLE jetzt. Überprüfen mit:

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

    Funktionale Indizes funktionieren nur mit IMMUTABLE Funktionen.

    InformationsquelleAutor der Antwort Erwin Brandstetter

Schreibe einen Kommentar