JSONB_OBJECT_AGG no PostgreSQL: transforme linhas chave-valor em um unico objeto JSON

JSONB_OBJECT_AGG(key, value) e uma funcao de agregacao que pega duas colunas, uma chave e um valor, de varias linhas e as dobra em um unico objeto JSON. E a forma direta de transformar uma tabela de configuracoes em um mapa como { "chave": valor } sem uma segunda consulta nem montar o JSON no codigo da aplicacao.

Sintaxe e exemplo basico

A funcao recebe exatamente dois argumentos: o primeiro vira a chave do objeto e o segundo o seu valor. A chave e convertida para texto; o valor mantem o seu tipo JSON. A chamada costuma ficar ao lado de um GROUP BY, mas sem agrupamento ela dobra a tabela inteira em um unico objeto.

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

Se settings contem as linhas ('theme', 'dark') e ('lang', 'en'), o resultado e {"theme": "dark", "lang": "en"}. Alguns comportamentos que vale lembrar:

• A chave nao pode ser NULL; uma linha assim gera um erro. Filtre-as antes com WHERE key IS NOT NULL.
• Um valor NULL e permitido e entra no objeto como null de JSON.
• A ordem das chaves no jsonb nao e preservada nem importa: e um mapa, nao uma lista.
• Um grupo vazio produz NULL, nao um objeto vazio {}.

Um documento de busca por grupo

A aplicacao mais util e construir um objeto JSON por entidade. Digamos que cada usuario tem um conjunto de pares chave-valor em uma tabela user_settings(user_id, key, value). Monte o perfil de configuracoes de cada usuario em uma unica consulta:

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

A origem das chaves e dos valores nao precisa ser uma tabela de configuracoes pronta. Voce pode agregar quaisquer duas colunas, por exemplo para construir um mapa "status do pedido -> total daquele status" 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 a subconsulta garante exatamente uma linha por par (user_id, status), ou seja, exatamente uma chave no objeto.

Comportamento com chaves duplicadas

Esta e a armadilha principal. Se um grupo tem dois valores sob a mesma chave, JSONB_OBJECT_AGG nao falha e nao os mescla; ele simplesmente mantem um, e qual depende da ordem de processamento das linhas, sendo praticamente imprevisivel.

-- 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);

Pegadinha: nao confie em um silencioso "o ultimo vence". Se, por exemplo, o registro mais recente deve vencer, faca a deduplicacao explicita com DISTINCT ON ou uma funcao de janela 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;

Assim e voce quem decide qual linha vence diante de uma chave duplicada, em vez de deixar isso para o planejador.

JSONB_OBJECT_AGG versus JSONB_AGG de objetos

Uma confusao comum: JSONB_AGG tambem constroi JSON, mas um array, nao um objeto. A diferenca esta na forma do resultado e em como voce o percorre depois.

-- 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 escolher:

• Precisa de busca por nome (prefs->>'theme') e chaves unicas? Use JSONB_OBJECT_AGG. E um dicionario.
• Precisa de uma lista ordenada, elementos repetidos ou varios campos por registro? Use JSONB_AGG. E uma lista.
• JSONB_AGG aceita um ORDER BY dentro do agregado; para um objeto a ordem das chaves nao faz sentido de qualquer forma.

Notas sobre outros bancos

No MySQL o equivalente e JSON_OBJECTAGG(key, value), que funciona de forma parecida, mas o comportamento com chaves duplicadas varia conforme a versao e o modo, entao deduplique antes assim como no PostgreSQL. O ClickHouse nao tem um agregado direto: voce constroi um mapa com map() ou um groupArray de pares e converte, e o tipo Map substitui o objeto JSON. Quando voce realmente precisa de um documento de busca compacto por grupo, JSONB_OBJECT_AGG no PostgreSQL continua sendo a ferramenta mais direta.