¿Cómo puedo realizar consultas usando campos dentro del nuevo tipo de datos JSON de PostgreSQL?

Estoy buscando algunos documentos y/o ejemplos para las nuevas funciones JSON en PostgreSQL 9.2.

Específicamente, dada una serie de registros JSON: 

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

¿Cómo escribiría el SQL para encontrar un registro por nombre?

En vanilla SQL: 

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

El manual oficial dev es bastante sparse:

Actualización I

He reunido un gist detallando lo que es posible actualmente con PostgreSQL 9.2. Usando algunas funciones personalizadas, es posible hacer cosas como: 

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

Actualización II

Ahora he movido mis funciones JSON a sus propias proyecto:

PostSQL - un conjunto de funciones para transformar PostgreSQL y PL/v8 en un almacén de documentos JSON totalmente impresionante

Author: Erwin Brandstetter, 2012-05-12
Source

3 answers

Postgres 9.2

Cito Andrew Dunstan en la lista de hackers de pgsql :

En algún momento posiblemente habrá algún procesamiento json (a diferencia de json), pero no en la versión 9.2.

No le impide proporcionar una implementación de ejemplo en PLV8 que debería resolver su problema.

Postgres 9.3

Ofrece un arsenal de nuevas funciones y operadores para agregar "json-procesamiento".

La respuesta a la pregunta original en Postgres 9.3: 

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

Ejemplo avanzado:

Para tablas más grandes es posible que desee agregar un índice de expresión para aumentar el rendimiento:

Postgres 9.4

Añade jsonb (b para "binario", los valores se almacenan como tipos Postgres nativos) y aún más funcionalidad para ambos tipos. Además de los índices de expresión mencionados anteriormente, jsonb también admite índices de GINEBRA, btree y hash , siendo el más potente de estos.

El manual va tan lejos como sugerir:

En general, la mayoría de las aplicaciones deberían preferir almacenar datos JSON como jsonb, a menos que haya necesidades bastante especializadas, como el legado suposiciones sobre el orden de las claves de objeto.

Énfasis en negrita mina.

El rendimiento se beneficia de las mejoras generales de los índices de GINEBRA.

Postgres 9.5

Completa jsonb funciones y operadores. Agregue más funciones para manipular jsonb en su lugar y para mostrar.

 159
Author: Erwin Brandstetter,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 11:47:24

Con Postgres 9.3+, simplemente use el operador ->. Por ejemplo,

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

Véase http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json / para algunos buenos ejemplos y un tutorial.

 73
Author: Meekohi,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-01 22:57:30

Con postgres 9.3 use -> para el acceso a objetos. 4 ejemplo

Semilla.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

Carriles c 

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

Devuelve 

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

Puede continuar anidando 

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

Return 

 data
------
 1
(1 row)
 16
Author: joseAndresGomezTovar,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-03-27 12:27:51