jsonb_build_array no PostgreSQL: montando arrays JSON com tipos mistos

jsonb_build_array e uma funcao do PostgreSQL que monta um array JSON a partir dos argumentos que voce listar, e cada argumento pode ser de um tipo diferente. Ao contrario de um array SQL comum, onde todos os elementos precisam ter o mesmo tipo, aqui numeros, strings, datas, booleanos e objetos aninhados convivem lado a lado.

Sintaxe basica

Voce passa qualquer numero de argumentos e a funcao retorna um valor jsonb preservando a ordem dos elementos.

SELECT jsonb_build_array(1, 'two', true, NULL);
-- [1, "two", true, null]

A diferenca central em relacao a um array do PostgreSQL e a heterogeneidade. A chamada abaixo e impossivel com ARRAY[...] porque os elementos tem tipos diferentes:

SELECT jsonb_build_array(id, name, salary, dept)
FROM employees
WHERE id = 42;
-- [42, "Ada", 95000.00, "engineering"]

Cada argumento passa pela conversao usual para JSON: numeros continuam numeros, texto vira string, boolean vira true/false e NULL vira JSON null. Uma data ou timestamp se transforma em uma string no formato ISO.

Tuplas e linhas de tabela como JSON

jsonb_build_array se destaca quando voce quer uma "tupla" de forma fixa: um array posicional em vez de um objeto com chaves. E mais compacto que um objeto e combina com dados tabulares onde o cliente ja conhece a ordem das colunas.

SELECT jsonb_agg(
         jsonb_build_array(id, email, country, created_at)
       ) AS rows
FROM users
WHERE country = 'BR';
-- [[1,"a@x.io","BR","2024-01-10T00:00:00"], [2,"b@x.io","BR", ...]]

Aqui jsonb_build_array forma uma tupla por linha, e jsonb_agg dobra todas as linhas em um array de arrays: um dump compacto do resultado.

Aninhamento com jsonb_build_object

O poder de verdade aparece ao combinar com jsonb_build_object. Um array pode conter objetos, e objetos podem conter arrays; o aninhamento nao tem limite fixo.

SELECT jsonb_build_object(
         'user_id', u.id,
         'recent_orders',
         (SELECT jsonb_agg(
                   jsonb_build_array(o.id, o.amount, o.status)
                 )
          FROM orders o
          WHERE o.user_id = u.id)
       ) AS payload
FROM users u
WHERE u.id = 7;
-- {"user_id": 7, "recent_orders": [[100, 49.90, "paid"], [101, 12.00, "pending"]]}

O array interno e uma tupla posicional de pedido, e o objeto externo da chaves legiveis a ela. E assim que se constroem respostas de API sem uma etapa intermediaria de montagem na aplicacao.

A diferenca em relacao a to_jsonb de um array

Uma confusao comum e jsonb_build_array(a, b, c) contra to_jsonb(ARRAY[a, b, c]). Os resultados parecem semelhantes a primeira vista, mas a semantica e diferente.

• jsonb_build_array aceita argumentos de tipos mistos e preserva seus tipos JSON nativos.
• to_jsonb de um array SQL exige que o array seja de um unico tipo e o converte como um todo.

-- Funciona: tipos mistos
SELECT jsonb_build_array(1, 'x', true);   -- [1, "x", true]

-- Erro: ARRAY precisa de um tipo comum
SELECT to_jsonb(ARRAY[1, 'x', true]);     -- ERROR: cannot mix types

-- Funciona: array de um unico tipo
SELECT to_jsonb(ARRAY[1, 2, 3]);          -- [1, 2, 3]

Pegadinha: o PostgreSQL vai tentar forcar os literais de ARRAY[1, 'x'] a um tipo comum e falhar com um erro de incompatibilidade de tipos. Quando voce realmente precisa de um conjunto de valores de tipos diferentes, use jsonb_build_array e reserve to_jsonb para um array ja homogeneo ou para uma linha de tabela (to_jsonb(t.*)).

Mais um detalhe com NULL: em jsonb_build_array, um NULL do SQL vira JSON null e permanece no array em vez de ser descartado. Se precisar remover valores vazios, filtre-os antes em uma subconsulta.

Outros bancos de dados

• MySQL usa JSON_ARRAY(...), um equivalente direto com a mesma ideia de argumentos mistos. Para objetos existe JSON_OBJECT(...).
• ClickHouse trata JSON de forma diferente historicamente: ha um tipo JSON e funcoes como toJSONString, mas nao um construtor posicional "no estilo Postgres"; as tuplas costumam ser montadas com tuple() e depois serializadas.

Se o seu codigo migra entre engines, mantenha a camada de formatacao JSON em um unico lugar: a sintaxe varia o suficiente para compensar.