Como faço para pesquisar os campos dentro do novo tipo de dados JSON PostgreSQL?

Estou à procura de alguns documentos e/ou exemplos para as novas funções JSON no PostgreSQL 9.2.

especificamente, dada uma série de registos JSON: 

[
  {name: "Toby", occupation: "Software Engineer"},
  {name: "Zaphod", occupation: "Galactic President"}
]
Como escreveria o SQL para encontrar um registo pelo nome?

em baunilha SQL: 

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

o manual oficial do dev é bastante esparsa:

Actualização I

reuni um gist detalhando o que é actualmente possível com o PostgreSQL 9.2 . Usando algumas funções personalizadas, é possível fazer coisas como: 

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

actualização II

Agora mudei as minhas funções JSON para as deles. projecto:

PostSQL - um conjunto de funções para transformar PostgreSQL e PL/V8 em uma loja de documentos JSON totalmente impressionante

Author: Erwin Brandstetter, 2012-05-12
Source

3 answers

Postgres 9, 2

Passo a citar Andrew Dunstan na lista de hackers pgsql.:
([[11]]) Em algum momento, possivelmente haverá algum JSON-processing (em oposição a para a produção de json), mas não em 9.2.

Isso não o impede de fornecer um exemplo de implementação no PLV8 que deve resolver o seu problema.

Postgres 9, 3

Oferece um arsenal de novas funções e operadores para adicionar "json-processing".

A resposta à pergunta original em Postgres 9.3: 

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

Exemplo avançado:

Para tabelas maiores, poderá querer adicionar um índice de expressão para aumentar o desempenho:

Postgres 9, 4

Acrescenta:jsonb ( b para "Binário", os valores são armazenados como tipos de Postgres nativos) e ainda mais funcionalidade para ambos os tipos. Além dos índices de expressão acima mencionados, {[1] } também suporta os índices de gin, btree e hash , sendo GIN o mais potente destes.

O manual chega ao ponto de sugerir:

Em geral, a maioria das aplicações deve preferir guardar os dados JSON como jsonb, a menos que haja necessidades bastante especializadas, como o legado suposições sobre a ordenação de chaves de objectos.

Ênfase ousada mina.

O desempenho beneficia de melhorias gerais nos índices GIN.

Postgres 9, 5

Completar jsonb funções e operadores. Adicionar mais funções para manipular jsonb no local e para visualização.

 159
Author: Erwin Brandstetter, 2017-05-23 11:47:24

Com Postgres 9, 3+, basta usar o operador {[[0]}. Por exemplo,

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

Ver http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/ para alguns bons exemplos e um tutorial.

 73
Author: Meekohi, 2017-05-01 22:57:30

Com postgres 9.3 utilização- > para acesso a objectos. 4 exemplo

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

Carris c 

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

Devolve 

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

Podes continuar a nidificar 

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

Volta 

 data
------
 1
(1 row)
 16
Author: joseAndresGomezTovar, 2014-03-27 12:27:51