keys - postgresql jsonb example

¿Cómo consulto usando campos dentro del nuevo tipo de datos PostgreSQL JSON?

Question

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 vainilla SQL:

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

El manual oficial de desarrollo es bastante escaso:

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

Actualización I

He reunido una esencia que detalla lo que es actualmente posible 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 su propio proyecto:

PostSQL : un conjunto de funciones para transformar PostgreSQL y PL / v8 en una increíble librería de documentos JSON

Answer 1

Postgres 9.2

Cito a Andrew Dunstan en la lista de pgsql-hackers :

En algún momento posiblemente haya algunas funciones de procesamiento json (en oposición a json-producing), pero no en 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-processing".

• El manual sobre la nueva funcionalidad JSON.
• The Postgres Wiki sobre nuevas características en la página 9.3 .
• @ Publicará un enlace a un blog que demuestre los nuevos operadores en los comentarios a continuación .

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:

• Consultas de consulta con una matriz anidada de registros en el tipo de datos JSON

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

• Índice para encontrar un elemento en una matriz JSON

Postgres 9.4

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

• El manual sobre tipos y functions datos json y jsonb .
• El Wiki de Postgres en JSONB en la página 9.4

El manual llega a 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 suposiciones heredadas sobre el pedido de claves de objeto.

Negrita énfasis mío.

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

Postgres 9.5

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

• Buenas noticias importantes en las notas de la versión de Postgres 9.5.

Answer 2

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

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

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

Answer 3

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

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

rieles c

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

devoluciones

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

Puedes continuar anidando

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

regreso

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