Json en Postgres

Tue, Jul 2, 2019

JSON o Javascript Object Notation es un formato de archivo que permite la representación de objetos complejos en el lenguaje de programación Javascript, lo que lo convierte en un estándar de facto para la compartición de datos entre aplicaciones web, APIs y aplicaciones móviles y de escritorio. Muchas veces, es importante obtener el resultado de una llamada a un API y almacenarlo directamente en la base de datos para su posterior análisis o utilización, o es importante que el resultado de una consulta quede formulado directamente en JSON desde el servidor de base de datos sobre todo si nuestra aplicación web cuenta con menos recursos para formatear las respuestas y servir la aplicación. En PostgreSQL existen 2 tipos de datos dedicados para almacenar y operar directamente cadenas de texto en JSON. Estos son los tipos json y jsonb.

El tipo de datos json

Para crear un campo tipo JSON dentro de una tabla lo podemos definir como:

CREATE TABLE api_response (
    id SERIAL PRIMARY KEY,
    api_resp json,
    time_resp timestampz
);

Para cargar un registro json:

INSERT INTO api_response(api_resp)
VALUES
 (
 '{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}'
 );

Para hacer consultas sobre campos json podemos usar un SELECT normal:

SELECT api_resp FROM api_response;

Para extraer información de un campo json dentro de una consulta se pueden utilizar los operadores -> y ->>.

SELECT
 info -> 'customer' AS customer
FROM
 orders;

El tipo de datos jsonb

jsonb es una representación binaria de las cadenas de json, la cual permite todas las operaciones mencionadas anteriormente mas algunas que aprovchan la representación binaria

La primera opeación es una pruba para saber si un documento, está contennido dentro de otro para esto usamos el operador @> ejemplo:

SELECT '["Fiction", "Thriller", "Horror"]'::jsonb @> '["Fiction", "Horror"]'::jsonb; 

Otra es checar existencia dentro del objeto Esto nos permite saber si existe la llave dentro del objeto:

SELECT COUNT(*) FROM books WHERE data ? 'authors'; 

Otra cualidad importante es que el tipo jsonb nos permite generar índices sobre los campos de la columna:

CREATE INDEX idx_published ON books (data->'published'); 

Conclusiones

Esto fue una breve introducción a las operaciones más comunes que se pueden llevar a cabo con los tipos de datos JSON en PostgreSQL, la documentación oficial de postgres es mucho más precisa y detallada, así que no está de más revisarla.