Comment puis-je interroger en utilisant les champs du nouveau type de données JSON PostgreSQL?

Je recherche des documents et / ou des exemples pour les nouvelles fonctions JSON dans PostgreSQL 9.2.

Plus précisément, étant donné une série d’enregistrements JSON:

[ {name: "Toby", occupation: "Software Engineer"}, {name: "Zaphod", occupation: "Galactic President"} ] 

Comment puis-je écrire le code SQL pour trouver un enregistrement par nom?

En vanille SQL:

 SELECT * from json_data WHERE "name" = "Toby" 

Le manuel de développement officiel est assez rare:

  • http://www.postgresql.org/docs/devel/static/datatype-json.html
  • http://www.postgresql.org/docs/devel/static/functions-json.html

Mise à jour je

J’ai mis en place un aperçu détaillé de ce qui est actuellement possible avec PostgreSQL 9.2 . En utilisant certaines fonctions personnalisées, il est possible de faire des choses comme:

 SELECT id, json_ssortingng(data,'name') FROM things WHERE json_ssortingng(data,'name') LIKE 'G%'; 

Mise à jour II

J’ai maintenant déplacé mes fonctions JSON dans leur propre projet:

PostSQL – un ensemble de fonctions pour transformer PostgreSQL et PL / v8 en un magasin de documents JSON totalement génial

Postgres 9.2

Je cite Andrew Dunstan sur la liste des pgsql-hackers :

A un certain stade, il y aura peut-être des fonctions de traitement json (par opposition à json-production), mais pas dans 9.2.

Cela ne l’empêche pas de fournir un exemple d’implémentation dans PLV8 qui devrait résoudre votre problème.

Postgres 9.3

Offre un arsenal de nouvelles fonctions et opérateurs pour append “json-processing”.

  • Le manuel sur la nouvelle fonctionnalité JSON.
  • Le wiki Postgres sur les nouvelles fonctionnalités de la pg 9.3 .
  • @Will a posté un lien vers un blog présentant les nouveaux opérateurs dans un commentaire ci-dessous .

La réponse à la question initiale dans Postgres 9.3:

 SELECT * FROM json_array_elements( '[{"name": "Toby", "occupation": "Software Engineer"}, {"name": "Zaphod", "occupation": "Galactic President"} ]' ) AS elem WHERE elem->>'name' = 'Toby'; 

Exemple avancé:

  • Combinaisons de requêtes avec un tableau nested d’enregistrements dans le type de données JSON

Pour des tables plus grandes, vous pouvez append un index d’expression pour augmenter les performances:

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

Postgres 9.4

Ajoute jsonb (b pour “binary”, les valeurs sont stockées en tant que types Postgres natifs) et encore plus de fonctionnalités pour les deux types. En plus des index d’expression mentionnés ci-dessus, jsonb prend également en charge les index GIN, btree et hash , GIN étant le plus puissant d’entre eux.

  • Le manuel sur les types de données et les fonctions json et jsonb .
  • Le Wiki Postgres sur JSONB dans la page 9.4

Le manuel va jusqu’à suggérer:

En général, la plupart des applications préfèrent stocker les données JSON en tant que jsonb , sauf s’il existe des besoins très spécialisés, tels que des hypothèses héritées sur la commande des clés d’object.

Emphase audacieuse la mienne

La performance bénéficie des améliorations générales apscopes aux index GIN.

Postgres 9.5

Fonctions et opérateurs jsonb complets. Ajouter plus de fonctions pour manipuler jsonb en place et pour l’affichage.

  • Bonne nouvelle dans les notes de publication de Postgres 9.5.

Avec Postgres 9.3+, utilisez simplement l’opérateur -> . Par exemple,

SELECT data->'images'->'thumbnail'->'url' AS thumb FROM instagram;

voir http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/ pour quelques exemples intéressants et un tutoriel.

Avec postgres 9.3, utilisez -> pour accéder aux objects. 4 exemple

seed.rb

 se = SmartElement.new se.data = { params: [ { type: 1, code: 1, value: 2012, description: 'year of producction' }, { type: 1, code: 2, value: 30, description: 'length' } ] } se.save 

rails c

 SELECT data->'params'->0 as data FROM smart_elements; 

résultats

  data ---------------------------------------------------------------------- {"type":1,"code":1,"value":2012,"description":"year of producction"} (1 row) 

Vous pouvez continuer à nicher

 SELECT data->'params'->0->'type' as data FROM smart_elements; 

revenir

  data ------ 1 (1 row)