JSON_AGG e JSONB_AGG no PostgreSQL: montar um array JSON para a resposta de API

JSON_AGG e JSONB_AGG sao funcoes de agregacao que dobram as linhas de um grupo em um unico array JSON. Elas transformam "muitas linhas por chave" em "uma linha com JSON", e sao a forma mais direta de retornar uma resposta de API aninhada direto do banco, sem montar o JSON no codigo da aplicacao.

JSON_AGG versus JSONB_AGG

Ambas reunem valores em um array JSON, mas o armazenam de maneiras diferentes:

• JSON_AGG retorna o tipo json: texto "como esta", preservando a ordem das chaves e as duplicatas.
• JSONB_AGG retorna jsonb: uma forma binaria onde chaves duplicadas se fundem (a ultima vence), os espacos sao normalizados e o acesso aos campos e mais rapido.
• Para a maioria das respostas de API, prefira JSONB_AGG: e mais compacto e mais facil de filtrar ou indexar adiante.

SELECT JSONB_AGG(t ORDER BY t.id) AS tasks
FROM tasks t;

Ao passar a linha inteira t para o agregado, voce obtem um array de objetos cujas chaves sao os nomes das colunas. E a maneira mais rapida de "entregar uma tabela como JSON".

ORDER BY dentro do agregado

A ordem dos elementos no array nao e garantida sem um ORDER BY dentro da propria chamada. Nunca confie na ordem das linhas da tabela; defina a ordenacao 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;

O ORDER BY vive dentro dos parenteses do agregado, nao na consulta externa. Um ORDER BY externo ordena as linhas do resultado, mas nao os elementos dentro de cada array JSON, uma confusao comum.

Um objeto por linha com jsonb_build_object

Um array de escalares puro raramente e o que voce quer; o normal e construir um array de objetos. jsonb_build_object monta um objeto JSON a partir de pares chave-valor, e JSONB_AGG dobra esses objetos em um 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;

Assim voce controla por completo os nomes dos campos na resposta: eles nao precisam coincidir com os nomes das colunas. Isso e util quando o esquema do banco e o contrato da API divergem.

Uma resposta de API aninhada em uma consulta

Essas pecas se combinam em um documento completo de "usuario com a lista de pedidos" em uma unica passada, sem consultas N+1 e sem montagem na aplicacao:

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 uma hierarquia de funcionarios, o mesmo truque reune cada gerente junto com seus 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 ignorar os NULL

JSONB_AGG inclui os valores NULL no array: uma linha sem correspondencia em um LEFT JOIN produz um elemento null. Para evitar isso, adicione um 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;

Pegadinha: se um usuario nao tem pedidos, JSONB_AGG(...) FILTER (...) retorna NULL, nao um array vazio []. Muitos clientes de API rejeitam null onde esperam um array. Envolva em COALESCE(JSONB_AGG(...) FILTER (...), '[]'::jsonb) para sempre emitir um array.

MySQL e ClickHouse

No MySQL 8 o equivalente e JSON_ARRAYAGG, e JSON_OBJECT constroi o objeto por linha:

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

O que ter em mente sobre o MySQL:

• JSON_ARRAYAGG nao tem um ORDER BY interno; imponha a ordem por meio de uma subconsulta ja ordenada.
• Nao ha separacao entre json e jsonb: o tipo e sempre JSON, ja armazenado em forma binaria normalizada.

O ClickHouse nao tem um equivalente direto; voce monta com groupArray e serializa, ou usa suas funcoes de strings JSON. Quando voce precisa de um JSON aninhado previsivel com ordenacao e filtragem, o PostgreSQL com JSONB_AGG ... ORDER BY ... FILTER continua sendo a ferramenta mais direta.