Comment puis-je interroger les champs à l'intérieur du nouveau datatype JSON PostgreSQL?

je cherche des docs et/ou des exemples pour les nouvelles fonctions JSON dans PostgreSQL 9.2.

spécifiquement, étant donné une série de dossiers JSON: 

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

Comment écrire le SQL pour trouver un enregistrement par son nom?

À la vanille SQL: 

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

L'officiel dev manuel est assez clairsemée:

Mise À Jour Je

j'ai assemblé un gist détaillant 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_string(data,'name') FROM things
WHERE json_string(data,'name') LIKE 'G%';

Mise à jour II

j'ai maintenant transféré mes fonctions JSON dans leur propre projet:

PostSQL - un ensemble de fonctions pour la transformation de PostgreSQL et oracle PL/v8 dans une totalement génial document JSON magasin

183
demandé sur Erwin Brandstetter 2012-05-12 05:45:13

3 réponses

Postgres 9.2

je cite Andrew Dunstan sur la liste pgsql-hackers :

à un moment donné, il y aura peut-être un traitement json (par opposition à un traitement json). pour les fonctions de production json), mais pas dans 9.2.

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

Postgres 9.3

Propose un arsenal de nouvelles fonctions et opérateurs pour ajouter "json-traitement".

la réponse à La question d'origine dans Postgresql 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é:

pour les tables plus grandes, Vous pouvez ajouter un indice d'expression pour augmenter la performance:

Postgres 9.4

, Ajoute jsonb (b pour "binaire", les valeurs sont stockées en tant que natif de Postgres types) et encore plus de fonctionnalités pour les deux . En plus des index d'expression mentionnés ci-dessus, jsonb supporte aussi index de gin, btree et hash , le GIN étant le plus puissant de ils.

Le manuel va jusqu'à suggérer:

en général, la plupart des applications devraient préférer stocker des données JSON comme jsonb , à moins qu'il n'y ait des besoins tout à fait spécialisés, tels que l'héritage hypothèses sur la commande des clés d'objet.

le Gras c'est moi qui souligne.

avantages de Performance d'améliorations générales de GIN index.

Postgres 9.5

Complet jsonb de fonctions et d'opérateurs. Ajouter plus de fonctions pour manipuler jsonb en place et pour l'affichage.

159
répondu Erwin Brandstetter 2017-05-23 11:47:24

avec Postgres 9.3+, il suffit d'utiliser 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 beaux exemples et un tutoriel.

73
répondu Meekohi 2017-05-01 22:57:30

Avec postgresql 9.3 utilisation -> pour l'accès aux objets. 4 Exemple

de la graine.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;

retourne 

                                 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;

retour 

 data
------
 1
(1 row)
16
répondu joseAndresGomezTovar 2014-03-27 12:27:51