La sintaxis de entrada/salida para los tipos de datos JSON es la especificada en la RFC 7159.

Las siguientes son todas expresiones válidas de tipo json (o jsonb):

-- Valor escalar/primitivo simple
-- Los valores primitivos pueden ser números, cadenas entre comillas, true, false o null
SELECT '5'::json;

-- Array de cero o más elementos (no es necesario que los elementos sean del mismo tipo)
SELECT '[1, 2, "foo", null]'::json;

-- Objeto que contiene pares de claves y valores
-- Ten en cuenta que las claves de los objetos siempre deben ser cadenas entre comillas
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- Los arrays y objetos se pueden anidar arbitrariamente
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

Como se indicó anteriormente, cuando se ingresa un valor JSON y luego se imprime sin ningún procesamiento adicional, json devuelve el mismo texto que se ingresó, mientras que jsonb no conserva los detalles semánticamente insignificantes como los espacios en blanco. Por ejemplo, observa las diferencias aquí:

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
                      json
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
                      jsonb
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

Un detalle semánticamente insignificante que vale la pena señalar es que en jsonb, los números se imprimirán de acuerdo con el comportamiento del tipo numeric subyacente. En la práctica, esto significa que los números introducidos con la notación E se imprimirán sin ella, por ejemplo:

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

Sin embargo, jsonb conservará los ceros fraccionarios finales, como se ve en este ejemplo, aunque sean semánticamente insignificantes para propósitos como las comprobaciones de igualdad.

Para obtener la lista de funciones y operadores integrados disponibles para construir y procesar valores JSON, consulta la Section 9.16.

Representar datos como JSON puede ser considerablemente más flexible que el modelo de datos relacional tradicional, lo que resulta atractivo en entornos donde los requisitos son cambiantes. Es muy posible que ambos enfoques coexistan y se complementen entre sí dentro de la misma aplicación. Sin embargo, incluso para aplicaciones donde se desea la máxima flexibilidad, se recomienda que los documentos JSON tengan una estructura un poco fija. La estructura normalmente no se impone (aunque es posible imponer algunas reglas de negocio de forma declarativa), pero tener una estructura predecible facilita la escritura de consultas que resuman de manera útil un conjunto de “documentos” (datos) en una tabla.

Los datos JSON están sujetos a las mismas consideraciones de control de concurrencia que cualquier otro tipo de datos cuando se almacenan en una tabla. Aunque almacenar documentos grandes es factible, ten en cuenta que cualquier actualización adquiere un bloqueo a nivel de fila en toda la fila. Considera limitar los documentos JSON a un tamaño manejable para disminuir la contención de bloqueo entre las transacciones que realizan actualizaciones. Idealmente, cada documento JSON debería representar un dato atómico que las reglas de negocio dicten que no se puede subdividir razonablemente en datos más pequeños que pudieran modificarse de forma independiente.

Probar la contención es una capacidad importante de jsonb. No existe un conjunto de facilidades paralelo para el tipo json. La contención prueba si un documento jsonb contiene a otro. Estos ejemplos devuelven true excepto donde se indique:

-- Los valores escalares/primitivos simples contienen solo el valor idéntico:
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

-- El array del lado derecho está contenido en el del lado izquierdo:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

-- El orden de los elementos del array no es significativo, por lo que esto también es cierto:
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

-- Los elementos duplicados del array tampoco importan:
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

-- El objeto con un solo par en el lado derecho está contenido
-- dentro del objeto en el lado izquierdo:
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;

-- El array del lado derecho no se considera contenido dentro del
-- array de la izquierda, a pesar de que un array similar está anidado dentro de él:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb; -- devuelve false

-- Pero con un nivel de anidamiento, sí está contenido:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

-- De manera similar, aquí no se reporta contención:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb; -- devuelve false

-- Se contiene una clave de nivel superior y un objeto vacío:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

