Index pour trouver un élément dans un tableau JSON

Question

J'ai un tableau qui ressemble à ceci :

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

Il y a plusieurs autres colonnes qui ne sont pas pertinentes pour cette question. Il y a une raison pour qu'elles soient stockées en JSON.

Ce que j'essaie de faire, c'est de rechercher une piste qui a un nom spécifique nom de l'artiste (correspondance exacte).

J'utilise cette requête :

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

par exemple

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

Cependant, cette opération consiste à balayer l'ensemble de la table, ce qui n'est pas très rapide. J'ai essayé de créer un index GIN à l'aide d'une fonction names_as_array(artists) et utilisé 'ARTIST NAME' = ANY names_as_array(artists) Cependant, l'index n'est pas utilisé et la requête est en fait beaucoup plus lente.

Answer 1

jsonb dans Postgres 9.4 et plus

Le type de données binaires JSON jsonb améliore largement les options d'indexation. Il est désormais possible d'avoir un index GIN sur une jsonb directement :

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

Pas besoin d'une fonction pour convertir le tableau. Cela supporterait une requête :

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

@> étant le jsonb opérateur "contient". qui peut utiliser l'indice GIN. (Pas pour json seulement jsonb !)

Ou vous utilisez la classe d'opérateurs GIN plus spécialisée, qui n'est pas la classe par défaut. jsonb_path_ops pour l'index :

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

Même question.

Actuellement jsonb_path_ops ne prend en charge que le @> opérateur. Mais il est généralement beaucoup plus petit et plus rapide. Il y a plus d'options d'indexation, détails dans le manuel .

---

Si la colonne artists ne retient que les noms tels qu'ils sont affichés dans l'exemple, il serait plus efficace de ne stocker que le nom de l'entreprise. valeurs en tant que texte JSON primitives et la redondance clé peut être le nom de la colonne.

Notez la différence entre les objets JSON et les types primitifs :

• Utilisation des index dans un tableau json dans 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);

Une requête :

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

? ne fonctionne pas pour l'objet valeurs juste clés y éléments du tableau .

Ou :

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

Une requête :

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

Plus efficace si les noms sont fortement dupliqués.

json dans Postgres 9.3 et plus

Cela devrait fonctionner avec un IMMUTABLE fonction :

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

Créez cette fonctionnel indice :

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

Et utilisez un requête comme ça. L'expression dans le WHERE doit correspondre à celle de l'index :

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

Mis à jour avec les réactions dans les commentaires. Nous devons utiliser opérateurs de tableau pour soutenir l'indice GIN.
Le site opérateur "est contenu par" <@ dans ce cas.

Notes sur la volatilité des fonctions

Vous pouvez déclarer votre fonction IMMUTABLE même si json_array_elements() n'est pas ne l'était pas.
Le plus JSON Les fonctions n'étaient auparavant que STABLE pas IMMUTABLE . Il y a eu une discussion sur la liste des hackers pour changer cela. La plupart sont IMMUTABLE maintenant. Vérifiez avec :

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

Les index fonctionnels ne fonctionnent qu'avec IMMUTABLE fonctions.