sql - recognized - Índice para encontrar un elemento en una matriz JSON

open json sql 2014

Question

Tengo una mesa que se ve así:

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 muchas otras columnas que no son relevantes para esta pregunta. Hay una razón para tenerlos almacenados como JSON.

Lo que intento hacer es buscar una pista que tenga un nombre de artista 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 hace un escaneo completo de la tabla, y no es muy rápido. Traté de crear un índice GIN usando una función names_as_array(artists) , y utilicé ''ARTIST NAME'' = ANY names_as_array(artists) , sin embargo, el índice no se usa y la consulta es significativamente más lenta.

Answer 1

jsonb en Postgres 9.4+

Con el nuevo tipo de datos JSON binarios jsonb , Postgres 9.4 introdujo opciones de índice ampliamente mejoradas . Ahora puede tener un índice GIN en una matriz jsonb directamente:

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

No hay necesidad de una función para convertir la matriz. Esto apoyaría una consulta:

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

@> es el nuevo jsonb "contains" , que puede usar el índice GIN. (No para el tipo json , solo jsonb !)

O utiliza la clase de operador de GIN más especializada y no predeterminada jsonb_path_ops para el índice:

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

Misma consulta

Actualmente jsonb_path_ops solo admite el operador @> . Pero es típicamente mucho más pequeño y más rápido. Hay más opciones de índice, detalles en el manual .

Si los artists solo conservan los nombres tal como se muestran en el ejemplo, sería más eficiente almacenar un valor JSON menos redundante para comenzar: solo los valores como primitivas de texto y la clave redundante pueden estar en el nombre de la columna.

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

• Usar índices en json array 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 objeto, solo claves y elementos de matriz .
O (más eficiente si los nombres se repiten a menudo):

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

Consulta:

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

json en Postgres 9.3+

Esto debería funcionar con una función 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)'';

Crea este índice funcional :

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

Y usa una consulta como esta. La expresión en la cláusula WHERE debe coincidir con la del índice:

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

Actualizado con comentarios en comentarios. Necesitamos usar operadores de matriz para soportar el índice GIN.
El operador "está contenido por" <@ en este caso.

Notas sobre la volatilidad de la función

Puede declarar su función IMMUTABLE incluso si json_array_elements() no lo era.
La mayoría de las funciones JSON solían ser STABLE , no IMMUTABLE . Hubo una discusión en la lista de piratas informáticos para cambiar eso. La mayoría son IMMUTABLE ahora. 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 funciones IMMUTABLE .