El principio general es que el objeto contenido debe coincidir con el objeto contenedor en cuanto a estructura y contenido de datos, posiblemente después de descartar algunos elementos de array no coincidentes o pares clave/valor de objeto del objeto contenedor. Pero recuerda que el orden de los elementos del array no es significativo al hacer una coincidencia de contención, y los elementos duplicados del array se consideran efectivamente solo una vez.

Como excepción especial al principio general de que las estructuras deben coincidir, un array puede contener un valor primitivo:

-- Este array contiene el valor de cadena primitivo:
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

-- Esta excepción no es recíproca -- aquí se reporta la no contención:
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb; -- devuelve false

jsonb también tiene un operador de existencia, que es una variación del tema de la contención: prueba si una cadena (dada como un valor de tipo text) aparece como una clave de objeto o elemento de array en el nivel superior del valor jsonb. Estos ejemplos devuelven true excepto donde se indique:

-- La cadena existe como elemento de array:
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

-- La cadena existe como clave de objeto:
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

-- Los valores de los objetos no se consideran:
SELECT '{"foo": "bar"}'::jsonb ? 'bar'; -- devuelve false

-- Al igual que con la contención, la existencia debe coincidir en el nivel superior:
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- devuelve false

-- Se considera que existe una cadena si coincide con una cadena JSON primitiva:
SELECT '"foo"'::jsonb ? 'foo';

Los objetos JSON son más adecuados que los arrays para probar la contención o existencia cuando hay muchas claves o elementos involucrados, porque a diferencia de los arrays, están optimizados internamente para búsquedas y no necesitan buscarse linealmente.

SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';

SELECT doc->'site_name' FROM websites
  WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';

Los diversos operadores de contención y existencia, junto con todos los demás operadores y funciones de JSON, están documentados en la Section 9.16.

Se pueden utilizar índices GIN para buscar eficientemente claves o pares clave/valor que ocurran dentro de una gran cantidad de documentos (datos) de tipo jsonb. Se proporcionan dos “clases de operadores” GIN, que ofrecen diferentes compensaciones de rendimiento y flexibilidad.

La clase de operador GIN predeterminada para jsonb admite consultas con los operadores de existencia de clave ?, ?| y ?&, el operador de contención @>, y los operadores de coincidencia de jsonpath @? y @@. (Para obtener detalles sobre la semántica que implementan estos operadores, consulta la Table 9.48). Un ejemplo de creación de un índice con esta clase de operador es:

CREATE INDEX idxgin ON api USING GIN (jdoc);

La clase de operador GIN no predeterminada jsonb_path_ops no admite los operadores de existencia de clave, pero sí admite @>, @? y @@. Un ejemplo de creación de un índice con esta clase de operador es:

CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);

Considera el ejemplo de una tabla que almacena documentos JSON recuperados de un servicio web de terceros, con una definición de esquema documentada. Un documento típico es:

{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

Almacenamos estos documentos en una tabla llamada api, en una columna de tipo jsonb llamada jdoc. Si se crea un índice GIN en esta columna, las consultas como la siguiente pueden hacer uso del índice:

-- Buscar documentos en los que la clave "company" tenga el valor "Magnafone"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

Sin embargo, el índice no se podría utilizar para consultas como la siguiente, porque aunque el operador ? es indexable, no se aplica directamente a la columna indexada jdoc:

-- Buscar documentos en los que la clave "tags" contenga la clave o elemento de array "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

Aún así, con el uso adecuado de índices de expresión, la consulta anterior puede usar un índice. Si la búsqueda de elementos particulares dentro de la clave "tags" es común, puede valer la pena definir un índice como este:

CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));

Ahora, la cláusula WHERE jdoc -> 'tags' ? 'qui' se reconocerá como una aplicación del operador indexable ? a la expresión indexada jdoc -> 'tags'. (Se puede encontrar más información sobre índices de expresión en la Section 11.7).

Otro enfoque para realizar consultas es explotar la contención, por ejemplo:

