Requête pour les éléments de tableau dans le type JSON

J’essaie de tester le type json dans PostgreSQL 9.3.
J’ai une colonne json appelée data dans un tableau appelé reports . Le JSON ressemble à 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 le DB pour tous les rapports qui correspondent à 'src' = 'foo.png' ? J’ai écrit avec succès une requête qui peut correspondre à "background" :

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

Mais puisque "objects" a un tableau de valeurs, je n’arrive pas à écrire quelque chose qui fonctionne. Est-il possible d’interroger la firebase database pour tous les rapports correspondant à 'src' = 'foo.png' ? J’ai parcouru ces sources mais je n’arrive toujours pas à l’obtenir:

  • http://www.postgresql.org/docs/9.3/static/functions-json.html
  • Comment puis-je interroger en utilisant les champs du nouveau type de données JSON PostgreSQL?
  • 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 SQL, donc je ne sais pas ce que je fais mal.

json dans Postgres 9.3+

Dévaluez le tableau JSON avec la fonction json_array_elements() dans une jointure latérale dans la clause FROM et testez 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 ( WITH requête) remplace simplement les reports table.
Ou, équivalent à un seul niveau d’imbrication:

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

->> opérateurs ->> , -> et #> sont expliqués dans le manuel.

Les deux requêtes utilisent un JOIN LATERAL implicite.

Violon SQL

Réponse étroitement liée:

  • Requête pour élément de tableau dans la colonne JSON

jsonb dans Postgres 9.4+

Utilisez l’équivalent jsonb_array_elements() .

Mieux encore, utilisez l’opérateur new “contains” @> (le mieux 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"}]'; 

Comme les objects clés contiennent un tableau JSON, nous devons faire correspondre la structure dans le terme de recherche et envelopper l’élément du tableau entre crochets. Supprimez les crochets du tableau lors de la recherche d’un enregistrement simple.

Explication détaillée et plus d’options:

  • Index pour trouver un élément dans un tableau JSON

Créer une table avec une colonne de type json

 # CREATE TABLE friends ( id serial primary key, data jsonb); 

Insérons maintenant 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 des 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 avez peut-être remarqué que les résultats sont entre guillemets (“) et entre parenthèses ([])

  name | work ------------+---------------------------- "Arya" | ["Improvements", "Office"] "Tim Cook" | ["Cook", "ceo", "Play"] (2 rows) 

Maintenant, pour récupérer uniquement les valeurs que vous utilisez ->>

 # 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';