jsonb_build_object в PostgreSQL: как собрать JSON прямо в SQL-запросе

jsonb_build_object — это функция PostgreSQL, которая собирает JSON-объект прямо внутри запроса.

Она принимает аргументы парами:

key, value, key, value, key, value

На нечётных позициях стоят ключи, на чётных — значения.

Например:

SELECT jsonb_build_object(
    'id', 1,
    'email', 'ann@example.com',
    'is_active', true
) AS payload;

Результат:

{"id": 1, "email": "ann@example.com", "is_active": true}

Эта функция особенно полезна, когда нужно отдать из базы готовый JSON для API, отчёта, webhook-события или вложенной структуры. Вместо того чтобы собирать JSON строками в приложении, можно описать нужный ответ прямо в SELECT.

Главное преимущество: PostgreSQL сам правильно сохраняет типы, расставляет кавычки, экранирует строки и превращает SQL-значения в корректный JSON.

Зачем нужен jsonb_build_object

Представим, что у нас есть таблица пользователей.

id | email           | country | is_active
---+-----------------+---------+----------
1  | ann@example.com | DE      | true
2  | bob@example.com | BR      | false

Мы хотим получить не обычные колонки, а JSON-объект для каждой строки.

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

Результат будет примерно таким:

{"id": 1, "email": "ann@example.com", "country": "DE", "is_active": true}
{"id": 2, "email": "bob@example.com", "country": "BR", "is_active": false}

Обратите внимание: id остался числом, is_active остался булевым значением, а строки получили кавычки.

Если бы мы собирали JSON руками через конкатенацию строк, пришлось бы самостоятельно следить за кавычками, экранированием и типами. Это неудобно и опасно.

Плохой стиль выглядит примерно так:

SELECT
    '{"id": "' || id || '", "email": "' || email || '"}' AS payload
FROM users;

На первый взгляд работает. Но здесь id уже стал строкой, потому что попал в кавычки. Если в email окажется кавычка или другой специальный символ, JSON может сломаться. А если значение будет NULL, результат тоже может стать неожиданным.

С jsonb_build_object PostgreSQL делает эту работу за вас.

Базовый синтаксис

Форма функции простая:

jsonb_build_object(key, value, key, value, ...)

Пример:

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

Здесь:

• 'user_id', 'email', 'country' — ключи будущего JSON-объекта;
• u.id, u.email, u.country — значения;
• результат имеет тип jsonb.

Ключи PostgreSQL приводит к тексту. Значения могут быть почти любого типа: числа, строки, даты, булевы значения, NULL, массивы, другие JSON-объекты.

Аргументов должно быть чётное число

У jsonb_build_object каждый ключ должен иметь значение. Поэтому число аргументов обязательно должно быть чётным.

Так правильно:

SELECT jsonb_build_object(
    'id', 1,
    'name', 'Ann'
) AS payload;

А так неправильно:

SELECT jsonb_build_object(
    'id', 1,
    'name'
) AS payload;

Во втором запросе для ключа 'name' нет значения. PostgreSQL выдаст ошибку.

Логика простая: функция читает аргументы парами. Если пара не закончилась, объект собрать невозможно.

Значения сохраняют типы

Одна из главных причин использовать jsonb_build_object — сохранение типов.

SELECT jsonb_build_object(
    'id', 10,
    'amount', 1499.50,
    'is_paid', true,
    'comment', NULL
) AS payload;

Результат:

{"id": 10, "amount": 1499.50, "is_paid": true, "comment": null}

Здесь важно:

• 10 — число, а не строка "10";
• 1499.50 — число, а не строка "1499.50";
• true — JSON-булево значение, а не строка "true";
• SQL-значение NULL превратилось в JSON null.

Это удобно для фронтенда и внешних API. Клиенту не нужно гадать, почему цена пришла строкой, а флаг активности пришёл текстом.

Что происходит с NULL

Если NULL стоит на месте значения, ключ остаётся в объекте, а значение становится JSON null.

SELECT jsonb_build_object(
    'id', 1,
    'middle_name', NULL
) AS payload;

