jsonb_build_array in PostgreSQL: Build JSON Arrays from Mixed-Type Values

jsonb_build_array — это функция PostgreSQL, которая собирает JSON-массив из перечисленных аргументов, причём каждому аргументу разрешено быть своего типа. В отличие от обычного SQL-массива ARRAY[...], где все элементы обязаны делить один тип, здесь в одном массиве спокойно соседствуют числа, строки, даты, булевы значения и вложенные объекты. Применяют её, когда бэкенду нужно отдать клиенту не плоскую строку таблицы, а готовый кусок JSON, и собирать его руками на стороне приложения уже не хочется.

Возвращает функция значение типа jsonb, то есть нормализованное бинарное представление: дубли ключей во вложенных объектах схлопываются, пробелы не сохраняются, а порядок элементов самого массива остаётся ровно таким, в каком вы перечислили аргументы. Если нужен потоковый текст без нормализации, есть парная функция json_build_array с типом результата json — синтаксис тот же, отличается только хранение.

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

Передаёте функции сколько угодно аргументов — получаете значение типа jsonb, в котором порядок элементов сохранён в точности.

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

Ключевое слово здесь — разнотипность. Тот же вызов через ARRAY[...] попросту не скомпилируется, потому что элементы несовместимы по типу:

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

Каждый аргумент проходит штатное приведение к JSON: числа остаются числами, текст превращается в строки, boolean — в true/false, а SQL-NULL — в JSON null. Дата или timestamp аккуратно сериализуется в строку формата ISO. Никакой магии — ровно те же правила, по которым PostgreSQL вообще раскладывает значения в JSON. Аргументов может не быть вовсе: пустой вызов jsonb_build_array() законен и даёт пустой массив [], что удобно как безопасное значение по умолчанию вместо NULL. Если же тип аргумента самой функции неоднозначен, его иногда приходится подсказать явным приведением — например, передать NULL::int вместо голого NULL, чтобы планировщик не спорил о типе.

Кортежи и строки таблицы как JSON

По-настоящему функция раскрывается там, где нужен «кортеж» фиксированной формы — позиционный массив вместо объекта с ключами. Он компактнее объекта и идеально ложится на табличные данные, когда клиент и так знает порядок колонок: незачем гонять по сети имена полей в каждой строке.

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

Работают в паре: jsonb_build_array лепит из каждой строки один кортеж, а jsonb_agg сворачивает их все в массив массивов — получается плотный «дамп» выборки одним значением. Платой за компактность становится потеря самодокументируемости: позиции колонок зашиты в порядок аргументов, поэтому стоит переставить поля в SELECT местами — и клиент молча получит сдвинутую схему. Если контракт между сервисами не зафиксирован, безопаснее объект с ключами через jsonb_build_object; позиционный кортеж хорош там, где порядок согласован раз и навсегда.

Вложенность с jsonb_build_object

Дальше начинается самое интересное — связка с jsonb_build_object. Массив может держать внутри объекты, объекты — массивы, и так на любую глубину; жёсткого предела вложенности нет.

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"]]}

Внутренний массив — компактный позиционный кортеж заказа, внешний объект навешивает на него понятные ключи. Так прямо в запросе собирается полноценный ответ API, без промежуточного склеивания на стороне приложения.

Отличие от to_jsonb массива

А вот и место, где спотыкаются чаще всего: jsonb_build_array(a, b, c) против to_jsonb(ARRAY[a, b, c]). На вид результат похож, по сути — две разные истории.

• jsonb_build_array берёт разнотипные аргументы и бережно сохраняет их «родные» JSON-типы.
• to_jsonb от SQL-массива требует, чтобы массив был однородным, и конвертирует его целиком, как есть.

-- Works: mixed types
SELECT jsonb_build_array(1, 'x', true);   -- [1, "x", true]

-- Error: ARRAY needs a common type
SELECT to_jsonb(ARRAY[1, 'x', true]);     -- ERROR: cannot mix types

-- Works: single-typed array
SELECT to_jsonb(ARRAY[1, 2, 3]);          -- [1, 2, 3]

Грабли: PostgreSQL попытается привести литералы ARRAY[1, 'x'] к общему типу — и упадёт с ошибкой о несовместимости ещё до того, как дело дойдёт до to_jsonb. Нужен именно набор значений разных типов — берите jsonb_build_array. А to_jsonb приберегите для уже однородного массива или для целой строки таблицы (to_jsonb(t.*)).

И ещё тонкость с NULL. В jsonb_build_array SQL-NULL становится JSON null и остаётся в массиве на своём месте, а не молча выпадает — длина и позиции элементов не «съезжают». Это важное отличие от jsonb_build_object, где NULL допустим в значениях, но не в имени ключа. Хотите выкинуть пустые значения — фильтруйте их заранее, в подзапросе, а не надейтесь, что функция сделает это за вас. Сам результат jsonb тоже не теряется при дальнейшей сборке: его можно вложить ещё глубже, склеить с другим массивом оператором || или достать элемент по индексу через ->, не превращая обратно в текст.

Другие СУБД

• MySQL предлагает JSON_ARRAY(...) — прямой аналог с той же идеей разнотипных аргументов. Для объектов рядом лежит JSON_OBJECT(...).
• ClickHouse исторически смотрит на JSON под другим углом: есть тип JSON и функции вроде toJSONString, но позиционной сборки «как в Postgres» нет — кортежи чаще собирают через tuple(), а потом сериализуют.

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