Operaciones Avanzadas con JSON/JSONB en PostgreSQL

Introducción

En este laboratorio, exploraremos operaciones avanzadas en los tipos de datos JSON/JSONB de PostgreSQL. Nos centraremos en mejorar el rendimiento de las consultas y la manipulación de datos dentro de las columnas JSONB.

Aprenderá cómo crear índices GIN en campos JSONB para optimizar las búsquedas, consultar estructuras JSON anidadas, actualizar elementos específicos dentro de las columnas JSONB y agregar datos JSON para informes y análisis.

Skills Graph

Indexar campos JSONB con GIN

En este paso, exploraremos cómo crear índices GIN en campos JSONB en PostgreSQL. Los índices GIN (Índice Invertido Generalizado) son particularmente útiles para indexar datos JSONB porque le permiten buscar eficientemente claves y valores dentro de documentos JSON.

Primero, conéctese a la base de datos PostgreSQL utilizando el comando psql como el usuario postgres:

sudo -u postgres psql

Ahora, creemos una tabla llamada products con una columna data de tipo JSONB:

CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    data JSONB
);

A continuación, inserte algunos datos de muestra en la tabla products:

INSERT INTO products (data) VALUES
('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}'),
('{"name": "Keyboard", "price": 75, "features": ["Mechanical", "RGB"]}'),
('{"name": "Mouse", "price": 30, "features": ["Wireless", "Ergonomic"]}'),
('{"name": "Monitor", "price": 300, "features": ["27 inch", "144Hz"]}');

Para acelerar las consultas en campos JSONB, podemos crear un índice GIN. Por ejemplo, para indexar todo el documento JSONB, ejecute el siguiente comando:

CREATE INDEX idx_products_data ON products USING GIN (data);

Para verificar que el índice se ha creado, puede utilizar el comando \di en psql:

\di idx_products_data

La salida debería mostrar el índice idx_products_data y sus detalles.

Finalmente, salga del shell psql:

\q

Consultar estructuras JSON anidadas

En este paso, aprenderemos cómo consultar estructuras JSON anidadas en PostgreSQL utilizando el tipo de datos JSONB.

Primero, conéctese a la base de datos PostgreSQL utilizando el comando psql como el usuario postgres:

sudo -u postgres psql

Añadamos algunos datos anidados más complejos a nuestra tabla products.

INSERT INTO products (data) VALUES
('{"name": "Gaming PC", "price": 1500, "specs": {"cpu": "Intel i7", "ram": "32GB", "storage": "1TB SSD", "gpu": "Nvidia RTX 3070"}}'),
('{"name": "Office PC", "price": 800, "specs": {"cpu": "Intel i5", "ram": "16GB", "storage": "500GB SSD", "gpu": "Integrated"}}');

Para acceder a una clave dentro de una estructura JSON anidada, puede encadenar el operador ->. Por ejemplo, para recuperar la CPU de la "Gaming PC", usaría:

SELECT data -> 'specs' ->> 'cpu' FROM products WHERE name = 'Gaming PC';

El operador ->> se utiliza para recuperar el valor como texto. Si utiliza ->, el resultado seguirá siendo un objeto JSONB.

También puede filtrar filas basándose en valores dentro de estructuras JSON anidadas. Por ejemplo, para encontrar todos los productos con una CPU Intel i5:

SELECT * FROM products WHERE data -> 'specs' ->> 'cpu' = 'Intel i5';

Encontremos el nombre del producto que tiene una GPU Nvidia RTX 3070.

SELECT name FROM products WHERE data -> 'specs' ->> 'gpu' = 'Nvidia RTX 3070';

Esta consulta debería devolver "Gaming PC".

Finalmente, salga del shell psql:

\q

Actualizar elementos JSONB específicos

En este paso, aprenderemos cómo actualizar elementos específicos dentro de una columna JSONB en PostgreSQL.

Primero, conéctese a la base de datos PostgreSQL utilizando el comando psql como el usuario postgres:

sudo -u postgres psql

Utilizaremos la función jsonb_set para actualizar elementos específicos dentro de los datos JSONB. La sintaxis básica es:

jsonb_set(target JSONB, path TEXT[], new_value JSONB, create_missing BOOLEAN)

Digamos que queremos actualizar el precio de la "Laptop" a 1250.

UPDATE products
SET data = jsonb_set(data, '{price}', '1250'::JSONB)
WHERE name = 'Laptop';

Para verificar la actualización, puede ejecutar la siguiente consulta:

SELECT data FROM products WHERE name = 'Laptop';

La salida debería mostrar que el precio de la "Laptop" se ha actualizado a 1250.

Aumentemos el precio de la "Office PC" en 100.

UPDATE products
SET data = jsonb_set(data, '{price}', ((data ->> 'price')::numeric + 100)::TEXT::JSONB)
WHERE name = 'Office PC';

Para verificar la actualización, puede ejecutar la siguiente consulta:

SELECT data FROM products WHERE name = 'Office PC';

La salida debería mostrar que el precio de la "Office PC" se ha incrementado en 100.

Finalmente, salga del shell psql:

\q

Agregar datos JSON

En este paso, exploraremos cómo agregar datos almacenados en columnas JSONB en PostgreSQL.

Primero, conéctese a la base de datos PostgreSQL utilizando el comando psql como el usuario postgres:

sudo -u postgres psql

La agregación de datos JSONB a menudo implica extraer valores de los objetos JSONB y luego aplicar funciones de agregación como SUM, AVG, MIN, MAX y COUNT.

Para calcular el precio promedio de todos los productos, puede utilizar la siguiente consulta:

SELECT AVG((data ->> 'price')::numeric) FROM products;

Aquí, extraemos el price como texto usando ->>, lo convertimos a un tipo numérico y luego calculamos el promedio usando la función AVG.

Añadamos un campo "category" (categoría) a nuestros productos:

UPDATE products SET data = jsonb_set(data, '{category}', '"Electronics"'::JSONB) WHERE id IN (1,4,5);
UPDATE products SET data = jsonb_set(data, '{category}', '"Accessories"'::JSONB) WHERE id IN (2,3);
UPDATE products SET data = jsonb_set(data, '{category}', '"Computers"'::JSONB) WHERE id IN (6);

Ahora, podemos contar el número de productos en cada categoría:

SELECT data ->> 'category', COUNT(*) FROM products GROUP BY data ->> 'category';

Esta consulta extrae el valor de category como texto y agrupa las filas basándose en este valor.

Calculemos el precio total de todos los productos en la categoría "Electronics" (Electrónica).

SELECT SUM((data ->> 'price')::numeric) FROM products WHERE data ->> 'category' = 'Electronics';

Esta consulta debería devolver la suma de los precios de la Laptop (Portátil), Monitor y Gaming PC (PC para juegos).

Finalmente, salga del shell psql:

\q

Resumen

En este laboratorio, exploramos operaciones avanzadas en datos JSONB de PostgreSQL, centrándonos en la indexación, la consulta (query), la actualización y la agregación. Creamos índices GIN en campos JSONB para optimizar el rendimiento de las consultas. También demostramos cómo consultar estructuras JSON anidadas y actualizar elementos JSONB específicos. Finalmente, aprendimos cómo agregar datos JSON utilizando varias funciones.