Como faço para consultar usando campos dentro do novo tipo de dados PostgreSQL JSON?

Eu estou procurando alguns documentos e / ou exemplos para as novas funções JSON no PostgreSQL 9.2.

Especificamente, dada uma série de registros JSON:

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

Como eu escreveria o SQL para encontrar um registro pelo nome?

Em baunilha SQL:

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

O manual oficial de desenvolvimento é bastante escasso:

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

Atualizar eu

Eu reuni uma essência que detalha o que é atualmente 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%'; 

Atualizar II

Agora movi minhas funções JSON para seu próprio projeto:

PostSQL – um conjunto de funções para transformar o PostgreSQL e PL / v8 em um armazenamento de documentos JSON totalmente impressionante

Postgres 9.2

Cito Andrew Dunstan na lista pgsql-hackers :

Em algum momento, possivelmente haverá algumas funções de processamento json (em oposição à produção de json), mas não em 9.2.

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 “processamento json”.

  • O manual sobre a nova funcionalidade JSON.
  • O Wiki do Postgres sobre novos resources na página 9.3 .
  • @Will postou um link para um blog demonstrando os novos operadores nos comentários abaixo .

A resposta para a pergunta original no 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:

  • Combinações de consulta com matriz aninhada de registros no tipo de dados JSON

Para tabelas maiores, você pode querer adicionar um índice de expressão para aumentar o desempenho:

  • Índice para encontrar um elemento em um array JSON

Postgres 9,4

Adiciona jsonb (b para “binary”, valores são armazenados como tipos nativos de Postgres) e ainda mais funcionalidade para ambos os tipos. Além dos índices de expressão mencionados acima, o jsonb também suporta os índices GIN, btree e hash , sendo o GIN o mais potente deles.

  • O manual sobre tipos e funções de dados json e jsonb .
  • O Wiki do Postgres no JSONB na pág. 9.4

O manual chega até a sugerir:

Em geral, a maioria dos aplicativos deve preferir armazenar dados JSON como jsonb , a menos que haja necessidades bastante especializadas, como suposições herdadas sobre a ordenação de chaves de objects.

Ousada ênfase minha.

O desempenho se beneficia de melhorias gerais nos índices GIN.

Postgres 9,5

Funções e operadores completos do jsonb . Adicione mais funções para manipular o jsonb no lugar e para exibição.

  • Boas notícias importantes nas notas de lançamento do Postgres 9.5.

Com o Postgres 9.3+, basta usar o operador -> . Por exemplo,

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

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

Com o postgres 9.3 use -> para access a objects. 4 exemplo

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; 

retorna

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

Você pode continuar aninhando

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

Retorna

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