Ejemplos de consultas complejas con JSONB

En las lecciones anteriores ya vimos lo básico de JSONB: cómo crear, modificar y sacar datos. Ahora toca lo bueno — consultas complejas que muestran todo el poder de este tipo de datos.

Imagina una tienda online con un catálogo de productos. Cada producto tiene info básica (nombre, ID), pero las características pueden ser muy diferentes: un portátil tiene RAM y CPU, la ropa — tallas y materiales, los libros — autores y géneros. ¿Guardar todo esto en tablas separadas? Un rollo. ¿En JSONB? ¡Perfecto! Pero, ¿cómo encuentras todos los productos de una marca concreta, los ordenas por precio o calculas estadísticas por categorías? ¿Cómo currar con datos que no están en columnas normales, sino metidos dentro de una estructura JSON?

Hoy vamos a ver escenarios reales: desde filtrado simple hasta consultas complejas con agrupación y agregación. Vas a ver cómo JSONB convierte PostgreSQL en una herramienta súper flexible para cualquier tipo de datos.

Filtrado de datos en JSONB

Filtrar datos es como un colador para el té: dejas solo lo que quieres y quitas lo demás. Con JSONB es aún más divertido, porque puedes filtrar no solo por columnas normales, sino también por datos que están bien metidos dentro de la estructura JSON.

Operadores para filtrar JSONB:

• @> — "JSONB-contiene". Comprueba si el objeto JSONB contiene el subconjunto indicado.
• ? — "Clave presente". Comprueba si la clave indicada está en el objeto JSONB.
• ?| — "Cualquiera de las claves presente". Comprueba si al menos una de las claves indicadas está.
• ?& — "Todas las claves presentes". Comprueba que todas las claves indicadas están.

Ejemplo: filtrado por clave y su valor. Supón que tienes una tabla products con una columna details que guarda info en JSONB sobre los productos:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);

Ejemplo de datos:

INSERT INTO products (name, details) VALUES
('Laptop', '{"brand": "Dell", "price": 1200, "specs": {"ram": "16GB", "cpu": "i7"}}'),
('Smartphone', '{"brand": "Apple", "price": 800, "specs": {"ram": "4GB", "cpu": "A15"}}'),
('Tablet', '{"brand": "Samsung", "price": 500, "specs": {"ram": "8GB", "cpu": "Exynos"}}');

Resultado:

Para encontrar todos los productos con brand "Apple":

SELECT *
FROM products 
WHERE details @> '{"brand": "Apple"}';

Resultado:

Si quieres encontrar todos los productos que tienen la clave specs, usa el operador ?:

SELECT *
FROM products 
WHERE details ? 'specs';

Resultado:

Todas las filas tienen el campo details y la clave specs.

Ordenación de datos en JSONB

A veces necesitas ordenar los datos no por columnas normales, sino por valores que están dentro de JSONB. Para eso puedes usar los operadores ->> (extrae el valor como texto) y CAST para convertir el texto al tipo que quieras.

Ejemplo: vamos a ordenar los productos por precio:

SELECT *
FROM products 
ORDER BY (details->>'price')::INTEGER;

Resultado:

Agrupación de datos en JSONB

Agrupar te permite agregar datos y sacar estadísticas. Por ejemplo, puedes ver cuántos productos hay de cada marca.

Ejemplo: vamos a contar cuántos productos hay por cada marca:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
GROUP BY details->>'brand';

Resultado:

Ejemplos prácticos

Filtrado y agrupación. Vamos a contar cuántos productos de cada marca cuestan más de 600:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
WHERE (details->>'price')::INTEGER > 600
GROUP BY details->>'brand';

Resultado:

Ordenación después de agrupar. Ahora vamos a ordenar las marcas por número de productos:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
GROUP BY details->>'brand'
ORDER BY product_count DESC;

Consulta compleja: filtrado, ordenación, agrupación

Imagina que quieres encontrar las marcas que tienen productos de más de 600 y elegir el producto más barato de cada marca. Así se hace:

WITH filtered_products AS (
    SELECT *
    FROM products
    WHERE (details->>'price')::INTEGER > 600
)
SELECT
    details->>'brand' AS brand,
    MIN((details->>'price')::INTEGER) AS min_price
FROM filtered_products
GROUP BY details->>'brand'
ORDER BY min_price;

Resultado:

Errores típicos y consejos

Error: Uso incorrecto de operadores. No confundas los operadores -> y ->>: el primero devuelve un objeto, el segundo — un valor de texto.

Error: Problemas de rendimiento. Si haces muchas consultas complejas, crea un índice GIN en la columna JSONB.

Error: Problemas de tipos. Los valores de JSONB son cadenas, así que no olvides usar CAST.

Ejemplo de creación de índice:

CREATE INDEX idx_products_details ON products USING GIN (details);

Ahora el filtrado tipo details @> '{"brand": "Apple"}' irá mucho más rápido.