JSON_AGG y JSONB_AGG son funciones de agregacion que pliegan las filas de un grupo en un unico array JSON. Convierten "muchas filas por clave" en "una fila con JSON", y son la forma mas directa de devolver una respuesta de API anidada desde la base de datos, sin armar el JSON en el codigo de la aplicacion.
JSON_AGG frente a JSONB_AGG
Ambas reunen valores en un array JSON, pero lo almacenan de forma distinta:
JSON_AGG devuelve el tipo json: texto "tal cual", conservando el orden de las claves y los duplicados.JSONB_AGG devuelve jsonb: una forma binaria donde las claves duplicadas se colapsan (gana la ultima), los espacios se normalizan y el acceso a los campos es mas rapido.- Para la mayoria de las respuestas de API, usa
JSONB_AGG: es mas compacto y mas facil de filtrar o indexar mas adelante.
SELECT JSONB_AGG(t ORDER BY t.id) AS tasks
FROM tasks t;
Al pasar la fila completa t al agregado, obtienes un array de objetos cuyas claves son los nombres de las columnas. Es la manera mas rapida de "entregar una tabla como JSON".
ORDER BY dentro del agregado
El orden de los elementos del array no esta garantizado sin un ORDER BY dentro de la propia llamada. Nunca dependas del orden de las filas de la tabla; define la ordenacion de forma explicita:
SELECT
u.id,
u.email,
JSONB_AGG(o.amount ORDER BY o.created_at DESC) AS amounts
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;
El ORDER BY vive dentro de los parentesis del agregado, no en la consulta externa. Un ORDER BY externo ordena las filas del resultado, pero no los elementos dentro de cada array JSON, una confusion frecuente.
Un objeto por fila con jsonb_build_object
Un array de escalares pelado rara vez es lo que quieres; lo normal es construir un array de objetos. jsonb_build_object arma un objeto JSON a partir de pares clave-valor, y JSONB_AGG pliega esos objetos en un array:
SELECT
u.id,
u.email,
JSONB_AGG(
jsonb_build_object(
'order_id', o.id,
'amount', o.amount,
'status', o.status
)
ORDER BY o.created_at DESC
) AS orders
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;
Asi controlas por completo los nombres de los campos en la respuesta: no tienen por que coincidir con los nombres de las columnas. Es util cuando el esquema de la base y el contrato de la API divergen.
Una respuesta de API anidada en una consulta
Estas piezas se combinan en un documento completo de "usuario con su lista de pedidos" en una sola pasada, sin consultas N+1 ni armado en la aplicacion:
SELECT jsonb_build_object(
'user_id', u.id,
'email', u.email,
'country', u.country,
'orders', JSONB_AGG(
jsonb_build_object('id', o.id, 'amount', o.amount)
ORDER BY o.created_at DESC
)
) AS payload
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 42
GROUP BY u.id, u.email, u.country;
Para una jerarquia de empleados, el mismo truco reune a cada jefe junto con sus subordinados:
SELECT
m.name AS manager,
JSONB_AGG(
jsonb_build_object('name', e.name, 'salary', e.salary)
ORDER BY e.salary DESC
) AS reports
FROM employees m
JOIN employees e ON e.manager_id = m.id
GROUP BY m.id, m.name;
FILTER para omitir los NULL
JSONB_AGG incluye los valores NULL en el array: una fila sin coincidencia en un LEFT JOIN produce un elemento null. Para evitarlo, anade un FILTER:
SELECT
u.id,
JSONB_AGG(o.id) FILTER (WHERE o.id IS NOT NULL) AS order_ids
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;
Trampa: si un usuario no tiene pedidos, JSONB_AGG(...) FILTER (...) devuelve NULL, no un array vacio []. Muchos clientes de API rechazan null donde esperan un array. Envuelvelo en COALESCE(JSONB_AGG(...) FILTER (...), '[]'::jsonb) para emitir siempre un array.
MySQL y ClickHouse
En MySQL 8 el equivalente es JSON_ARRAYAGG, y JSON_OBJECT construye el objeto por fila:
SELECT
user_id,
JSON_ARRAYAGG(JSON_OBJECT('id', id, 'amount', amount)) AS orders
FROM orders
GROUP BY user_id;
Que tener presente sobre MySQL:
JSON_ARRAYAGG no tiene un ORDER BY interno; impon el orden mediante una subconsulta ya ordenada.- No hay separacion entre
json y jsonb: el tipo es siempre JSON, ya almacenado en forma binaria normalizada.
ClickHouse no tiene un equivalente directo; lo armas con groupArray y lo serializas, o usas sus funciones de cadenas JSON. Cuando necesitas un JSON anidado predecible con orden y filtrado, PostgreSQL con JSONB_AGG ... ORDER BY ... FILTER sigue siendo la herramienta mas directa.
JSON_AGGyJSONB_AGGson funciones de agregacion que pliegan las filas de un grupo en un unico array JSON. Convierten "muchas filas por clave" en "una fila con JSON", y son la forma mas directa de devolver una respuesta de API anidada desde la base de datos, sin armar el JSON en el codigo de la aplicacion.JSON_AGG frente a JSONB_AGG
Ambas reunen valores en un array JSON, pero lo almacenan de forma distinta:
JSON_AGGdevuelve el tipojson: texto "tal cual", conservando el orden de las claves y los duplicados.JSONB_AGGdevuelvejsonb: una forma binaria donde las claves duplicadas se colapsan (gana la ultima), los espacios se normalizan y el acceso a los campos es mas rapido.JSONB_AGG: es mas compacto y mas facil de filtrar o indexar mas adelante.SELECT JSONB_AGG(t ORDER BY t.id) AS tasks FROM tasks t;Al pasar la fila completa
tal agregado, obtienes un array de objetos cuyas claves son los nombres de las columnas. Es la manera mas rapida de "entregar una tabla como JSON".ORDER BY dentro del agregado
El orden de los elementos del array no esta garantizado sin un
ORDER BYdentro de la propia llamada. Nunca dependas del orden de las filas de la tabla; define la ordenacion de forma explicita:SELECT u.id, u.email, JSONB_AGG(o.amount ORDER BY o.created_at DESC) AS amounts FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.id, u.email;El
ORDER BYvive dentro de los parentesis del agregado, no en la consulta externa. UnORDER BYexterno ordena las filas del resultado, pero no los elementos dentro de cada array JSON, una confusion frecuente.Un objeto por fila con jsonb_build_object
Un array de escalares pelado rara vez es lo que quieres; lo normal es construir un array de objetos.
jsonb_build_objectarma un objeto JSON a partir de pares clave-valor, yJSONB_AGGpliega esos objetos en un array:SELECT u.id, u.email, JSONB_AGG( jsonb_build_object( 'order_id', o.id, 'amount', o.amount, 'status', o.status ) ORDER BY o.created_at DESC ) AS orders FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.id, u.email;Asi controlas por completo los nombres de los campos en la respuesta: no tienen por que coincidir con los nombres de las columnas. Es util cuando el esquema de la base y el contrato de la API divergen.
Una respuesta de API anidada en una consulta
Estas piezas se combinan en un documento completo de "usuario con su lista de pedidos" en una sola pasada, sin consultas N+1 ni armado en la aplicacion:
SELECT jsonb_build_object( 'user_id', u.id, 'email', u.email, 'country', u.country, 'orders', JSONB_AGG( jsonb_build_object('id', o.id, 'amount', o.amount) ORDER BY o.created_at DESC ) ) AS payload FROM users u JOIN orders o ON o.user_id = u.id WHERE u.id = 42 GROUP BY u.id, u.email, u.country;Para una jerarquia de empleados, el mismo truco reune a cada jefe junto con sus subordinados:
SELECT m.name AS manager, JSONB_AGG( jsonb_build_object('name', e.name, 'salary', e.salary) ORDER BY e.salary DESC ) AS reports FROM employees m JOIN employees e ON e.manager_id = m.id GROUP BY m.id, m.name;FILTER para omitir los NULL
JSONB_AGGincluye los valoresNULLen el array: una fila sin coincidencia en unLEFT JOINproduce un elementonull. Para evitarlo, anade unFILTER:SELECT u.id, JSONB_AGG(o.id) FILTER (WHERE o.id IS NOT NULL) AS order_ids FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id;MySQL y ClickHouse
En MySQL 8 el equivalente es
JSON_ARRAYAGG, yJSON_OBJECTconstruye el objeto por fila:-- 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;Que tener presente sobre MySQL:
JSON_ARRAYAGGno tiene unORDER BYinterno; impon el orden mediante una subconsulta ya ordenada.jsonyjsonb: el tipo es siempreJSON, ya almacenado en forma binaria normalizada.ClickHouse no tiene un equivalente directo; lo armas con
groupArrayy lo serializas, o usas sus funciones de cadenas JSON. Cuando necesitas un JSON anidado predecible con orden y filtrado, PostgreSQL conJSONB_AGG ... ORDER BY ... FILTERsigue siendo la herramienta mas directa.