Índice para encontrar un elemento en una matriz JSON

Resuelto JeffS asked hace 11 años • 1 respuestas

Tengo una tabla que se parece a esta:

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

Hay varias otras columnas que no son relevantes para esta pregunta. Hay una razón para almacenarlos como JSON.

Lo que intento hacer es buscar una pista que tenga un nombre de artista específico. específico (coincidencia exacta).

Estoy usando esta consulta:

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

Por ejemplo

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

Sin embargo, esto realiza un escaneo completo de la tabla y no es muy rápido. Intenté crear un índice GIN usando una función names_as_array(artists)y la usé 'ARTIST NAME' = ANY names_as_array(artists), sin embargo, el índice no se usa y la consulta es significativamente más lenta.

JeffS avatar Aug 23 '13 20:08 JeffS
Aceptado

jsonben Postgres 9.4+

El tipo de datos binario JSON jsonbmejora en gran medida las opciones de índice. Ahora puedes tener un índice GIN en una jsonbmatriz directamente:

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

No es necesaria una función para convertir la matriz. Esto apoyaría una consulta:

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

@>siendo el jsonboperador "contiene" , que puede utilizar el índice GIN. (¡No jsonsolo para jsonb!)

O puede utilizar la clase de operador GIN más especializada y no predeterminada jsonb_path_opspara el índice:

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

Misma consulta.

Actualmente jsonb_path_opssolo es compatible con el @>operador. Pero normalmente es mucho más pequeño y más rápido. Hay más opciones de índice, detalles en el manual .

Si la columna artistssolo contiene nombres como se muestra en el ejemplo, sería más eficiente almacenar solo los valores como primitivos de texto JSON y la clave redundante puede ser el nombre de la columna.

Tenga en cuenta la diferencia entre objetos JSON y tipos primitivos:

  • Usando índices en matriz json en 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);

Consulta:

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

?no funciona para valores de objetos , sólo claves y elementos de matriz .

O:

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

Consulta:

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

Más eficiente si los nombres se duplican mucho.

jsonen Postgres 9.3+

Esto debería funcionar con una IMMUTABLE función :

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

Cree este índice funcional :

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

Y utilice una consulta como esta. La expresión de la WHEREcláusula tiene que coincidir con la del índice:

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

Actualizado con comentarios en los comentarios. Necesitamos utilizar operadores de matriz para admitir el índice GIN.
En este caso, el operador "está contenido por"<@ .

Notas sobre la volatilidad de la función

Puede declarar su función IMMUTABLEincluso si json_array_elements() no lo fue.
La mayoría de JSONlas funciones solían ser sólo STABLE, no IMMUTABLE. Hubo una discusión sobre la lista de hackers para cambiar eso. La mayoría lo son IMMUTABLEahora. Comprobar con:

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

Los índices funcionales solo funcionan con IMMUTABLEfunciones.

Erwin Brandstetter avatar Aug 23 '2013 14:08 Erwin Brandstetter