Extrayendo datos anidados: jsonb_to_recordset()

Ahora vamos a meternos en escenarios más avanzados con datos en formato JSONB — extrayendo datos anidados y convirtiéndolos en filas de tabla. ¿Te preguntas para qué sirve esto? ¡Muy fácil! Imagina que te pasan un objeto JSON con un array de compras y te piden calcular el total de todas las compras o mostrarlas en formato tabla para un informe. ¡Vamos a ver cómo hacerlo ahora mismo!

¿Por qué no podemos simplemente trabajar con JSON como si fuera texto o una estructura? Veamos el caso. En muchas apps reales, los datos se guardan como arrays de JSON:

[
  { "id": 1, "product_name": "Laptop", "price": 1200 },
  { "id": 2, "product_name": "Smartphone", "price": 800 },
  { "id": 3, "product_name": "Tablet", "price": 400 }
]

Está guay, pero cuando analizas datos, muchas veces necesitas convertir el array en una tabla para hacer cosas como filtrar, ordenar o agregar. Imagina: «Todos los pedidos con importe mayor de 500 dólares». JSONB por sí solo no te deja hacer esto tan fácil como querrías. Aquí es donde jsonb_to_recordset() te salva la vida.

Trabajando con jsonb_to_recordset()

La función jsonb_to_recordset() te permite convertir un array de objetos JSONB en filas de tabla. Literalmente transforma cada elemento del array en una fila, y las claves en columnas. Esta función es imprescindible cuando tienes datos muy anidados o arrays de objetos.

Sintaxis

SELECT *
FROM jsonb_to_recordset('[ array JSONB ]') AS alias(columna1 TIPO, columna2 TIPO, ...);

• [ array JSONB ]: el array de objetos JSON del que vamos a sacar los datos.
• AS alias: creamos un nombre temporal para la tabla resultante.
• columna1 TIPO, columna2 TIPO: definimos cómo se llamarán las columnas y qué tipos de datos usarán (por ejemplo, INTEGER, TEXT, NUMERIC).

Ejemplo: convertir un array JSONB en filas

Supón que tenemos la siguiente tabla:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT,
    products JSONB
);

Y en la tabla hay estos datos:

Ahora el reto: mostrar la lista de todos los productos de todos los pedidos en formato tabla. Así lo haces usando jsonb_to_recordset():

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC);

Resultado:

Ejemplo: filtrando datos

Vamos a complicarlo un poco. Queremos mostrar solo los productos de los pedidos que cuestan más de 100 dólares:

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
WHERE
    p.price > 100;

Resultado:

Ejemplo: agregando datos

¿Qué tal si queremos calcular el total de todos los productos en los pedidos? Solo usamos funciones de agregación:

SELECT
    o.customer_name,
    SUM(p.price) AS total_amount
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
GROUP BY
    o.customer_name;

Resultado:

Notas importantes

Asegúrate de que la estructura del array JSON sea igual para todos los objetos. Si un objeto tiene claves diferentes o estructuras anidadas, puedes tener errores o resultados raros.

Define bien los tipos de datos para las columnas extraídas. Por ejemplo, si una clave tiene una fecha, usa DATE; para números, NUMERIC o INTEGER.

Recuerda que jsonb_to_recordset() solo convierte arrays JSONB; no funciona con objetos individuales.

Errores típicos y cómo evitarlos

Uso incorrecto de tipos de datos: si en el array JSONB hay valores con tipos distintos (por ejemplo, una cadena en vez de un número), tendrás un error. Es mejor convertir los datos al formato correcto antes de usar la función.

Acceso a claves incorrectas: si falta una clave en alguno de los objetos del array, habrá error. Revisa la estructura de los datos antes de lanzar la consulta.

Falta de datos: si la columna JSONB está vacía (NULL), la función no devuelve resultados. En estos casos, mete comprobaciones, por ejemplo con COALESCE().

Aplicación práctica

jsonb_to_recordset() se usa un montón en tareas reales, como procesar pedidos, analizar informes, registrar acciones de usuarios y manejar APIs externas. Por ejemplo:

• En tiendas online es fácil convertir arrays de productos en tablas y hacer informes.
• Un REST API puede devolver datos en formato JSON, que puedes analizar cómodamente con PostgreSQL.
• Las apps de analítica suelen usar esta función para procesar datos complejos y multinivel.