Requête pour l'élément de tableau dans la colonne JSON
récemment mis à jour en utilisant PostgreSQL 9.3.1 pour tirer parti des fonctionnalités js. Dans ma table j'ai une colonne de type json qui a une structure comme ceci:
{
"id": "123",
"name": "foo",
"emails":[
{
"id": "123",
"address": "somethinghere"
},
{
"id": "456",
"address": "soemthing"
}
]
}
il s'agit simplement de données fictives pour les besoins de la question.
Est-il possible de requête pour un élément spécifique dans le tableau emails en fonction de l'id?
À peu près: "retour email où id = 123)"?
4 réponses
Oui, c'est possible:
SELECT *
FROM tbl t, json_array_elements(t.json_col->'emails') AS elem
WHERE elem->>'id' = 123;
tbl étant votre nom de table, json_col étant le nom de la colonne JSON.
plus de détails dans cette réponse liée:
pour en savoir plus sur le CROSS JOIN LATERAL implicite dans le dernier paragraphe de cette réponse connexe:
de l'Indice à l'appui de ce type de requête:
avec une colonne JSONB dans Postgres 9.4+ vous pouvez utiliser l'opérateur contains @> pour rechercher un élément dans un tableau:
SELECT * FROM jsontest WHERE data @> '{ "emails": [{ "id": "123" }] }';
Voir Requête pour les éléments du tableau à l'intérieur de JSON type pour plus de détails.
voici un exemple pratique:
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 rangée)
est tombé sur ce post et a constaté que vous pouvez interroger directement sur la table comme ceci:
SELECT *
FROM table_name, json_array_elements(json_column) AS data
WHERE data->>'id' = 123;
sans cette partie:
json_array_elements(t.json_col->'emails')
vous pouvez le faire aussi simple que:
SELECT * FROM table WHERE emails->>'id' = '123';
il semble que vous stockez l'id comme chaîne, si c'était un entier vous pouvez le faire comme ceci:
SELECT * from table WHERE cast(emails->>'id' as integer ) = 123 ;
ou vous pouvez obtenir toutes les lignes avec id > 10
SELECT * from table WHERE cast(emails->>'id' as integer ) > 10 ;