-- Buscar documentos en los que la clave "tags" contenga el elemento de array "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

Un índice GIN simple en la columna jdoc puede admitir esta consulta. Pero ten en cuenta que dicho índice almacenará copias de cada clave y valor en la columna jdoc, mientras que el índice de expresión del ejemplo anterior almacena solo los datos encontrados bajo la clave tags. Si bien el enfoque de índice simple es mucho más flexible (ya que admite consultas sobre cualquier clave), los índices de expresión específicos probablemente sean más pequeños y rápidos de buscar que un índice simple.

Los índices GIN también admiten los operadores @? y @@, que realizan la coincidencia de jsonpath. Los ejemplos son:

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';

Para estos operadores, un índice GIN extrae cláusulas de la forma accessors_chain == constant fuera del patrón jsonpath, y realiza la búsqueda en el índice basándose en las claves y valores mencionados en estas cláusulas. La cadena de accesores puede incluir accesores .key, [*] y [index]. La clase de operador jsonb_ops también admite accesores .* y .**, but the jsonb_path_ops operator class does not.

Aunque la clase de operador jsonb_path_ops admite solo consultas con los operadores @>, @? y @@, tiene notables ventajas de rendimiento sobre la clase de operador predeterminada jsonb_ops. Un índice jsonb_path_ops suele ser mucho más pequeño que un índice jsonb_ops sobre los mismos datos, y la especificidad de las búsquedas es mejor, particularmente cuando las consultas contienen claves que aparecen con frecuencia en los datos. Por lo tanto, las operaciones de búsqueda suelen tener un mejor rendimiento que con la clase de operador predeterminada.

La diferencia técnica entre un índice GIN jsonb_ops y uno jsonb_path_ops es que el primero crea elementos de índice independientes para cada clave y valor en los datos, mientras que el segundo crea elementos de índice solo para cada valor en los datos. ^[7] Básicamente, cada elemento de índice jsonb_path_ops es un hash del valor y las claves que conducen a él; por ejemplo, para indexar {"foo": {"bar": "baz"}}, se crearía un único elemento de índice que incorporaría los tres valores foo, bar y baz en el valor hash. Por lo tanto, una consulta de contención que busque esta estructura daría como resultado una búsqueda de índice extremadamente específica; pero no hay forma alguna de averiguar si foo aparece como una clave. Por otro lado, un índice jsonb_ops crearía tres elementos de índice que representarían foo, bar y baz por separado; luego, para realizar la consulta de contención, buscaría las filas que contuvieran estos tres elementos. Si bien los índices GIN pueden realizar una búsqueda AND de este tipo de manera bastante eficiente, seguirá siendo menos específica y más lenta que la búsqueda equivalente de jsonb_path_ops, especialmente si hay una cantidad muy grande de filas que contienen uno solo de los tres elementos de índice.

Una desventaja del enfoque jsonb_path_ops es que no produce entradas de índice para estructuras JSON que no contengan ningún valor, como {"a": {}}. Si se solicita una búsqueda de documentos que contengan dicha estructura, se requerirá un escaneo completo del índice, lo cual es bastante lento. Por lo tanto, jsonb_path_ops no es adecuado para aplicaciones que realizan tales búsquedas con frecuencia.

jsonb también admite índices btree y hash. Estos suelen ser útiles solo si es importante comprobar la igualdad de documentos JSON completos. El ordenamiento btree para los datos jsonb rara vez es de gran interés, pero para completitud es el siguiente:

Objeto > Array > Booleano > Número > Cadena > null

Objeto con n pares > objeto con n - 1 pares

Array con n elementos > array con n - 1 elementos

con la excepción de que (por razones históricas) un array vacío de nivel superior se ordena antes que null. Los objetos con el mismo número de pares se comparan en el orden:

clave-1, valor-1, clave-2 ...

