sql - que - Consulta de elemento de matriz en la columna JSON

mongodb español

Question

Recientemente se actualizó a usar PostgreSQL 9.3.1 para aprovechar las funcionalidades JSON. En mi tabla tengo una columna tipo json que tiene una estructura como esta:

{
   "id": "123",
   "name": "foo",
   "emails":[
      {
        "id": "123",
        "address": "somethinghere"
      },
      {
        "id": "456",
        "address": "soemthing"
      }
   ]
} 

Esto es solo datos ficticios para el propósito de la pregunta.

¿Es posible consultar un elemento específico en el conjunto de correos electrónicos basado en el id?
Más o menos: "devolver el correo electrónico donde id = 123)"?

Answer 1

Con una columna JSONB en Postgres 9.4+, puede usar el operador contains @> para consultar un elemento en una matriz:

SELECT * FROM jsontest WHERE data @> ''{ "emails": [{ "id": "123" }] }'';

Consulte Consulta de elementos de matriz dentro del tipo JSON para obtener más detalles.

Aquí hay un ejemplo de trabajo:

CREATE TABLE jsontest(data JSONB NOT NULL);
INSERT INTO jsontest VALUES (
  ''{
     "name": "foo",
     "id": "123",
     "emails": 
     [
       {
         "address": "somethinghere",
         "id": "123"
       },
       {
         "address": "soemthing",
         "id": "456"
       }
     ]
  }''
);
SELECT * FROM jsontest WHERE data @> ''{ "emails": [{ "id": "123" }] }'';

data
----
{"id": "123", "name": "foo", "emails": [{"id": "123", "address": "somethinghere"}, {"id": "456", "address": "soemthing"}]}

(1 fila)

Answer 2

Encontré esta publicación y descubrí que puedes consultar directamente en una tabla como esta:

SELECT *
FROM   table_name, json_array_elements(json_column) AS data
WHERE  data->>''id'' = 123;

Omitiendo esta parte:

json_array_elements(t.json_col->''emails'')

Answer 3

Puedes hacerlo tan simple como:

SELECT * FROM table WHERE emails->>''id'' = ''123'';

parece que guardas el ID como una cadena; si fuera un número entero, puedes hacerlo así:

SELECT *  from table WHERE cast(emails->>''id'' as integer ) = 123  ;

o puede obtener todas las filas con id> 10

SELECT *  from table WHERE cast(emails->>''id'' as integer ) > 10  ;

Answer 4

Sí, eso es posible:

SELECT *
FROM   tbl t, json_array_elements(t.json_col->''emails'') AS elem
WHERE  elem->>''id'' = 123;

tbl es el nombre de su tabla, json_col es el nombre de la columna JSON.

Más detalles en esta respuesta relacionada:

• ¿Cómo consulto usando campos dentro del nuevo tipo de datos PostgreSQL JSON?

Más sobre el CROSS JOIN LATERAL implícito en el último párrafo de esta respuesta relacionada:

• PostgreSQL unnest () con número de elemento

Índice para admitir este tipo de consulta:

• Índice para encontrar un elemento en una matriz JSON