Результат:

{"id": 1, "middle_name": null}

Это нормальное поведение. Ключ не исчезает.

Если вы хотите убрать поля со значением null, можно дополнительно использовать jsonb_strip_nulls.

SELECT jsonb_strip_nulls(
    jsonb_build_object(
        'id', 1,
        'middle_name', NULL
    )
) AS payload;

Результат:

{"id": 1}

А вот NULL на месте ключа использовать нельзя.

SELECT jsonb_build_object(
    NULL, 'Ann'
) AS payload;

Ключ JSON-объекта должен существовать. Поэтому такой запрос завершится ошибкой.

Пример: карточка пользователя для API

Самый частый сценарий — собрать ответ для API прямо в запросе.

Допустим, нам нужна карточка пользователя:

{
  "user_id": 1,
  "name": "Ann",
  "is_verified": true,
  "signup_year": 2024
}

Соберём её в SQL:

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;

Здесь внутри JSON есть не только значения из колонок, но и вычисляемые поля.

is_verified считается выражением:

u.email IS NOT NULL

Если email есть, в JSON попадёт true. Если email нет, попадёт false.

signup_year берётся из даты регистрации:

EXTRACT(YEAR FROM u.created_at)

Так можно собрать аккуратный контракт ответа прямо в запросе: переименовать ключи, добавить вычисляемые поля, убрать лишние технические колонки.

Почему это лучше ручной сборки строк

Иногда новичок думает: «JSON — это же просто текст, значит, можно склеить его руками».

Например:

SELECT
    '{"id": ' || id || ', "email": "' || email || '"}' AS payload
FROM users;

Такой подход быстро ломается.

Если в email попадёт кавычка, слэш или другой специальный символ, JSON может стать некорректным. Если значение окажется NULL, вся строка может стать NULL. Если число случайно взяли в кавычки, клиент получит строку вместо числа.

jsonb_build_object решает эти проблемы:

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

PostgreSQL сам понимает, где строка, где число, где NULL, где булево значение. Поэтому JSON получается корректным без ручной возни с кавычками.

jsonb_build_object и повторяющиеся ключи

У jsonb_build_object результат имеет тип jsonb.

Для jsonb повторяющиеся ключи не хранятся. Если один и тот же ключ указан несколько раз, останется последнее значение.

SELECT jsonb_build_object(
    'status', 'new',
    'status', 'paid'
) AS payload;

Результат:

{"status": "paid"}

Это тихое поведение: PostgreSQL не выдаст предупреждение и не скажет, что ключ повторился.

Поэтому в больших объектах важно внимательно следить за именами ключей. Дубли легко не заметить, особенно когда объект собирается из многих полей.

Есть похожая функция json_build_object. Она возвращает тип json, а не jsonb. В json повторы ключей могут сохраниться в текстовом представлении. Но для большинства рабочих задач в PostgreSQL чаще выбирают именно jsonb, потому что с ним удобнее искать, сравнивать, индексировать и использовать операторы JSONB.

Важная деталь про порядок ключей

В обычном JSON порядок ключей часто выглядит как порядок, в котором вы их написали. Но для jsonb на это полагаться нельзя.

jsonb хранит объект в нормализованном виде. Он убирает лишнее форматирование, схлопывает повторяющиеся ключи и не обязан сохранять исходный порядок ключей.

Поэтому не стоит строить логику приложения на том, что ключи в JSONB-объекте выйдут ровно в том порядке, в котором вы указали их в jsonb_build_object.

Для API это обычно не проблема: JSON-объект по смыслу является набором пар key-value, а не упорядоченным списком. Если порядок важен, обычно нужен массив.

Вложенные объекты

jsonb_build_object можно вкладывать внутрь другого jsonb_build_object.

Например, соберём заказ с вложенным объектом покупателя.

SELECT jsonb_build_object(
    'order_id', o.id,
    'amount', o.amount,
    'status', o.status,
    'customer', jsonb_build_object(
        'id', u.id,
        'email', u.email
    )
) AS order_json
FROM orders o
JOIN users u ON u.id = o.user_id;

