jsonb_build_object monta um objeto JSON dentro da propria consulta a partir de argumentos alternados chave, valor, chave, valor, .... Ao contrario da concatenacao manual de strings, ela preserva os tipos dos valores: um numero continua numero, um booleano continua booleano e NULL vira um null JSON de verdade. E a ferramenta de trabalho para devolver um payload de API pronto ou uma estrutura aninhada em um unico SELECT.
Sintaxe e exemplo basico
Os argumentos vem em pares: as posicoes impares sao chaves (convertidas para texto) e as pares sao valores de qualquer tipo. O resultado e jsonb; o irmao json_build_object devolve json (texto sem normalizacao).
SELECT jsonb_build_object(
'id', id,
'email', email,
'country', country
) AS payload
FROM users;
Alguns comportamentos que vale conhecer:
- O numero de argumentos deve ser par, ou ocorre o erro
argument list must have even number of elements.
- Os tipos dos valores sao preservados:
amount numeric vira um numero JSON, nao uma string entre aspas.
- Um valor
NULL produz um null JSON, e a chave permanece no objeto.
- Um
NULL em posicao de chave e rejeitado com erro; as chaves sempre precisam estar presentes.
Um payload de API por linha
O caso principal e modelar um payload de API dentro do banco sem duplicar nomes de campos no codigo da aplicacao. Montar um "cartao" de usuario com alguns campos derivados e natural:
SELECT jsonb_build_object(
'user_id', u.id,
'name', u.name,
'is_verified', (u.email IS NOT NULL),
'signup_year', EXTRACT(YEAR FROM u.created_at)
) AS user_card
FROM users u;
Aqui is_verified e um booleano JSON real, nao a string "true". Essa e a vantagem chave sobre a concatenacao: os tipos sobrevivem e o PostgreSQL cuida das aspas e do escape por voce.
Pegadinha: as chaves nao sao deduplicadas na construcao em json_build_object (o tipo json), mas o jsonb mantem o ultimo valor de uma chave repetida e descarta o resto. Se voce informar uma chave duas vezes, o resultado jsonb guarda apenas o par final, entao a ordem dos argumentos decide qual vence.
Aninhamento: objetos dentro de objetos e arrays
jsonb_build_object se aninha livremente dentro de si mesmo e dentro de jsonb_build_array, entao voce pode construir estruturas de qualquer profundidade, como um pedido com um bloco de cliente aninhado e uma lista de tags:
SELECT jsonb_build_object(
'order_id', o.id,
'amount', o.amount,
'status', o.status,
'customer', jsonb_build_object(
'id', u.id,
'email', u.email
),
'flags', jsonb_build_array('priority', o.status)
) AS order_json
FROM orders o
JOIN users u ON u.id = o.user_id;
Para construir um array de objetos filhos (todos os pedidos de um usuario), combine jsonb_build_object com o agregado jsonb_agg:
SELECT jsonb_build_object(
'user_id', u.id,
'orders', jsonb_agg(
jsonb_build_object('id', o.id, 'amount', o.amount)
ORDER BY o.created_at
)
) AS user_with_orders
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id;
Quando usar to_jsonb em vez disso
Quando voce quer JSON com todas as colunas de uma linha e sem renomear, to_jsonb(row) e mais simples: ele mapeia a linha pelo nome da coluna automaticamente.
SELECT to_jsonb(e) AS employee_json
FROM employees e;
Escolher entre os dois e direto:
to_jsonb(t) e um dump rapido da linha inteira; as chaves sao os nomes das colunas e voce nao pode escolher nem renomear campos.jsonb_build_object(...) da controle total sobre o conjunto de chaves, os nomes e a ordem, ideal para um contrato de API estavel.- Hibrido:
to_jsonb(e) - 'salary' remove um campo, e to_jsonb(e) || jsonb_build_object('bonus', e.salary * 0.1) adiciona um calculado.
MySQL e ClickHouse
O MySQL oferece JSON_OBJECT('id', id, 'name', name) com a mesma logica de pares alternados, e os tipos dos valores tambem sao preservados. Nao ha um equivalente direto de to_jsonb(row), entao voce lista as chaves na mao. Para arrays de objetos, use JSON_ARRAYAGG(JSON_OBJECT(...)).
O ClickHouse trabalhou historicamente com JSON em string por meio de funcoes como toJSONString sobre tuplas e tuples nomeadas. Quando voce precisa de um contrato de resposta estrito com tipos e aninhamento, o PostgreSQL com jsonb_build_object mais jsonb_agg continua sendo a ferramenta mais direta e previsivel.
jsonb_build_objectmonta um objeto JSON dentro da propria consulta a partir de argumentos alternadoschave, valor, chave, valor, .... Ao contrario da concatenacao manual de strings, ela preserva os tipos dos valores: um numero continua numero, um booleano continua booleano eNULLvira umnullJSON de verdade. E a ferramenta de trabalho para devolver um payload de API pronto ou uma estrutura aninhada em um unico SELECT.Sintaxe e exemplo basico
Os argumentos vem em pares: as posicoes impares sao chaves (convertidas para texto) e as pares sao valores de qualquer tipo. O resultado e
jsonb; o irmaojson_build_objectdevolvejson(texto sem normalizacao).SELECT jsonb_build_object( 'id', id, 'email', email, 'country', country ) AS payload FROM users;Alguns comportamentos que vale conhecer:
argument list must have even number of elements.amount numericvira um numero JSON, nao uma string entre aspas.NULLproduz umnullJSON, e a chave permanece no objeto.NULLem posicao de chave e rejeitado com erro; as chaves sempre precisam estar presentes.Um payload de API por linha
O caso principal e modelar um payload de API dentro do banco sem duplicar nomes de campos no codigo da aplicacao. Montar um "cartao" de usuario com alguns campos derivados e natural:
SELECT jsonb_build_object( 'user_id', u.id, 'name', u.name, 'is_verified', (u.email IS NOT NULL), 'signup_year', EXTRACT(YEAR FROM u.created_at) ) AS user_card FROM users u;Aqui
is_verifiede um booleano JSON real, nao a string"true". Essa e a vantagem chave sobre a concatenacao: os tipos sobrevivem e o PostgreSQL cuida das aspas e do escape por voce.Aninhamento: objetos dentro de objetos e arrays
jsonb_build_objectse aninha livremente dentro de si mesmo e dentro dejsonb_build_array, entao voce pode construir estruturas de qualquer profundidade, como um pedido com um bloco de cliente aninhado e uma lista de tags:SELECT jsonb_build_object( 'order_id', o.id, 'amount', o.amount, 'status', o.status, 'customer', jsonb_build_object( 'id', u.id, 'email', u.email ), 'flags', jsonb_build_array('priority', o.status) ) AS order_json FROM orders o JOIN users u ON u.id = o.user_id;Para construir um array de objetos filhos (todos os pedidos de um usuario), combine
jsonb_build_objectcom o agregadojsonb_agg:SELECT jsonb_build_object( 'user_id', u.id, 'orders', jsonb_agg( jsonb_build_object('id', o.id, 'amount', o.amount) ORDER BY o.created_at ) ) AS user_with_orders FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.id;Quando usar to_jsonb em vez disso
Quando voce quer JSON com todas as colunas de uma linha e sem renomear,
to_jsonb(row)e mais simples: ele mapeia a linha pelo nome da coluna automaticamente.SELECT to_jsonb(e) AS employee_json FROM employees e;Escolher entre os dois e direto:
to_jsonb(t)e um dump rapido da linha inteira; as chaves sao os nomes das colunas e voce nao pode escolher nem renomear campos.jsonb_build_object(...)da controle total sobre o conjunto de chaves, os nomes e a ordem, ideal para um contrato de API estavel.to_jsonb(e) - 'salary'remove um campo, eto_jsonb(e) || jsonb_build_object('bonus', e.salary * 0.1)adiciona um calculado.MySQL e ClickHouse
O MySQL oferece
JSON_OBJECT('id', id, 'name', name)com a mesma logica de pares alternados, e os tipos dos valores tambem sao preservados. Nao ha um equivalente direto deto_jsonb(row), entao voce lista as chaves na mao. Para arrays de objetos, useJSON_ARRAYAGG(JSON_OBJECT(...)).O ClickHouse trabalhou historicamente com JSON em string por meio de funcoes como
toJSONStringsobre tuplas etuples nomeadas. Quando voce precisa de um contrato de resposta estrito com tipos e aninhamento, o PostgreSQL comjsonb_build_objectmaisjsonb_aggcontinua sendo a ferramenta mais direta e previsivel.