Manipulación de Datos JSON en PostgreSQL

Clase 21 de 34 • Curso de PostgreSQL Aplicado a Ciencia de Datos

Resumen

¿Cómo trabajar con JSON en PostgreSQL?

En el manejo de bases de datos, uno de los retos principales ha sido lidiar con una amplia variedad de estructuras de datos, siendo JSON uno de los formatos más flexibles y complejos. PostgreSQL nos ofrece una poderosa funcionalidad para trabajar con objetos JSON, haciendo el manejo de estos datos tan natural como con estructuras primitivas.

¿Qué son los objetos JSON y cómo los maneja PostgreSQL?

JSON, o JavaScript Object Notation, es una estructura de datos ligera y de fácil comprensión que almacena pares de clave-valor. Gracias a su estructura jerárquica, JSON permite anidar datos fácilmente. PostgreSQL permite almacenar estos objetos de dos maneras:

JSON: Almacena los datos como texto, lo cual es efectivo pero puede ralentizar ciertas operaciones debido a la deserialización necesaria.
JSONB: Almacena los datos en formato binario, optimizando la velocidad de acceso y permitiendo realizar operaciones más rápidamente.

¿Cómo crear y manipular tablas con campos JSON en PostgreSQL?

Vamos a ver cómo podemos crear una tabla que contenga un campo de tipo JSON y realizar algunas operaciones básicas. Para comenzar, crearemos una tabla denominada ordenes con dos campos: un id serial y el campo info que almacenará nuestros objetos JSON.

CREATE TABLE ordenes (
    id SERIAL PRIMARY KEY,
    info JSON NOT NULL
);

Este campo info permitirá almacenar cualquier estructura JSON, dando flexibilidad al tipo de datos que podemos manejar.

¿Cómo insertar datos JSON en una tabla?

A continuación, podemos insertar datos JSON en nuestra tabla utilizando la sentencia INSERT. Es esencial envolver el objeto JSON en comillas para que PostgreSQL lo trate como una cadena:

INSERT INTO ordenes (info) VALUES 
('{"cliente": "David Sánchez", "items": [{"producto": "biberón", "cantidad": 24}]}'),
('{"cliente": "Edman Cárdenas", "items": [{"producto": "carro de juguete", "cantidad": 1}]}'),
('{"cliente": "Israel Vásquez", "items": [{"producto": "tren de juguete", "cantidad": 2}]}');

Esto ilustra cómo varios registros pueden tener estructuras similares pero con información diversa, como distintos clientes y productos.

¿Cómo consultar y manipular datos JSON?

Consultar datos JSON es tan sencillo como utilizar la sentencia SELECT. Puedes recuperar sub-campos específicos de un objeto JSON utilizando el operador -> para claves JSON y con ->> si deseas el valor como texto.

Por ejemplo, para obtener el nombre del cliente:

SELECT info->>'cliente' AS cliente_nombre FROM ordenes;

Si deseas filtrar por un producto específico en el JSON, puedes usar el operador de filtro JSON igualmente:

SELECT info->>'cliente' AS cliente_nombre 
FROM ordenes 
WHERE info->'items'->0->>'producto' = 'biberón';

¿Por qué JSON es importante en PostgreSQL?

El soporte para JSON en PostgreSQL permite una flexibilidad extraordinaria para aplicaciones que requieren almacenar datos semiestructurados. Esto es especialmente útil en desarrollos ágiles donde los requisitos de datos pueden cambiar rápidamente. La capacidad de almacenar, consultar y filtrar eficientemente objetos JSON abre un amplio espectro de posibilidades para el desarrollo de aplicaciones modernas.

Con esta funcionalidad, PostgreSQL se coloca a la vanguardia en el manejo de datos NoSQL dentro de un sistema relacional, ofreciendo lo mejor de ambos mundos. Los invito a continuar explorando y aprovechando estas ventajas que PostgreSQL brinda para gestionar datos en formato JSON ¡Sigue aprendiendo y potenciando tus habilidades en la gestión de bases de datos!

Angel Francisco Flores Ayala

student•

Una caracteristica muy importante de PostgreSQL es su capacidad de trabajar con estructuras JSON.

JSON Texto plano - Es unicamente un string de texto.
JSON Binary - Es más rápido de procesar ya que se guarda como un archivo binario.

El uso de objetos nos dará más flexibilidad en el trabajo.

-- Inserción de datos
INSERT INTO  ordenes(info)
VALUES
('{"cliente":"Edna Moda", "items":{"producto":"Biberon", "cantidad":3}}'),
('{"cliente":"Juan Moda", "items":{"producto":"Carro", "cantidad":3}}');