Ten en cuenta que las claves de los objetos se comparan en su orden de almacenamiento; en particular, dado que las claves más cortas se almacenan antes que las claves más largas, esto puede conducir a resultados que podrían ser poco intuitivos, como:

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

Del mismo modo, los arrays con el mismo número de elementos se comparan en el orden:

elemento-1, elemento-2 ...

Los valores JSON primitivos se comparan utilizando las mismas reglas de comparación que para el tipo de datos subyacente de PostgreSQL. Las cadenas se comparan utilizando la colación predeterminada de la base de datos.

El tipo de datos jsonb admite expresiones de subindizado de estilo array para extraer y modificar elementos. Los valores anidados se pueden indicar encadenando expresiones de subindizado, siguiendo las mismas reglas que el argumento path en la función jsonb_set. Si un valor jsonb es un array, los subíndices numéricos comienzan en cero y los enteros negativos cuentan hacia atrás desde el último elemento del array. No se admiten expresiones de rebanado. El resultado de una expresión de subindizado siempre es del tipo de datos jsonb.

Las sentencias UPDATE pueden usar subindizado en la cláusula SET para modificar valores de tipo jsonb. Las rutas de subíndices deben ser transitables para todos los valores afectados en la medida en que existan. Por ejemplo, la ruta val['a']['b']['c'] se puede transitar por completo hasta c si cada uno de los elementos val, val['a'] y val['a']['b'] es un objeto. Si algún elemento val['a'] o val['a']['b'] no está definido, se creará como un objeto vacío y se rellenará según sea necesario. Sin embargo, si el propio val o uno de los valores intermedios se define como un no objeto, como una cadena, un número o un jsonb null, el recorrido no puede continuar, por lo que se produce un error y se aborta la transacción.

Un ejemplo de sintaxis de subindizado:

-- Extraer valor de objeto por clave
SELECT ('{"a": 1}'::jsonb)['a'];

-- Extraer valor de objeto anidado por ruta de clave
SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];

-- Extraer elemento de array por índice
SELECT ('[1, "2", null]'::jsonb)[1];

-- Actualizar valor de objeto por clave. Ten en cuenta las comillas alrededor de '1': el valor
-- asignado también debe ser del tipo jsonb
UPDATE table_name SET jsonb_field['key'] = '1';

-- Esto producirá un error si el elemento jsonb_field['a']['b'] de algún registro es algo
-- distinto de un objeto. Por ejemplo, el valor {"a": 1} tiene un valor numérico
-- en la clave 'a'.
UPDATE table_name SET jsonb_field['a']['b']['c'] = '1';

-- Filtrar registros mediante una cláusula WHERE con subindizado. Como el resultado del
-- subindizado es jsonb, el valor con el que lo comparamos también debe ser jsonb.
-- Las comillas dobles hacen que "value" también sea una cadena jsonb válida.
SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';

La asignación a jsonb mediante subindizado maneja algunos casos límite de manera diferente a jsonb_set. Cuando un valor jsonb de origen es NULL, la asignación mediante subindizado continuará como si fuera un valor JSON vacío del tipo (objeto o array) implícito en la clave del subíndice:

-- Donde jsonb_field era NULL, ahora es {"a": 1}
UPDATE table_name SET jsonb_field['a'] = '1';

-- Donde jsonb_field era NULL, ahora es [1]
UPDATE table_name SET jsonb_field[0] = '1';

Si se especifica un índice para un array que contiene muy pocos elementos, se agregarán elementos NULL hasta que se pueda alcanzar el índice y establecer el valor.

-- Donde jsonb_field era [], ahora es [null, null, 2];
-- donde jsonb_field era [0], ahora es [0, null, 2]
UPDATE table_name SET jsonb_field[2] = '2';

