Operaciones Avanzadas de JSON/JSONB en PostgreSQL

Introducción

En este laboratorio, explorará operaciones avanzadas utilizando el tipo de dato JSONB de PostgreSQL. Aprenderá a almacenar, gestionar y consultar datos JSON de manera efectiva dentro de una base de datos relacional.

El laboratorio lo guiará a través de la creación de una tabla con una columna JSONB, la inserción de datos y luego la aplicación de un índice GIN para mejorar el rendimiento de la búsqueda. También practicará la consulta de estructuras JSON anidadas, la actualización de elementos específicos dentro de un documento JSONB y la realización de cálculos agregados sobre sus datos JSON.

Crear una Tabla e Indexar Campos JSONB

En este paso, creará una tabla para almacenar información de productos utilizando el tipo de dato JSONB y luego creará un índice GIN para optimizar las consultas sobre estos datos. JSONB almacena datos JSON en un formato binario descompuesto, que es ligeramente más lento de ingresar pero mucho más rápido de procesar. Un GIN (Índice Invertido Generalizado) es ideal para indexar valores compuestos como los de una columna JSONB.

Primero, abra una terminal y conéctese a la base de datos PostgreSQL utilizando el shell interactivo psql:

sudo -u postgres psql

Ahora verá el prompt de PostgreSQL, que se parece a postgres=#.

A continuación, cree una tabla llamada products con una columna id y una columna data de tipo JSONB.

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

Ahora, inserte algunos datos de productos de ejemplo en la tabla products.

INSERT INTO products (data) VALUES
('{"name": "Laptop", "price": 1200, "tags": ["electronics", "computer"]}'),
('{"name": "Keyboard", "price": 75, "tags": ["electronics", "accessory"]}'),
('{"name": "Mouse", "price": 30, "tags": ["electronics", "accessory"]}'),
('{"name": "Monitor", "price": 300, "tags": ["electronics", "display"]}');

Para acelerar significativamente las búsquedas dentro de la columna data, cree un índice GIN sobre ella.

CREATE INDEX idx_products_data ON products USING GIN (data);

Puede verificar que el índice se creó correctamente utilizando el comando \di, que lista todos los índices.

\di

Debería ver idx_products_data en la lista de relaciones, similar a la salida a continuación:

                                List of relations
 Schema |        Name         | Type  |  Owner   |   Table   |    Size    | Description
--------+---------------------+-------+----------+-----------+------------+-------------
 public | idx_products_data   | index | postgres | products  | 16 kB      |
 public | products_pkey       | index | postgres | products  | 16 kB      |
(2 rows)

Ha configurado correctamente su tabla y ha indexado la columna JSONB. Permanecerá en el shell psql para el siguiente paso.

Consultar Estructuras JSON Anidadas

En este paso, aprenderá a consultar datos dentro de columnas JSONB, incluidas las estructuras anidadas. PostgreSQL proporciona varios operadores para este propósito.

Primero, insertemos algunos productos con datos más complejos y anidados en la tabla products.

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

Para acceder a una clave de nivel superior, puede usar el operador ->>, que devuelve el valor como texto. Busquemos el precio del "Laptop".

SELECT data ->> 'price' FROM products WHERE data ->> 'name' = 'Laptop';

La salida será:

 price
-------
 1200
(1 row)

Para acceder a una clave dentro de un objeto JSON anidado, puede encadenar los operadores -> y ->>. El operador -> obtiene un campo de objeto JSON, mientras que ->> lo obtiene como texto. Recuperemos la especificación de CPU para el "Gaming PC".

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

Este comando devolverá el tipo de CPU:

   ?column?
--------------
 Intel i7
(1 row)

También puede usar estos operadores en la cláusula WHERE para filtrar resultados basándose en valores anidados. Por ejemplo, encuentre todos los productos que tienen una CPU "Intel i5".

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

La consulta devolverá el nombre del producto coincidente:

   name
-----------
 Office PC
