JSONB_BUILD_OBJECT in PostgreSQL: Build a JSON Object from Columns with Typed Values

jsonb_build_object assembles a JSON object right inside the query from alternating key, value, key, value, ... arguments. Unlike hand-built string concatenation, it preserves value types: a number stays a number, a boolean stays boolean, and NULL becomes a real JSON null. It is the workhorse for returning a ready API payload or a nested structure from a single SELECT.

Syntax and a basic example

Arguments come in pairs: odd positions are keys (cast to text), even positions are values of any type. The result is jsonb; the sibling json_build_object returns json (text with no normalization).

SELECT jsonb_build_object(
    'id', id,
    'email', email,
    'country', country
) AS payload
FROM users;

A few behaviors worth knowing:

• The argument count must be even, or you get argument list must have even number of elements.
• Value types are preserved: amount numeric becomes a JSON number, not a quoted string.
• A NULL value yields JSON null, and the key stays in the object.
• A NULL in a key position is rejected with an error; keys must always be present.

A per-row API payload

The headline use case is shaping an API payload inside the database without duplicating field names in application code. Building a user "card" with a couple of derived fields is 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;

Here is_verified is a true JSON boolean, not the string "true". That is the key advantage over concatenation: types survive, and PostgreSQL handles quoting and escaping for you.

Gotcha: keys are not deduplicated at build time in json_build_object (the json type), but jsonb keeps the last value for a repeated key and drops the rest. Specify one key twice and the jsonb result keeps only the final pair, so argument order decides which one wins.

Nesting: objects inside objects and arrays

jsonb_build_object nests freely inside itself and inside jsonb_build_array, so you can build structures of any depth, like an order with a nested customer block and a list of 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;

To build an array of child objects (all of a user's orders), combine jsonb_build_object with the jsonb_agg aggregate:

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;

When to reach for to_jsonb instead

When you want JSON with every column of a row and no renaming, to_jsonb(row) is simpler: it maps the row by column name automatically.

SELECT to_jsonb(e) AS employee_json
FROM employees e;

Choosing between them is straightforward:

• to_jsonb(t) is a fast whole-row dump; keys equal column names, and you cannot pick or rename fields.
• jsonb_build_object(...) gives full control over the key set, names, and order, which is ideal for a stable API contract.
• Hybrid: to_jsonb(e) - 'salary' strips a field, while to_jsonb(e) || jsonb_build_object('bonus', e.salary * 0.1) appends a computed one.

MySQL and ClickHouse

MySQL offers JSON_OBJECT('id', id, 'name', name) with the same alternating-pair logic, and value types are preserved too. There is no direct to_jsonb(row) equivalent, so you list keys by hand. For arrays of objects, use JSON_ARRAYAGG(JSON_OBJECT(...)).

ClickHouse historically worked with string JSON through functions like toJSONString over tuples and named tuples. When you need a strict response contract with types and nesting, PostgreSQL with jsonb_build_object plus jsonb_agg stays the most direct and predictable tool.