Результат будет похож на такой:

{
  "order_id": 1001,
  "amount": 2500,
  "status": "paid",
  "customer": {
    "id": 1,
    "email": "ann@example.com"
  }
}

Такой запрос хорошо читается: верхний объект описывает заказ, а внутри ключа customer лежит отдельный объект с данными пользователя.

Массивы внутри объекта

Если нужно добавить массив, можно использовать jsonb_build_array.

SELECT jsonb_build_object(
    'order_id', o.id,
    'status', o.status,
    'flags', jsonb_build_array('priority', o.status)
) AS order_json
FROM orders o;

Результат:

{
  "order_id": 1001,
  "status": "paid",
  "flags": ["priority", "paid"]
}

jsonb_build_array похож на jsonb_build_object, но собирает не объект, а массив. Ему не нужны пары key-value: он просто кладёт переданные значения по порядку.

Массив дочерних объектов через jsonb_agg

Частый сценарий: нужно получить пользователя и все его заказы внутри одного JSON.

Например:

{
  "user_id": 1,
  "email": "ann@example.com",
  "orders": [
    {"id": 1001, "amount": 2500},
    {"id": 1002, "amount": 900}
  ]
}

Для этого jsonb_build_object удобно сочетать с агрегатом jsonb_agg.

SELECT jsonb_build_object(
    'user_id', u.id,
    'email', u.email,
    '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, u.email;

Здесь происходит несколько вещей:

1. Для каждого заказа собирается маленький JSON-объект.
2. jsonb_agg собирает эти объекты в массив.
3. Внешний jsonb_build_object кладёт этот массив в поле orders.

ORDER BY внутри jsonb_agg очень важен. Без него порядок элементов в массиве не стоит считать надёжным. Если клиенту важно получать заказы от старых к новым, порядок нужно задать явно.

Что делать, если у пользователя нет заказов

Если использовать обычный JOIN, пользователи без заказов не попадут в результат.

Чтобы оставить всех пользователей, нужен LEFT JOIN.

SELECT jsonb_build_object(
    'user_id', u.id,
    'email', u.email,
    'orders', COALESCE(
        jsonb_agg(
            jsonb_build_object(
                'id', o.id,
                'amount', o.amount
            )
            ORDER BY o.created_at
        ) FILTER (WHERE o.id IS NOT NULL),
        '[]'::jsonb
    )
) AS user_with_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

Здесь есть два важных приёма.

FILTER (WHERE o.id IS NOT NULL) не даёт собрать один пустой объект для пользователя без заказов.

COALESCE(..., '[]'::jsonb) превращает NULL в пустой массив.

В итоге пользователь без заказов получит:

{
  "user_id": 1,
  "email": "ann@example.com",
  "orders": []
}

А не null и не массив с пустым заказом.

Когда использовать to_jsonb

Если вам нужно превратить всю строку таблицы в JSON, можно использовать to_jsonb.

SELECT to_jsonb(e) AS employee_json
FROM employees e;

Если в таблице employees есть колонки id, name, department, результат будет примерно таким:

{"id": 1, "name": "Ann", "department": "QA"}

to_jsonb удобен, когда нужно быстро отдать строку целиком.

Но у него есть важная особенность: ключи берутся из имён колонок. Если вы пишете to_jsonb(e), в JSON попадут все колонки строки e.

Это удобно для внутренних задач, но опасно для публичного API. Если завтра в таблицу добавят колонку salary, internal_note или admin_comment, она тоже может попасть в JSON.

Поэтому для стабильного API-контракта чаще лучше использовать jsonb_build_object и явно перечислять нужные поля.

Гибридный подход: взять строку и убрать лишнее

Иногда удобно взять строку целиком, но убрать одно или несколько полей.

Для этого у jsonb есть оператор -.

SELECT to_jsonb(e) - 'salary' AS employee_json
FROM employees e;

Так из JSON убирается ключ salary.

Можно убрать несколько ключей:

SELECT to_jsonb(e) - ARRAY['salary', 'internal_note'] AS employee_json
FROM employees e;

А можно добавить вычисляемое поле через оператор ||.

SELECT
    (to_jsonb(e) - ARRAY['salary', 'internal_note'])
    || jsonb_build_object('bonus', e.salary * 0.1) AS employee_json
FROM employees e;

Здесь мы:

1. Превратили строку сотрудника в jsonb.
2. Убрали чувствительные поля.
3. Добавили вычисляемое поле bonus.

Но для внешнего API всё равно будьте осторожны. Если таблица меняется, to_jsonb(e) может начать включать новые колонки. Для стабильного публичного ответа безопаснее явно описывать объект через jsonb_build_object.

Как сделать стабильный JSON через подзапрос

Есть хороший компромисс: сначала выбрать только нужные поля в подзапросе, а потом применить to_jsonb.

SELECT to_jsonb(x) AS employee_json
FROM (
    SELECT
        e.id,
        e.name,
        e.department
    FROM employees e
) x;

Теперь в JSON попадут только id, name и department.

Даже если в таблицу employees позже добавят новые колонки, они не утекут в ответ, потому что подзапрос их не выбирает.

Это полезный приём, когда полей много и не хочется писать длинный jsonb_build_object, но при этом нужен контроль над тем, что уходит наружу.

Когда выбирать jsonb_build_object, а когда to_jsonb

Выбор зависит от задачи.

Если нужен точный API-контракт с понятными именами ключей, берите jsonb_build_object.

SELECT jsonb_build_object(
    'user_id', u.id,
    'email', u.email,
    'signup_year', EXTRACT(YEAR FROM u.created_at)
) AS payload
FROM users u;

Если нужно быстро превратить всю строку в JSON для внутренней задачи, можно взять to_jsonb.

SELECT to_jsonb(u) AS user_json
FROM users u;

Если нужно взять много полей, но не все, используйте подзапрос.

SELECT to_jsonb(x) AS user_json
FROM (
    SELECT
        u.id,
        u.email,
        u.country
    FROM users u
) x;

Если нужно собрать вложенную структуру, массивы, переименования и вычисляемые поля, удобнее всего jsonb_build_object вместе с jsonb_agg.

jsonb_build_object и даты

Если положить в JSON дату или timestamp, PostgreSQL сам преобразует значение в JSON-представление.

SELECT jsonb_build_object(
    'id', o.id,
    'created_at', o.created_at
) AS order_json
FROM orders o;

Результат может выглядеть так:

{"id": 1001, "created_at": "2024-03-15T14:30:00"}

В JSON нет отдельного типа для даты, поэтому дата обычно превращается в строку. Это нормально.

Если API требует конкретный формат даты, лучше задать его явно.

SELECT jsonb_build_object(
    'id', o.id,
    'created_date', to_char(o.created_at, 'YYYY-MM-DD')
) AS order_json
FROM orders o;

Так контракт становится предсказуемым: клиент всегда получает дату в нужном формате.

Практический пример: ответ с заказом

Соберём более жизненный пример: заказ, покупатель и список товаров.

Допустим, есть таблицы:

• orders — заказы;
• users — пользователи;
• order_items — товары в заказе.

Запрос:

SELECT jsonb_build_object(
    'order_id', o.id,
    'status', o.status,
    'amount', o.amount,
    'customer', jsonb_build_object(
        'id', u.id,
        'email', u.email
    ),
    'items', jsonb_agg(
        jsonb_build_object(
            'product_id', i.product_id,
            'title', i.title,
            'qty', i.qty,
            'price', i.price
        )
        ORDER BY i.id
    )
) AS order_payload
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items i ON i.order_id = o.id
WHERE o.id = 1001
GROUP BY o.id, o.status, o.amount, u.id, u.email;

Результат:

{
  "order_id": 1001,
  "status": "paid",
  "amount": 2500,
  "customer": {
    "id": 1,
    "email": "ann@example.com"
  },
  "items": [
    {
      "product_id": 501,
      "title": "SQL Course",
      "qty": 1,
      "price": 2000
    },
    {
      "product_id": 502,
      "title": "Interview Pack",
      "qty": 1,
      "price": 500
    }
  ]
}

Такой SQL уже похож на описание готового API-ответа. Видно, какие ключи уйдут наружу, где вложенный объект, где массив, какие поля вычисляются или переименовываются.

Отличие json от jsonb

В PostgreSQL есть два близких типа: json и jsonb.

Функция json_build_object возвращает json.

Функция jsonb_build_object возвращает jsonb.

На практике чаще используют jsonb, потому что он хранится в разобранном бинарном виде и поддерживает больше удобных операций: проверку ключей, удаление полей, конкатенацию, индексацию и поиск внутри структуры.

Но у jsonb есть особенности:

• порядок ключей не гарантируется;
• повторяющиеся ключи схлопываются;
• лишнее форматирование не сохраняется.

Для API-ответов это обычно нормально. Клиенту важны ключи и значения, а не пробелы и порядок ключей в объекте.

Если же вам по какой-то причине нужно сохранить текст JSON ближе к исходному виду, можно смотреть в сторону json. Но для большинства задач в PostgreSQL удобнее jsonb.

MySQL и ClickHouse

В MySQL похожая функция называется JSON_OBJECT.

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

Для массивов объектов в MySQL используют JSON_ARRAYAGG вместе с JSON_OBJECT.

SELECT JSON_OBJECT(
    'user_id', u.id,
    'orders', JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', o.id,
            'amount', o.amount
        )
    )
) AS payload
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

