JSONB_AGG no PostgreSQL: agrupar linhas em um array JSON para suas JSON APIs

jsonb_agg e uma funcao de agregacao que reune os valores de todas as linhas de um grupo em um unico array JSON. Enquanto array_agg produz um array de um unico tipo, jsonb_agg aceita qualquer coisa, inclusive linhas inteiras de uma tabela, e por isso e o bloco base das JSON APIs: uma consulta e voce tem um documento com colecoes aninhadas.

Sintaxe e exemplo basico

A assinatura coincide com qualquer agregado: jsonb_agg(item ORDER BY created_at), geralmente ao lado de um GROUP BY. O item costuma ser um row_to_json(...) ou um jsonb_build_object(...) para que cada elemento do array seja um objeto, e nao um escalar solto.

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

Alguns comportamentos que vale conhecer:

• A ordem dos elementos nao e garantida sem um ORDER BY dentro do agregado. Nunca confie na ordem das linhas da tabela.
• O tipo do resultado e sempre jsonb, independentemente do tipo da expressao interna.
• Linhas NULL entram no array como JSON null. Para descarta-las, adicione FILTER (WHERE expr IS NOT NULL).
• Um grupo vazio produz NULL, nao um array vazio []. Essa e a armadilha principal, vista abaixo.

Um array de objetos com ORDER BY e FILTER

Escalares sao o aquecimento. Na pratica voce coloca objetos no array: para cada usuario, a lista dos seus pedidos, ordenada e filtrada dentro da propria chamada.

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 da controle total sobre os nomes das chaves, enquanto to_jsonb(o) pega a linha inteira com todas as colunas. Escolha conforme precise ou nao de um contrato de resposta rigido.

Um documento pai com um array aninhado

O padrao mais comum das JSON APIs e um pai com uma colecao aninhada de filhos. Voce monta isso com uma subconsulta correlacionada: a consulta externa percorre os usuarios e a interna agrega os pedidos de cada um com 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;

Uma unica passada e cada linha do resultado esta pronta para enviar ao cliente. Sem N+1 na aplicacao, sem montar a arvore no codigo.

O grupo vazio: COALESCE para '[]'

Aqui esta a armadilha. Se um usuario nao tem pedidos, a subconsulta retorna NULL, nao [], e um frontend que espera um array quebra. Envolva em 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;

Pegadinha: FILTER tambem pode transformar um grupo nao vazio em NULL. Se nenhuma linha passar no filtro, jsonb_agg(...) FILTER (...) retorna NULL, nao []. Por isso COALESCE(..., '[]'::jsonb) e necessario sempre que houver um FILTER, mesmo quando o grupo certamente tem linhas.

A mesma regra vale para uma hierarquia: reuna em um array os subordinados de cada gerente e garanta [] para os que nao tem nenhum.

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 e ClickHouse

No MySQL 8 o equivalente e JSON_ARRAYAGG, acompanhado de JSON_OBJECT para os 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;

Diferencas que vale lembrar:

• JSON_ARRAYAGG nao tem um ORDER BY interno; imponha a ordem com uma subconsulta ja ordenada.
• Tambem nao ha FILTER; filtre com uma clausula WHERE ou uma expressao condicional interna.
• No ClickHouse um resultado parecido vem de groupArray sobre um tuple, ou combinando com toJSONString.

Quando voce precisa de um documento JSON previsivel com colecoes aninhadas e um [] garantido em vez de NULL, o PostgreSQL com jsonb_agg ... ORDER BY ... FILTER mais COALESCE continua sendo a ferramenta mais direta.