tipo - json type sql

Consulta de elementos de la matriz dentro del tipo JSON

Question

Estoy tratando de probar el tipo json en PostgreSQL 9.3.
Tengo una columna json llamada data en una tabla llamada reports . El JSON se ve así:

{
  "objects": [
    {"src":"foo.png"},
    {"src":"bar.png"}
  ],
  "background":"background.png"
}

Me gustaría consultar en la tabla todos los informes que coinciden con el valor ''src'' en la matriz de ''objetos''. Por ejemplo, ¿es posible consultar el DB para todos los informes que coinciden con ''src'' = ''foo.png'' ? Escribí con éxito una consulta que puede coincidir con el "background" :

SELECT data AS data FROM reports where data->>''background'' = ''background.png''

Pero como "objects" tiene una matriz de valores, parece que no puedo escribir algo que funcione. ¿Es posible consultar el DB para todos los informes que coincidan con ''src'' = ''foo.png'' ? Miré a través de estas fuentes pero aún no puedo obtenerlo:

• http://www.postgresql.org/docs/9.3/static/functions-json.html
• ¿Cómo consulto usando campos dentro del nuevo tipo de datos PostgreSQL JSON?
• http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/

También probé cosas como esta, pero fue en vano:

SELECT json_array_elements(data->''objects'') AS data from reports
WHERE  data->>''src'' = ''foo.png'';

No soy un experto en SQL, así que no sé lo que estoy haciendo mal.

Answer 1

json en Postgres 9.3+

Unnest la matriz JSON con la función json_array_elements() en una unión lateral en la cláusula FROM y prueba sus elementos:

WITH reports(data) AS (
   VALUES (''{"objects":[{"src":"foo.png"}, {"src":"bar.png"}]
           , "background":"background.png"}''::json)
   ) 
SELECT *
FROM   reports r, json_array_elements(r.data#>''{objects}'') obj
WHERE  obj->>''src'' = ''foo.png'';

El CTE (consulta WITH ) simplemente sustituye a los reports una tabla.
O, equivalente para un solo nivel de anidación:

SELECT *
FROM   reports r, json_array_elements(r.data->''objects'') obj
WHERE  obj->>''src'' = ''foo.png'';

->> operadores ->> , -> y #> se explican en el manual.

Ambas consultas usan un JOIN LATERAL implícito.

SQL Fiddle.

Respuesta estrechamente relacionada:

• Consulta de elemento de matriz en la columna JSON

jsonb en Postgres 9.4+

Use el equivalente json_array_elements() .

Mejor aún, use el nuevo operador "contiene" @> (el mejor en combinación con un índice GIN coincidente en los data->''objects'' expresión data->''objects'' ):

CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->''objects'') jsonb_path_ops);

SELECT * FROM reports WHERE data->''objects'' @> ''[{"src":"foo.png"}]'';

Como los objects clave contienen una matriz JSON, debemos hacer coincidir la estructura en el término de búsqueda y envolver el elemento de la matriz entre corchetes también. Suelte los corchetes de la matriz al buscar un registro simple.

Explicación detallada y más opciones:

• Índice para encontrar un elemento en una matriz JSON