-- Extrae los datos con formato JSON
SELECT info->'cliente' AS cliente FROM ordenes;

-- Extrae los datos con el tipo de dato asignado por Postgres
SELECT info->>'cliente' AS cliente FROM ordenes;

Julián Cárdenas

student•

Excelente resumen!

Pablo Cesar Acosta Paredes

student•

Que bueno tocar el tema del json porque es algo muy importante y cada vez se usa mucho más justamente para optimizar más el desempeño, en mi trabajo no usamos base de datos no sql y cuando tenemos formularios con mucha información entonces asignamos campos json o jsonb. No obstante solemos utilizar también esta función del postgres para convertir la fila o las filas en formato json

SELECT json_build_object( 'id', id, 'info', info )json_data FROM ordenes WHERE info -> 'items' ->> 'cantidad' = '1';

Esto lo que hace es devolver la consulta en formato json completo y eso a su vez al llamar en un formulario que utilice angular o react se puede devolver en formato json sin tener que convertir del lado del javascript(ejemplo)

Carlos Felipe González Calero

student•

Es sencillo, pero no lo he visto en los aportes...

Para sacar la tabla ORDENES completa a nivel texto sería así:

SELECT 
	info ->> 'cliente' AS cliente,
	info -> 'items' ->> 'producto' AS producto,
	info -> 'items' ->> 'cantidad' AS cantidad
FROM ordenes;

Luis Rogelio Reyes Hernandez

student•

Trabajando con objetos

PostgreSQL permite trabajar directamente con objetos tipo JSON esto es una funcionalidad que no brindan todos los manejadores de datos

porque trabajar con objetos JSON es complicado ya que son una cadena de texto con una estructura especifica, y lo que suele ocurrir es que los manejadores de datos guardan un string que evalúan en cada consulta que se les haga

PostgresSQL posee el tipo de dato json y jsonb que guarda los datos de forma binaria, estos tipos de datos permiten que internamente postgresql guarde una estructura con la que puede trabajar más cómodamente los tipos de datos json

Tabla con una columna en formato json

-- DROP TABLE ordenes;
CREATE TABLE ordenes(
  id serial NOT NULL PRIMARY KEY,
  info json NOT NULL
);

INSERT INTO ordenes (info)
VALUES (
  '{"cliente": "David Shanchez", "items": { "producto": "Biberon", "cantidad": "24" }}'
),
(
  '{"cliente": "Jorge Luis", "items": { "producto": "Carro hotweels", "cantidad": "2" }}'
),
(
  '{"cliente": "Sancho Panza", "items": { "producto": "Caja", "cantidad": "13" }}'
);

-- regresa el valor aun en formato json
SELECT
  info -> 'cliente' AS cliente
FROM ordenes;

-- regresa el valor en formato string
SELECT
  info ->> 'cliente' AS cliente
FROM ordenes;

-- usando filtros con un campo json
SELECT
  info ->> 'cliente' AS cliente
FROM ordenes
WHERE info -> 'items' ->> 'producto' = 'Biberon';

Oscar Julian Layton Galindo

student•

Muy útil esta clase, nunca pensé que postgreSQL manejara JSON de manera tan amigable..

Julián Cárdenas

student•

Así es!

JAVIER SANTIAGO SALGADO

student•

CREATE TABLE ordenes(
	ID serial NOT NULL PRIMARY KEY,
	info json NOT NULL
);

SELECT * FROM ordenes;

INSERT INTO ordenes (info)
VALUES
(
	'{"cliente": "David Sanchez", "items":{"producto":"Biberon", "cantidad":"24"}}'
),
(
	'{"cliente": "Edna Cardenas Sanchez", "items":{"producto":"Carro Juguete", "cantidad":"4"}}'
),
(
	'{"cliente": "Israel Vazquez", "items":{"producto":"Tren Juguete", "cantidad":"6"}}'
);

SELECT * FROM ordenes;

SELECT
	info -> 'cliente' AS cliente
FROM ordenes;

SELECT
	info ->> 'cliente' AS cliente
FROM ordenes;

SELECT 
	info ->> 'cliente' AS cliente
FROM ordenes
WHERE info -> 'items' ->> 'producto' = 'Biberon';

JONATAN CADAVID TORRES

student•

Wow esta funcion es muy util, este manejador de bases de datos podria convertirse facilmente en la herramienta principal de una analysta de datos, solo le falta poder generar graficos jeje.

Santiago J. Vasconcello Acuña

student•

Como podría con un SELECT traer la columna cantidad dentro de items siendo este de formato JSON?

