JSONB_OBJECT_AGG en PostgreSQL: convierte filas clave-valor en un solo objeto JSON

JSONB_OBJECT_AGG(key, value) es una funcion de agregacion que toma dos columnas, una clave y un valor, de muchas filas y las pliega en un solo objeto JSON. Es la forma directa de convertir una tabla de ajustes en un mapa como { "clave": valor } sin una segunda consulta ni construir el JSON en el codigo de la aplicacion.

Sintaxis y ejemplo basico

La funcion recibe exactamente dos argumentos: el primero se convierte en la clave del objeto y el segundo en su valor. La clave se convierte a texto; el valor conserva su tipo JSON. La llamada suele ir junto a un GROUP BY, pero sin agrupacion pliega toda la tabla en un unico objeto.

SELECT JSONB_OBJECT_AGG(key, value) AS settings
FROM settings;

Si settings contiene las filas ('theme', 'dark') y ('lang', 'en'), el resultado es {"theme": "dark", "lang": "en"}. Algunos comportamientos que conviene recordar:

• La clave no puede ser NULL; una fila asi provoca un error. Filtralas antes con WHERE key IS NOT NULL.
• Un valor NULL esta permitido y entra en el objeto como null de JSON.
• El orden de las claves en jsonb no se conserva ni importa: es un mapa, no una lista.
• Un grupo vacio produce NULL, no un objeto vacio {}.

Un documento de busqueda por grupo

La aplicacion mas util es construir un objeto JSON por entidad. Supongamos que cada usuario tiene un conjunto de pares clave-valor en una tabla user_settings(user_id, key, value). Arma el perfil de ajustes de cada usuario en una sola consulta:

SELECT
    user_id,
    JSONB_OBJECT_AGG(key, value) AS prefs
FROM user_settings
GROUP BY user_id;

El origen de claves y valores no tiene por que ser una tabla de ajustes ya hecha. Puedes agregar dos columnas cualesquiera, por ejemplo para construir un mapa "estado del pedido -> total de ese estado" por usuario:

SELECT
    user_id,
    JSONB_OBJECT_AGG(status, total) AS totals_by_status
FROM (
    SELECT user_id, status, SUM(amount) AS total
    FROM orders
    GROUP BY user_id, status
) s
GROUP BY user_id;

Aqui la subconsulta garantiza exactamente una fila por par (user_id, status), es decir, exactamente una clave en el objeto.

Comportamiento ante claves duplicadas

Esta es la trampa principal. Si un grupo tiene dos valores bajo la misma clave, JSONB_OBJECT_AGG no falla y no los fusiona; simplemente conserva uno, y cual depende del orden de procesamiento de las filas, asi que es practicamente impredecible.

-- two rows share the key 'color' -> only one survives, order is undefined
SELECT JSONB_OBJECT_AGG(key, value)
FROM (VALUES ('color', 'red'), ('color', 'blue')) AS t(key, value);

Trampa: no dependas de un silencioso "gana el ultimo". Si, por ejemplo, debe ganar el registro mas reciente, haz la deduplicacion explicita con DISTINCT ON o una funcion de ventana antes de agregar:

SELECT user_id, JSONB_OBJECT_AGG(key, value) AS prefs
FROM (
    SELECT DISTINCT ON (user_id, key)
        user_id, key, value
    FROM user_settings
    ORDER BY user_id, key, updated_at DESC
) latest
GROUP BY user_id;

Asi decides tu que fila gana ante una clave duplicada, en lugar de dejarlo al planificador.

JSONB_OBJECT_AGG frente a JSONB_AGG de objetos

Una confusion frecuente: JSONB_AGG tambien construye JSON, pero un array, no un objeto. La diferencia esta en la forma del resultado y en como lo recorres despues.

-- object (map): key lookup by name
SELECT JSONB_OBJECT_AGG(key, value)
FROM user_settings WHERE user_id = 1;
-- => {"theme": "dark", "lang": "en"}

-- array of objects: ordered list, no direct key lookup
SELECT JSONB_AGG(JSONB_BUILD_OBJECT('key', key, 'value', value))
FROM user_settings WHERE user_id = 1;
-- => [{"key": "theme", "value": "dark"}, {"key": "lang", "value": "en"}]

Como elegir:

• Necesitas busqueda por nombre (prefs->>'theme') y claves unicas? Usa JSONB_OBJECT_AGG. Es un diccionario.
• Necesitas una lista ordenada, elementos repetidos o varios campos por registro? Usa JSONB_AGG. Es una lista.
• JSONB_AGG admite un ORDER BY dentro del agregado; para un objeto el orden de las claves carece de sentido de todos modos.

Notas sobre otras bases de datos

En MySQL el equivalente es JSON_OBJECTAGG(key, value), que funciona de forma parecida, pero el comportamiento ante claves duplicadas varia segun la version y el modo, asi que deduplica de antemano igual que en PostgreSQL. ClickHouse no tiene un agregado directo: construyes un mapa con map() o un groupArray de pares y lo conviertes, y el tipo Map reemplaza al objeto JSON. Cuando de verdad necesitas un documento de busqueda compacto por grupo, JSONB_OBJECT_AGG en PostgreSQL sigue siendo la herramienta mas directa.