arrays - Consultando dentro de matrices JSON de Postgres

mongodb postgresql

Question

¿Cómo buscaría un elemento dentro de una matriz almacenada en una columna json ? (Actualización: también vea la respuesta actualizada para jsonb columnas jsonb 9.4).

Si tengo un documento JSON como este, almacenado en una columna json llamada blob :

{"name": "Wolf",
"ids": [185603363281305602,185603363289694211]}

Lo que me gustaría poder hacer es algo como:

SELECT * from "mytable" WHERE 185603363289694211 = ANY("blob"->''ids'');

y saca todas las filas iguales. Pero esto no funciona porque "blob"->''ids'' devuelve valores JSON, no matrices de Postgres.

También me gustaría crear un índice en las ID individuales, si eso es posible.

Answer 1

Primero, intente con el operador ->> lugar de -> para quitar la capa JSON del valor de la matriz.

A continuación, la consulta puede funcionar así:
¿Cómo hago una consulta usando campos dentro del nuevo tipo de datos PostgreSQL JSON?

Y la indexación podría funcionar así:
Índice para encontrar un elemento en una matriz JSON

Answer 2

Sé que ha pasado un tiempo ...

En postgresql-9.5, ahora es posible consultarlo fácilmente.

select ''{"name": "Wolf",
"ids": [185603363281305602,185603363289694211]}''::jsonb
@> ''{"ids":[185603363281305602]}''

Creo que deberías usar un campo jsonb y puedes indexarlo después.

CREATE INDEX idx_gin_ids ON mytable USING gin ((blob -> ''ids''));

Answer 3

La siguiente respuesta original aplica solo para Postgres 9.3. Para obtener una respuesta de Postgres 9.4, consulte la Actualización a continuación.

Esto se basa en las respuestas de referencia de Erwin , pero es un poco más explícito para esta pregunta.

Los ID en este caso son bigint s, por lo tanto, cree una función auxiliar para convertir una matriz JSON en una matriz bigint Postgres:

CREATE OR REPLACE FUNCTION json_array_bigint(_j json)
RETURNS bigint[] AS
$$
SELECT array_agg(elem::text::bigint)
FROM json_array_elements(_j) AS elem
$$
LANGUAGE sql IMMUTABLE;

Podríamos simplemente haber devuelto (y tal vez más reiterativamente) una matriz de text aquí. Sospecho que la indexación en bigint es mucho más rápida que el text pero me resulta difícil encontrar pruebas en línea que lo respalden.

Para construir el índice:

CREATE INDEX "myindex" ON "mytable"
USING GIN (json_array_bigint("blob"->''ids''));

Para consultas, esto funciona y usa el índice:

SELECT * FROM "mytable"
WHERE ''{185603363289694211}'' <@ json_array_bigint("blob"->''ids'');

Hacer esto también funcionará para consultas, pero no usa el índice:

SELECT * FROM "mytable"
WHERE 185603363289694211 = ANY(json_array_bigint("blob"->''ids''));

Actualización para 9.4

Postgres 9.4 introdujo el tipo jsonb . Esta es una buena respuesta SO sobre jsonb y cuándo debería usarla sobre json . En resumen, si alguna vez consulta el JSON, debe usar jsonb .

Si construyes tu columna como jsonb , puedes usar esta consulta:

SELECT * FROM "mytable"
WHERE blob @> ''{"ids": [185603363289694211]}'';

El operador @> Postgres ''contiene el operador, documentado para jsonb aquí . Gracias a la respuesta de Alain por llamar esto a mi atención.