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

jsonb_build_object собирает JSON-объект прямо в запросе из чередующихся аргументов вида key, value, key, value, ...: нечётные позиции — ключи, чётные — значения. Берут её там, где нужно отдать готовый API-ответ или вложенную структуру одним SELECT, не собирая JSON вручную из строк в коде приложения.

Главное её свойство — сохранение типов. В отличие от ручной конкатенации, где всё стекается в текст, здесь число останется числом, булево — булевым, а NULL превратится в честный JSON null, а не в строку "null". Поэтому на фронт не прилетит число в кавычках, а кавычки и экранирование ключей и строковых значений PostgreSQL берёт на себя.

Тип результата — именно jsonb: дубли ключей схлопываются до последнего, ключи нормализуются, лишние пробелы убираются. У близнеца json_build_object тип json — это просто текст, который хранит ввод как есть, со всеми повторами ключей и исходным форматированием. Дальше разбираем синтаксис, сборку API-ответа по строке, вложенность и выбор между jsonb_build_object и to_jsonb.

Синтаксис и базовый пример

Аргументы идут парами: на нечётных позициях стоят ключи (их PostgreSQL приводит к тексту), на чётных — значения любого типа. На выходе — jsonb; у близнеца json_build_object тип другой, json — это просто текст без нормализации, без дедупликации ключей и без переупорядочивания.

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

Несколько деталей, на которых легко споткнуться:

• Аргументов должно быть чётное число, иначе прилетит ошибка argument list must have even number of elements.
• Типы значений сохраняются: amount numeric уедет в JSON числом, а не строкой в кавычках.
• NULL в значении даёт JSON null, но ключ при этом из объекта никуда не девается.
• А вот NULL в позиции ключа под запретом — будет ошибка. Ключи обязаны быть заданы всегда.

API-ответ по строке

Главный сценарий, ради которого всё затевалось, — собирать полезную нагрузку для API прямо в базе, не размазывая имена полей по коду приложения. Контракт ответа живёт в одном месте, в запросе, а не дублируется в десяти DTO. Соберём «карточку» пользователя сразу с парой производных полей:

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;

Обратите внимание на is_verified: в JSON это настоящий булев тип, а не строка "true", которую потом пришлось бы разбирать на клиенте. Вот оно, ключевое преимущество перед конкатенацией — типы не плывут, а кавычки и экранирование PostgreSQL берёт на себя.

Грабли: ключи объекта не дедуплицируются на этапе сборки в json_build_object (тип json) — туда уедут оба повтора. А jsonb поступает иначе: он хранит только последнее значение повторяющегося ключа, а ранние молча выбрасывает. Зададите один ключ дважды — в jsonb-результате останется лишь финальная пара, и какая именно, решает порядок аргументов. Поведение тихое, без предупреждений, так что дубль ключа легко не заметить.

Вложенность: объекты внутри объектов и массивы

jsonb_build_object свободно вкладывается и сама в себя, и в jsonb_build_array, так что структуру любой глубины собрать не проблема — скажем, заказ с вложенным блоком о покупателе и списком флагов:

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;

Когда же нужен массив дочерних объектов — например, все заказы одного пользователя в одном поле, — jsonb_build_object идёт в связке с агрегатом jsonb_agg. Внутри агрегата удобно сразу задать порядок через ORDER BY, чтобы массив пришёл предсказуемым:

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;

Когда брать to_jsonb вместо build_object

Если в ответ нужны все колонки строки без переименований, городить длинный список пар незачем — проще to_jsonb(row). Она сама раскладывает строку по ключам, имена которых равны именам столбцов:

SELECT to_jsonb(e) AS employee_json
FROM employees e;

Выбор между ними, по сути, сводится к одному вопросу — нужен ли вам контроль над набором ключей:

• to_jsonb(t) — быстрый дамп строки целиком; ключи равны именам колонок, а выбрать или переименовать поля нельзя.
• jsonb_build_object(...) — полный контроль над набором ключей, их именами и порядком; то, что нужно для стабильного контракта API.
• Гибрид: to_jsonb(e) - 'salary' уберёт лишнее поле, а to_jsonb(e) || jsonb_build_object('bonus', e.salary * 0.1) допишет вычисляемое.

Именно гибрид на практике экономит больше всего нервов: берёте всю строку через to_jsonb, оператором - вырезаете чувствительные колонки, а оператором конкатенации || дописываете производные поля. Контракт ответа при этом остаётся стабильным даже после ALTER TABLE — новая колонка не утечёт наружу сама собой, потому что в выборку её никто не добавлял.

MySQL и ClickHouse

В MySQL есть JSON_OBJECT('id', id, 'name', name) ровно с той же логикой чередующихся пар, и типы значений там тоже сохраняются. Прямого аналога to_jsonb(row) нет, так что ключи приходится перечислять руками, а для массивов объектов берут JSON_ARRAYAGG(JSON_OBJECT(...)).

В ClickHouse исторически работали со строковым JSON — через функции вроде toJSONString поверх кортежей и именованных tuple. Подход рабочий, но как только нужен строгий контракт ответа с типами и вложенностью, PostgreSQL с парой jsonb_build_object плюс jsonb_agg остаётся самым прямым и предсказуемым инструментом: один SELECT — и наружу уходит ровно та структура, которую вы описали, без сюрпризов.