Прямого полного аналога to_jsonb(row) из PostgreSQL в MySQL нет, поэтому ключи обычно перечисляют руками.

В ClickHouse подход зависит от версии и структуры данных. Часто JSON собирают через функции сериализации вроде toJSONString поверх кортежей, именованных кортежей или других структур.

Но идея везде одна и та же: если нужен JSON-ответ, лучше использовать функции базы для сборки JSON, а не склеивать строку руками.

Частые ошибки

Первая ошибка — нечётное количество аргументов.

SELECT jsonb_build_object(
    'id', 1,
    'email'
) AS payload;

У ключа 'email' нет значения, поэтому объект не соберётся.

Вторая ошибка — повторяющиеся ключи.

SELECT jsonb_build_object(
    'id', 1,
    'id', 2
) AS payload;

В jsonb останется последнее значение:

{"id": 2}

Третья ошибка — ожидать, что NULL удалит ключ.

SELECT jsonb_build_object(
    'id', 1,
    'comment', NULL
) AS payload;

Результат будет с ключом comment:

{"id": 1, "comment": null}

Если ключи со значением null нужно убрать, используйте jsonb_strip_nulls.

Четвёртая ошибка — отдавать наружу to_jsonb(table_alias) без контроля. Если в таблицу добавят новую колонку, она может неожиданно попасть в ответ.

Пятая ошибка — рассчитывать на порядок ключей в jsonb. Для объекта порядок ключей не должен иметь значения. Если порядок важен, используйте массив.

Главное из статьи

jsonb_build_object собирает JSONB-объект из пар key, value.

Аргументов должно быть чётное число: каждому ключу нужно значение.

Значения сохраняют типы: числа остаются числами, булевы значения остаются булевыми, SQL NULL превращается в JSON null.

NULL в значении не удаляет ключ. Чтобы убрать поля со значением null, используйте jsonb_strip_nulls.

NULL на месте ключа недопустим.

Если ключ повторяется несколько раз, в jsonb останется последнее значение.

jsonb_build_object удобно использовать для API-ответов, вложенных объектов и точного контроля над контрактом.

Для массивов дочерних объектов используйте связку jsonb_build_object и jsonb_agg.

Если нужно быстро превратить строку в JSON, есть to_jsonb, но для публичного API безопаснее явно перечислять поля или использовать подзапрос с нужными колонками.

Главное правило простое: не собирайте JSON руками через строки. Пусть PostgreSQL сам правильно сохранит типы, поставит кавычки, обработает NULL и соберёт корректную структуру.