(1 row)

Ahora puede consultar datos tanto de nivel superior como anidados dentro de sus columnas JSONB.

Actualizar Elementos Específicos de JSONB

En este paso, aprenderá a modificar elementos específicos dentro de una columna JSONB utilizando la función jsonb_set. Esto es más eficiente que recuperar todo el objeto JSON, modificarlo en su aplicación y volver a escribirlo.

La función jsonb_set tiene la siguiente sintaxis: jsonb_set(target_jsonb, path_array, new_value_jsonb, create_if_missing).

Actualicemos el precio del "Laptop" de 1200 a 1250. La ruta al precio es '{price}', y el nuevo valor debe ser casteado a JSONB.

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

Verifique la actualización seleccionando los datos del portátil.

SELECT data FROM products WHERE data ->> 'name' = 'Laptop';

La salida debería mostrar el nuevo precio:

                                   data
--------------------------------------------------------------------------
 {"name": "Laptop", "price": 1250, "tags": ["electronics", "computer"]}
(1 row)

También puede actualizar valores anidados. Actualicemos la RAM del "Office PC" a "32GB". La ruta a la clave anidada ram es '{specs,ram}'.

UPDATE products
SET data = jsonb_set(data, '{specs,ram}', '"32GB"'::jsonb)
WHERE data ->> 'name' = 'Office PC';

Ahora, verifique esta actualización anidada.

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

El resultado confirmará el cambio en el valor de ram:

                          ?column?
------------------------------------------------------------
 {"cpu": "Intel i5", "ram": "32GB", "storage": "500GB SSD"}
(1 row)

Ahora ha aprendido a realizar actualizaciones específicas en datos JSONB.

Agregar Datos JSON

En este último paso, realizará cálculos de agregación sobre datos extraídos de columnas JSONB. Esto es útil para informes y análisis.

Para realizar cálculos, a menudo necesita extraer un valor y castearlo a un tipo numérico. Calculemos el precio promedio de todos los productos.

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

La consulta extrae el price como texto, lo castea a numeric y luego calcula el promedio. La salida será un solo número:

          avg
------------------------
 659.1666666666666667
(1 row)

También puede usar funciones de agregación con una cláusula GROUP BY. Encontremos el número total de productos para cada etiqueta (tag). Para hacer esto, primero necesitamos desenrollar el array tags en filas separadas usando jsonb_array_elements_text.

SELECT tag, COUNT(*)
FROM products, jsonb_array_elements_text(data -> 'tags') AS tag
GROUP BY tag;

Esta consulta produce un recuento para cada etiqueta única:

     tag     | count
-------------+-------
 accessory   |     2
 electronics |     4
 computer    |     1
 display     |     1
(4 rows)

Finalmente, encontremos el valor total de todos los productos que tienen la etiqueta "electronics".

SELECT SUM((data ->> 'price')::numeric)
FROM products
WHERE data -> 'tags' @> '"electronics"'::jsonb;

El operador @> verifica si el valor JSONB de la izquierda contiene el valor JSONB de la derecha. Aquí, verificamos si el array tags contiene el elemento "electronics".

El resultado es la suma de los precios de los cuatro productos electrónicos:

  sum
--------
 1655
(1 row)

Ahora ha aprendido a utilizar funciones de agregación en sus datos JSONB. Para salir del shell psql, escriba:

\q

Resumen

En este laboratorio, ha aprendido varias operaciones avanzadas para manejar datos JSONB en PostgreSQL. Comenzó creando una tabla con una columna JSONB y vio la importancia de usar un índice GIN para optimizar el rendimiento de las consultas. Luego practicó la consulta de datos JSONB tanto de nivel superior como anidados utilizando los operadores -> y ->>. Además, aprendió a realizar modificaciones específicas en documentos JSONB con la función jsonb_set y cómo realizar análisis de datos potentes utilizando funciones de agregación como AVG, COUNT y SUM en sus datos JSON.