Cómo gestionar JSON en PostgreSQL

Con la intención de obtener parte del poder de las bases de datos nosql, en las últimas versiones de PostgreSQL se le ha integrado a su motor la capacidad para soportar JSON. Esto puede ser desconocido para muchos, puesto que, como hemos dicho anteriormente, es una gran novedad. Como ya sabéis, las bases de datos nosql son sistemas de gestión de datos que difieren de la estructura clásica de las bases de datos. No son bases de datos basadas en relaciones, tampoco se almacena la información en tablas, más bien comprenden relaciones como clave:valor, mucho más simples y más veloces a la hora de implementar un proyecto. A continuación te vamos a explicar con sencillos ejemplos las diferentes maneras de gestionar un JSON en PostgreSQL.

Establecemos una consulta SQL básica

SELECT * FROM persona

Consultamos un JSON a partir de un registro específico

SELECT to_json(c.*) FROM persona c WHERE id = 1

Consultamos un JSON array a partir de una lista de registros

SELECT array_to_json(array_agg(p.*)) FROM persona p

Creamos un result set a dos columas con cada uno de los atributos del JSON

-- JSON object desde result set
SELECT * FROM json_each((SELECT to_json(c.*) FROM persona c WHERE id = 1))
 
-- JSON object
SELECT * FROM json_each('{ "id" : 1, "Nombre" : "Jorge" }')

Creamos un result set donde cada registro es uno de los JSON internos de nuestro array

-- JSON Array a partir de un result set
SELECT * FROM json_array_elements((SELECT array_to_json(array_agg(c.*)) FROM persona c))
 
-- JSON Array
SELECT * FROM json_array_elements('[{ "id" : 1, "Nombre" : "Jorge" }, { "id" : 2, "Nombre" : "Pablo" }]')

Consultamos sobre un array de JSON, un registro específico

-- JSON Array a partir de un result set
SELECT value FROM (SELECT value FROM json_array_elements((SELECT array_to_json(array_agg(c.*))
FROM persona c))) d WHERE (value::JSON->'id')::VARCHAR = '1'
 
-- JSON Array
SELECT value FROM (SELECT value FROM json_array_elements('[{ "id" : 1, "Nombre" : "Jorge" }, '
    || '{ "id" : 2, "Nombre" : "Pablo" }]')) d WHERE (value::JSON->'id')::VARCHAR = '1'

Consultamos una lista de registros en un array JSON

-- Result set
SELECT array_to_json(array_agg(e.*)) FROM (SELECT value FROM (SELECT value
FROM json_array_elements((SELECT array_to_json(array_agg(c.*)) FROM persona c))) d
WHERE (value::JSON->'id')::VARCHAR IN ('1', '2')) e
 
-- JSON array
SELECT array_to_json(array_agg(e.*)) FROM (SELECT value FROM (SELECT value
FROM json_array_elements('[{ "id" : 1, "Nombre" : "Jorge" }, { "id" : 2, "Nombre" : "Pablo" }, '
    || '{ "id" : 3, "Nombre" : "Angela" }]')) d WHERE (value::JSON->'id')::VARCHAR IN ('1', '3')) e

Insertamos un nuevo atributo a un JSON

SELECT ('{ ' || string_agg('"' || key || '" : ' || value, ', ') || ' }')::JSON FROM (
SELECT * FROM json_each('{ "id" : 1, "Nombre" : "Jorge" }')
UNION ALL SELECT 'NuevoKey', '"NuevoValor"') t

Eliminamos un atributo a un JSON

SELECT ('{ ' || string_agg('"' || key || '" : ' || value, ', ') || ' }')::JSON FROM (
SELECT * FROM json_each('{ "id" : 1, "Nombre" : "Jorge" }') WHERE key <> 'id') t

Modificamos un atributo a un JSON

SELECT ('{ ' || string_agg('"' || key || '" : ' || value, ', ') || ' }')::JSON FROM (
SELECT * FROM json_each('{ "id" : 1, "Nombre" : "Jorge" }') WHERE key <> 'id'
UNION ALL SELECT 'id', '4') t

Insertamos un JSON object a un JSON array

SELECT ('[ ' || string_agg(value::TEXT, ', ') || ' ]')::JSON
FROM (SELECT * FROM json_array_elements('[{ "id" : 1, "Nombre" : "Jorge" }, { "id" : 2, "Nombre" : "Pablo" }]')
UNION ALL SELECT '{ "id" : 3, "Nombre" : "Angela" }') t;

Y este ha sido el artículo en el que trataba explicaros cómo gestionar JSON en PostgreSQL, esperamos que te haya gustado y sepas aplicarlo en tus futuros proyectos. Ya sabes que si nos quieres proponer un tema que quieres ver reflejado como un tutorial o como una práctica, solo tienes que hacer uso del área de comentarios de un poco más abajo. Por el contrario, si quieres enviarnos tus propios tutoriales, puedes hacerlo a través de la intranet de usuarios que está habilitada para ello, a través del menú Enviar Tutorial. Ya sabes, ayúdanos a crecer con tus conocimientos. ¡Un saludo y feliz código!

COMPARTE ESTE ARTÍCULO

COMPARTIR EN FACEBOOK
COMPARTIR EN TWITTER
COMPARTIR EN LINKEDIN
COMPARTIR EN WHATSAPP