Un valor jsonb aceptará asignaciones a rutas de subíndice inexistentes siempre que el último elemento existente a transitar sea un objeto o un array, según lo implícito por el subíndice correspondiente (el elemento indicado por el último subíndice en la ruta no se transita y puede ser cualquier cosa). Se crearán estructuras de arrays y objetos anidados y, en el primer caso, se rellenarán con null, según lo especificado por la ruta del subíndice hasta que se pueda colocar el valor asignado.

-- Donde jsonb_field era {}, ahora es {"a": [{"b": 1}]}
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';

-- Donde jsonb_field era [], ahora es [null, {"a": 1}]
UPDATE table_name SET jsonb_field[1]['a'] = '1';

Hay extensiones adicionales disponibles que implementan transformaciones para el tipo jsonb para diferentes lenguajes procedimentales.

Las extensiones para PL/Perl se llaman jsonb_plperl y jsonb_plperlu. Si las usas, los valores jsonb se asignan a arrays, hashes y escalares de Perl, según corresponda.

La extensión para PL/Python se llama jsonb_plpython3u. Si la usas, los valores jsonb se asignan a diccionarios, listas y escalares de Python, según corresponda.

De estas extensiones, jsonb_plperl se considera “de confianza” (trusted), es decir, puede ser instalada por usuarios que no sean superusuarios y que tengan el privilegio CREATE en la base de datos actual. El resto requiere privilegios de superusuario para instalarse.

El tipo jsonpath implementa el soporte para el lenguaje de rutas SQL/JSON en PostgreSQL para consultar de manera eficiente datos JSON. Proporciona una representación binaria de la expresión de ruta SQL/JSON analizada que especifica los elementos que el motor de rutas debe recuperar de los datos JSON para su procesamiento posterior con las funciones de consulta SQL/JSON.

La semántica de los predicados y operadores de rutas SQL/JSON generalmente sigue a SQL. Al mismo tiempo, para proporcionar una forma natural de trabajar con datos JSON, la sintaxis de rutas SQL/JSON utiliza algunas convenciones de JavaScript:

Los literales numéricos en las expresiones de ruta SQL/JSON siguen las reglas de JavaScript, que difieren tanto de SQL como de JSON en algunos detalles menores. Por ejemplo, la ruta SQL/JSON permite .1 y 1., que no son válidos en JSON. Se admiten literales enteros no decimales y separadores de guion bajo, por ejemplo, 1_000_000, 0x1EEE_FFFF, 0o273, 0b100101. En las rutas SQL/JSON (y en JavaScript, pero no en SQL propiamente dicho), no debe haber un separador de guion bajo directamente después del prefijo de base.

Una expresión de ruta SQL/JSON normalmente se escribe en una consulta SQL como un literal de cadena de caracteres SQL, por lo que debe incluirse entre comillas simples, y cualquier comilla simple que se desee dentro del valor debe duplicarse (consulta la Section 4.1.2.1). Algunas formas de expresiones de ruta requieren literales de cadena dentro de ellas. Estos literales de cadena integrados siguen las convenciones de JavaScript/ECMAScript: deben estar rodeados por comillas dobles y se pueden usar escapes de barra invertida dentro de ellos para representar caracteres que de otro modo serían difíciles de escribir. En particular, la forma de escribir una comilla doble dentro de un literal de cadena integrado es \", y para escribir una barra invertida en sí, se debe escribir \\. Otras secuencias especiales de barra invertida incluyen las reconocidas en las cadenas de JavaScript: \b, \f, \n, \r, \t, \v para varios caracteres de control ASCII, \xNN para un código de carácter escrito con solo dos dígitos hexadecimales, \uNNNN para un carácter Unicode identificado por su punto de código de 4 dígitos hexadecimales y \u{N...} para un punto de código de carácter Unicode escrito con 1 a 6 dígitos hexadecimales.

Una expresión de ruta consta de una secuencia de elementos de ruta, que pueden ser cualquiera de los siguientes:

Para obtener detalles sobre el uso de expresiones jsonpath con funciones de consulta SQL/JSON, consulta la Section 9.16.2.