Requête pour les éléments de tableau à l'intérieur du type JSON
j'essaie de tester le type json dans PostgreSQL 9.3.
J'ai une colonne json appelée data dans une table appelée reports . Le JSON ressemble à quelque chose comme ceci:
{
"objects": [
{"src":"foo.png"},
{"src":"bar.png"}
],
"background":"background.png"
}
je voudrais interroger la table pour tous les rapports qui correspondent à la valeur 'src' dans le tableau 'objects'. Par exemple, est-il possible d'interroger la base de données pour tous les rapports qui correspondent à 'src' = 'foo.png' ? J'ai réussi à écrire une requête qui peut correspondent au "background" :
SELECT data AS data FROM reports where data->>'background' = 'background.png'
mais depuis "objects" a un tableau de valeurs, Je ne peux pas sembler écrire quelque chose qui fonctionne. Est-il possible d'interroger la base de données pour tous les rapports qui correspondent à 'src' = 'foo.png' ? J'ai parcouru ces sources, mais je n'y arrive toujours pas:
- http://www.postgresql.org/docs/9.3/static/functions-json.html
- comment interroger les champs dans le nouveau type de données PostgreSQL JSON?
- http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/
j'ai aussi essayé des choses comme ça mais en vain:
SELECT json_array_elements(data->'objects') AS data from reports
WHERE data->>'src' = 'foo.png';
Je ne suis pas un expert en SQL, donc je ne sais pas ce que je fais de mal.
2 réponses
json dans Postgresql 9.3+
annuler le tableau de JSON avec la fonction json_array_elements() dans une jointure latérale dans la FROM clause et test pour ses éléments:
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'; Le CTE ( "151960920 de la requête") remplace une table reports .
Ou équivalent pour juste un unique niveau d'imbrication:
SELECT *
FROM reports r, json_array_elements(r.data->'objects') obj
WHERE obj->>'src' = 'foo.png'; ->> , -> et #> sont expliqués dans le manuel.
les deux requêtes utilisent un JOIN LATERAL .
réponse très proche:
jsonb dans Postgresql 9.4+
utiliser l'équivalent jsonb_array_elements() .
meilleur pourtant, utilisez le nouveau "contient" opérateur @> (meilleur en combinaison avec un index GIN correspondant sur l'expression 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"}]';
puisque la clé objects contient un JSON array , nous devons faire correspondre la structure dans le terme de recherche et envelopper l'élément de tableau dans des crochets, aussi. Supprimez les crochets de tableau lors de la recherche d'un enregistrement simple.
explication détaillée et plus d'options:
créer une table avec la colonne comme type json
# CREATE TABLE friends ( id serial primary key, data jsonb);
maintenant insérons les données json
# INSERT INTO friends(data) VALUES ('{"name": "Arya", "work": ["Improvements", "Office"], "available": true}');
# INSERT INTO friends(data) VALUES ('{"name": "Tim Cook", "work": ["Cook", "ceo", "Play"], "uses": ["baseball", "laptop"], "available": false}');
maintenant faisons quelques requêtes pour récupérer des données
# select data->'name' from friends;
# select data->'name' as name, data->'work' as work from friends;
vous auriez pu remarquer que les résultats sont livrés avec virgule inversée ( " ) et crochets ([ ])
name | work
------------+----------------------------
"Arya" | ["Improvements", "Office"]
"Tim Cook" | ["Cook", "ceo", "Play"]
(2 rows)
maintenant pour récupérer seulement les valeurs il suffit d'utiliser ->>
# select data->>'name' as name, data->'work'->>0 as work from friends;
#select data->>'name' as name, data->'work'->>0 as work from friends where data->>'name'='Arya';