Come posso interrogare usando i campi all’interno del nuovo tipo di dati JSON PostgreSQL?

Sto cercando alcuni documenti e / o esempi per le nuove funzioni JSON in PostgreSQL 9.2.

In particolare, data una serie di record JSON:

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

Come scriverei l’SQL per trovare un record per nome?

In SQL vaniglia:

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

Il manuale ufficiale di sviluppo è piuttosto scarso:

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

Aggiornamento I

Ho messo insieme una sintesi di ciò che è attualmente ansible con PostgreSQL 9.2 . Usando alcune funzioni personalizzate, è ansible fare cose come:

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

Aggiornamento II

Ora ho spostato le mie funzioni JSON nel loro progetto:

PostSQL : una serie di funzioni per trasformare PostgreSQL e PL / v8 in un eccezionale archivio di documenti JSON

Postgres 9.2

Cito Andrew Dunstan sulla lista di pgsql-hackers :

Ad un certo punto ci saranno probabilmente alcune funzioni di elaborazione json (al contrario di quelle di json), ma non in 9.2.

Non gli impedisce di fornire un’implementazione di esempio in PLV8 che dovrebbe risolvere il tuo problema.

Postgres 9.3

Offre un arsenale di nuove funzioni e operatori per aggiungere “json-processing”.

  • Il manuale sulla nuova funzionalità JSON.
  • Il Wiki di Postgres sulle nuove funzionalità di pg 9.3 .
  • @Spostato un link a un blog che mostra i nuovi operatori in un commento qui sotto .

La risposta alla domanda originale in Postgres 9.3:

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

Esempio avanzato:

  • Combinazioni di query con array di record nidificati in tipo di dati JSON

Per le tabelle più grandi potresti voler aggiungere un indice di espressione per aumentare le prestazioni:

  • Indice per trovare un elemento in un array JSON

Postgres 9.4

Aggiunge jsonb (b per “binary”, i valori sono memorizzati come tipi nativi di Postgres) e ancora più funzionalità per entrambi i tipi. Oltre agli indici di espressione menzionati sopra, jsonb supporta anche GIN, btree e indici hash , il GIN è il più potente di questi.

  • Il manuale sui tipi e le funzioni di dati json e jsonb .
  • The Postgres Wiki su JSONB in ​​pg 9.4

Il manuale arriva a suggerire:

In generale, la maggior parte delle applicazioni dovrebbe preferire archiviare i dati JSON come jsonb , a meno che non vi siano esigenze abbastanza specializzate, come le ipotesi legacy sull’ordinazione delle chiavi dell’object.

Grassetto enfasi mio.

Le prestazioni beneficiano di miglioramenti generali agli indici GIN.

Postgres 9.5

Funzioni e operatori jsonb completi. Aggiungi più funzioni per manipolare jsonb in posizione e per la visualizzazione.

  • Principali buone notizie nelle note di rilascio di Postgres 9.5.

Con Postgres 9.3+, basta usare l’operatore -> . Per esempio,

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

vedi http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/ per alcuni buoni esempi e un tutorial.

Con postgres 9.3 usa -> per l’accesso agli oggetti. 4 esempio

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 

rotaie c

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

ritorna

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

Puoi continuare ad annidare

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

ritorno

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