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