JSONB_AGG en PostgreSQL: agrupar filas en un array JSON para tus JSON APIs

jsonb_agg es una funcion de agregacion que reune los valores de todas las filas de un grupo en un solo array JSON. Mientras array_agg produce un array de un solo tipo, jsonb_agg acepta cualquier cosa, incluidas filas completas de una tabla, y por eso es el bloque base de las JSON APIs: una consulta y tienes un documento con colecciones anidadas.

Sintaxis y ejemplo basico

La firma coincide con cualquier agregado: jsonb_agg(item ORDER BY created_at), normalmente junto a un GROUP BY. El item suele ser un row_to_json(...) o un jsonb_build_object(...) para que cada elemento del array sea un objeto y no un escalar suelto.

SELECT
    user_id,
    jsonb_agg(amount ORDER BY created_at) AS amounts
FROM orders
GROUP BY user_id;

Algunos comportamientos que conviene conocer:

• El orden de los elementos no esta garantizado sin un ORDER BY dentro del agregado. Nunca dependas del orden de las filas en la tabla.
• El tipo del resultado es siempre jsonb, sin importar el tipo de la expresion interna.
• Las filas NULL entran en el array como JSON null. Para descartarlas, anade FILTER (WHERE expr IS NOT NULL).
• Un grupo vacio produce NULL, no un array vacio []. Esa es la trampa principal, la vemos abajo.

Un array de objetos con ORDER BY y FILTER

Los escalares son el calentamiento. En la practica metes objetos en el array: para cada usuario, la lista de sus pedidos, ordenada y filtrada dentro de la propia llamada.

SELECT
    u.id,
    u.email,
    jsonb_agg(
        jsonb_build_object('id', o.id, 'amount', o.amount, 'at', o.created_at)
        ORDER BY o.created_at DESC
    ) FILTER (WHERE o.status = 'paid') AS paid_orders
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

jsonb_build_object te da control total sobre los nombres de las claves, mientras que to_jsonb(o) toma la fila entera con todas sus columnas. Elige segun necesites o no un contrato de respuesta estricto.

Un documento padre con un array anidado

El patron mas comun de las JSON APIs es un padre con una coleccion anidada de hijos. Se arma con una subconsulta correlacionada: la consulta externa recorre los usuarios y la interna agrega los pedidos de cada uno con jsonb_agg.

SELECT jsonb_build_object(
    'id', u.id,
    'email', u.email,
    'orders', (
        SELECT jsonb_agg(
            jsonb_build_object('id', o.id, 'amount', o.amount)
            ORDER BY o.created_at
        )
        FROM orders o
        WHERE o.user_id = u.id
    )
) AS user_doc
FROM users u;

Una sola pasada y cada fila del resultado esta lista para enviar al cliente. Sin N+1 en la aplicacion, sin armar el arbol en codigo.

El grupo vacio: COALESCE a '[]'

Aqui esta la trampa. Si un usuario no tiene pedidos, la subconsulta devuelve NULL, no [], y un frontend que espera un array se rompe. Envuelvelo en COALESCE:

SELECT jsonb_build_object(
    'id', u.id,
    'orders', COALESCE(
        (
            SELECT jsonb_agg(jsonb_build_object('id', o.id) ORDER BY o.created_at)
            FROM orders o
            WHERE o.user_id = u.id
        ),
        '[]'::jsonb
    )
) AS user_doc
FROM users u;

Trampa: FILTER tambien puede convertir un grupo no vacio en NULL. Si ninguna fila pasa el filtro, jsonb_agg(...) FILTER (...) devuelve NULL, no []. Por eso COALESCE(..., '[]'::jsonb) hace falta siempre que haya un FILTER, aunque el grupo tenga filas con seguridad.

La misma regla vale para una jerarquia: reune en un array los subordinados de cada jefe y garantiza [] para los que no tienen ninguno.

SELECT jsonb_build_object(
    'manager', m.name,
    'reports', COALESCE(
        (
            SELECT jsonb_agg(e.name ORDER BY e.salary DESC)
            FROM employees e
            WHERE e.manager_id = m.id
        ),
        '[]'::jsonb
    )
) AS team
FROM employees m;

MySQL y ClickHouse

En MySQL 8 el equivalente es JSON_ARRAYAGG, acompanado de JSON_OBJECT para los objetos:

-- MySQL 8: array of objects, one per order
SELECT user_id,
       JSON_ARRAYAGG(JSON_OBJECT('id', id, 'amount', amount)) AS orders
FROM orders
GROUP BY user_id;

Diferencias que conviene recordar:

• JSON_ARRAYAGG no tiene un ORDER BY interno; impon el orden con una subconsulta ya ordenada.
• Tampoco hay FILTER; filtra con una clausula WHERE o una expresion condicional dentro.
• En ClickHouse un resultado parecido sale de groupArray sobre un tuple, o combinandolo con toJSONString.

Cuando necesitas un documento JSON predecible con colecciones anidadas y un [] garantizado en lugar de NULL, PostgreSQL con jsonb_agg ... ORDER BY ... FILTER mas COALESCE sigue siendo la herramienta mas directa.