kevin Jhordy Puscán Ortiz

student•

select info->items->>cantidad from ordenes;

Luis Fernando Zarazua Aguilar

student•

Supongo que esta información te puede servir:

json_each(json)
select * from json_each('{"a":"foo", "b":"bar"}')

key	value
a	"foo"
b	"bar"

Funciones JSON PostgreSQL

Miguel Angel Alburqueque Alburqueque

student•

según lo investigado JSON no es un lenguaje de programación sino un archivo que contiene datos estructurados, que se utiliza para transferir información entre sistemas.

Rosse SPA

student•

Es el formato del archivo, en realidad, ya que viene del JavaScript Object Notation y lo que quiere decir es que la estructura de los datos contenidos en el archivo es igual a la de un objeto de JavaScript.

Isay Humberto Lucas Hernandez

student•

Wow! Me sorprende como Posgres permite manipular data de tipo JSON de forma tan sencilla y amigable. A diferencia, en Oracle resulta todo un lío.

Luis Fernando Úbeda Camacho

student•

Objetos JSON: Clave-Valor

Vladimir Gutierrez

student•

Genial, muy bueno si se quiere generar un informe historico, ya tengo varios ejemplos en mente.

Ricardo Martinez Martinez

student•

Para crear índices en columnas de tipo jsonb en PostgreSQL, puedes utilizar el índice GIN, que es eficiente para este tipo de datos. La sintaxis básica es:

CREATE INDEX idx_nombre ON nombre_tabla USING GIN (nombre_columna);

Esto permite realizar consultas más rápidas sobre los datos JSON. Por ejemplo:

CREATE INDEX idx_ordenes_info ON ordenes USING GIN (info);

Con esto, mejorarás el rendimiento en las consultas que involucren operaciones sobre el campo info que contiene datos en formato JSON.

Sergio Javier Lopez Olivera

student•

De esta forma podemos hacer comparaciones numéricas, casteando el resultado:

SELECT 
	info ->> 'cliente' AS nombre_cliente
FROM ordenes
WHERE (info -> 'items' ->> 'cantidad')::int > 5 ;
// clientes que compraron más de 5 productos

Franklin Paredes Trejo

student•

Un input sintético

INSERT INTO ordenes(info)
VALUES
('{"cliente":"Edna Moda", "items":{"producto":"Biberon", "cantidad":3}}'),
('{"cliente":"Juan Moda", "items":{"producto":"Carro", "cantidad":3}}'),
('{"cliente":"Luis Torres", "items":{"producto":"Cuna", "cantidad":1}}'),
('{"cliente":"Maria Lopez", "items":{"producto":"Pañales", "cantidad":5}}'),
('{"cliente":"Carlos Ruiz", "items":{"producto":"Juguete", "cantidad":2}}'),
('{"cliente":"Ana Gómez", "items":{"producto":"Termómetro", "cantidad":1}}'),
('{"cliente":"Pedro Sánchez", "items":{"producto":"Toallitas", "cantidad":4}}'),
('{"cliente":"Lucía Fernández", "items":{"producto":"Chupete", "cantidad":2}}'),
('{"cliente":"Diego Martínez", "items":{"producto":"Andador", "cantidad":1}}'),
('{"cliente":"Sofía Herrera", "items":{"producto":"Mochila", "cantidad":3}}');

Cayo Legal

student•

Entonces para poder manejar un json como un string en la query debemos utilizar doble flecha? ->>

Isay Humberto Lucas Hernandez

student•

Es correcto.

Christian Rangel

student•

En la clase el profesor carga los datos en formato JSON manualmente, es posible importar un archivo JSON a una tabla?

Osiris González Galván

student•

¡Hola! ¿Cómo podría agregar valores desde un archivo JSON que ya tengo preparado?

Miguel Angel Velazquez Romero

student•

Al crear la tabla ordenes me sale este error, cómo lo podría solucionar?

Javier Guevara

student•

Por lo que veo ya hay una tabla con ese nombre. Podrias probar eliminar esa tabla y volver a ejecutar el query, o renombrar la tabla del query sería más seguro para la integridad de los datos.

Miguel Angel Velazquez Romero

student•

¡Ay!, cómo no vi la tabla. Creí que refería a un error del modelo relacional o algo así, jejeje.

¡Muchas gracias Javi! :)

William Arevalo

student•

Como coloco multiples productos asociados a un cliente en ese formato JSON, y como los puedo consultar?

- -

student•

Lo muestra en el video, simplemente con varias llaves dentro del objeto JSON. La consulta tambien se muestra en el video y es con SELECT INFO -> "" as x