`jsonb_build_array` в PostgreSQL: как собрать JSON-массив прямо в SQL

Иногда SQL-запрос должен вернуть не просто строки таблицы, а готовый кусок JSON для приложения или API.

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

Для таких задач в PostgreSQL есть функция jsonb_build_array.

Она собирает JSON-массив из переданных аргументов:

SELECT jsonb_build_array(1, 'two', true, NULL);

Результат:

[1, "two", true, null]

Главная особенность: аргументы могут быть разных типов. В одном JSON-массиве спокойно живут числа, строки, булевы значения, даты, null, объекты и другие массивы.

Это не похоже на обычный SQL-массив через ARRAY[...], где PostgreSQL старается привести все элементы к одному общему типу. JSON устроен гибче: в нём массив может быть разношёрстным, как настоящая коробка с данными.

Зачем нужен jsonb_build_array

Допустим, в таблице employees лежат сотрудники:

id	name	salary	dept
42	Ada	95000.00	engineering

Можно собрать из строки компактный JSON-массив:

SELECT jsonb_build_array(id, name, salary, dept) AS employee_row
FROM employees
WHERE id = 42;

Результат:

[42, "Ada", 95000.00, "engineering"]

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

1. сначала id;
2. потом name;
3. потом salary;
4. потом dept.

Это похоже на короткую строку таблицы, только в JSON-формате.

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

Синтаксис простой:

jsonb_build_array(value1, value2, value3, ...)

Передаёте сколько угодно значений — получаете одно значение типа jsonb.

Пример:

SELECT jsonb_build_array(10, 'active', false);

Результат:

[10, "active", false]

Порядок элементов сохраняется ровно таким, каким вы указали аргументы.

SELECT jsonb_build_array('first', 'second', 'third');

Результат:

["first", "second", "third"]

Для массива это важно: в JSON-массиве порядок имеет значение.

Какие типы можно передавать

jsonb_build_array принимает обычные SQL-значения и превращает их в JSON.

Пример:

SELECT jsonb_build_array(
  100,
  'paid',
  true,
  DATE '2024-01-10',
  NULL
);

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

[100, "paid", true, "2024-01-10", null]

Правила понятные:

SQL-значение	Во что превращается в JSON
число	число
текст	строка
boolean	true или false
NULL	null
date	строка с датой
timestamp	строка с датой и временем
jsonb	готовый JSON без лишнего превращения в текст

Важно: SQL-NULL не исчезает из массива. Он становится JSON-значением null и остаётся на своём месте.

SELECT jsonb_build_array('a', NULL, 'c');

Результат:

["a", null, "c"]

Это хорошо: позиции элементов не съезжают. Второй элемент остался вторым, просто его значение — null.

Чем jsonb_build_array отличается от обычного ARRAY

На первый взгляд может показаться: зачем нужна отдельная функция, если в SQL уже есть массивы?

Например:

SELECT ARRAY[1, 2, 3];

Это обычный SQL-массив.

Но SQL-массивы требуют, чтобы элементы были одного типа или могли быть приведены к общему типу. А JSON-массив может содержать разные типы.

Вот так работает:

SELECT jsonb_build_array(1, 'x', true);

Результат:

[1, "x", true]

А вот обычный SQL-массив с такими разными значениями уже проблемный:

SELECT ARRAY[1, 'x', true];

PostgreSQL не может спокойно сложить число, строку и булево значение в один обычный массив без общего типа.

Поэтому правило простое:

• нужен обычный однородный SQL-массив — используйте ARRAY[...];
• нужен гибкий JSON-массив из разных типов — используйте jsonb_build_array.

Пустой массив

Функцию можно вызвать без аргументов:

SELECT jsonb_build_array();

Результат:

[]

Это удобно, когда нужно вернуть пустой JSON-массив вместо NULL.

Например, для API часто лучше вернуть:

[]

чем:

null

Пустой массив обычно означает: «список есть, просто в нём пока нет элементов». А null часто читается как «значение неизвестно» или «поле отсутствует».

Собираем строки таблицы в массивы

jsonb_build_array часто используют вместе с агрегатом jsonb_agg.

Одна функция собирает массив из одной строки, другая собирает много таких массивов в большой JSON-массив.

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

id	email	country	created_at
1	a@example.com	PT	2024-01-10
2	b@example.com	PT	2024-01-12

Соберём пользователей из Португалии в JSON:

SELECT jsonb_agg(
  jsonb_build_array(id, email, country, created_at)
) AS rows
FROM users
WHERE country = 'PT';

Результат:

[
  [1, "a@example.com", "PT", "2024-01-10T00:00:00"],
  [2, "b@example.com", "PT", "2024-01-12T00:00:00"]
]

Получился массив массивов.

Каждый внутренний массив — это одна строка:

[1, "a@example.com", "PT", "2024-01-10T00:00:00"]

А внешний массив — весь результат выборки.

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

Позиционный массив: компактно, но нужно помнить порядок

Массивы без ключей хороши, когда клиент точно знает порядок полей.

Например, вы договорились:

[id, email, country, created_at]

Тогда клиент понимает, что первый элемент — это id, второй — email, третий — country.

Но если кто-то поменяет порядок в SQL:

SELECT jsonb_agg(
  jsonb_build_array(email, id, country, created_at)
) AS rows
FROM users
WHERE country = 'PT';

Результат всё ещё будет валидным JSON. Ошибки не будет. Но клиент может прочитать email как id, а id как email.

Это неприятный тип багов: всё технически работает, но смысл данных сломан.

Поэтому позиционные массивы хороши там, где порядок жёстко зафиксирован и хорошо документирован.

Если контракт между базой, бэкендом и фронтендом ещё меняется, безопаснее использовать объект с ключами через jsonb_build_object.

Когда лучше использовать объект вместо массива

Сравним два формата.

Позиционный массив:

[42, "Ada", 95000.00, "engineering"]

Объект:

{
  "id": 42,
  "name": "Ada",
  "salary": 95000.00,
  "dept": "engineering"
}

Массив короче. Объект понятнее.

Через объект видно, где какое поле. Даже если поменять порядок ключей, смысл не потеряется.

Для объекта в PostgreSQL используют jsonb_build_object:

SELECT jsonb_build_object(
  'id', id,
  'name', name,
  'salary', salary,
  'dept', dept
) AS employee
FROM employees
WHERE id = 42;

Результат:

{
  "id": 42,
  "name": "Ada",
  "salary": 95000.00,
  "dept": "engineering"
}

Выбор простой:

• нужен компактный формат и порядок заранее известен — берите jsonb_build_array;
• нужен понятный самодокументируемый JSON — берите jsonb_build_object.

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

Сила JSON в том, что его можно вкладывать: массивы внутри объектов, объекты внутри массивов, массивы внутри массивов.

jsonb_build_array отлично работает вместе с jsonb_build_object.

Допустим, нужно собрать ответ по пользователю и его последним заказам:

SELECT jsonb_build_object(
  'user_id', u.id,
  'email', u.email,
  '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,
  "email": "user@example.com",
  "recent_orders": [
    [100, 49.90, "paid"],
    [101, 12.00, "pending"]
  ]
}

Что здесь происходит:

1. Внешний jsonb_build_object собирает объект пользователя.
2. Внутренний подзапрос находит его заказы.
3. jsonb_build_array превращает каждый заказ в короткий массив.
4. jsonb_agg собирает все заказы в один массив.

В итоге PostgreSQL сразу отдаёт готовый JSON для ответа API.

Что будет, если заказов нет

Есть важный нюанс: jsonb_agg возвращает NULL, если строк для агрегации нет.

То есть в предыдущем примере у пользователя без заказов поле recent_orders может стать null.

Иногда это нормально. Но для API часто удобнее вернуть пустой массив [].

Для этого используют coalesce:

SELECT jsonb_build_object(
  'user_id', u.id,
  'email', u.email,
  'recent_orders',
  coalesce(
    (
      SELECT jsonb_agg(
        jsonb_build_array(o.id, o.amount, o.status)
      )
      FROM orders o
      WHERE o.user_id = u.id
    ),
    '[]'::jsonb
  )
) AS payload
FROM users u
WHERE u.id = 7;

Теперь если заказов нет, результат будет таким:

{
  "user_id": 7,
  "email": "user@example.com",
  "recent_orders": []
}

Для клиента это обычно приятнее: он всегда получает массив и может спокойно по нему пройтись.

Отличие от to_jsonb(ARRAY[...])

Есть похожий на вид способ:

SELECT to_jsonb(ARRAY[1, 2, 3]);

Результат:

[1, 2, 3]

Но это другая история.

to_jsonb(ARRAY[...]) сначала создаёт обычный SQL-массив, а потом превращает его в JSONB.

Значит, на этапе ARRAY[...] всё ещё действуют правила SQL-массивов: элементы должны быть одного типа.

Так работает:

SELECT to_jsonb(ARRAY[1, 2, 3]);

Так тоже:

SELECT jsonb_build_array(1, 'x', true);

А вот так может не сработать:

SELECT to_jsonb(ARRAY[1, 'x', true]);

Проблема возникает ещё до to_jsonb: PostgreSQL пытается собрать обычный SQL-массив из разнотипных элементов.

Поэтому запомните:

• jsonb_build_array(1, 'x', true) — собирает JSON-массив из разных типов;
• to_jsonb(ARRAY[1, 2, 3]) — превращает уже готовый однородный SQL-массив в JSONB.

Если данные уже лежат в нормальном SQL-массиве, to_jsonb подходит отлично.

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

jsonb_build_array и готовые JSON-значения

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

SELECT jsonb_build_array(
  1,
  jsonb_build_object('type', 'email', 'enabled', true),
  jsonb_build_object('type', 'sms', 'enabled', false)
);

Результат:

[
  1,
  {
    "type": "email",
    "enabled": true
  },
  {
    "type": "sms",
    "enabled": false
  }
]

Это полезно, когда часть ответа удобнее представить объектом, а часть — коротким значением.

JSON не заставляет выбирать только один уровень или один формат. Главное — чтобы структура была понятна тем, кто её потом читает.

Что значит результат типа jsonb

jsonb_build_array возвращает значение типа jsonb.

Это не просто текстовая строка с квадратными скобками. Это JSON в бинарном нормализованном формате PostgreSQL.

Практически это значит:

• пробелы и форматирование внутри JSON не сохраняются;
• внутри объектов порядок ключей не стоит считать значимым;
• если во вложенном объекте встретятся дубли ключей, jsonb оставит итоговое нормализованное значение;
• порядок элементов массива сохраняется;
• с результатом можно дальше работать JSONB-операторами и функциями.

Например, можно достать первый элемент:

SELECT jsonb_build_array('a', 'b', 'c') -> 0;

Результат:

"a"

Можно склеить два JSONB-массива оператором ||:

SELECT jsonb_build_array(1, 2) || jsonb_build_array(3, 4);

Результат:

[1, 2, 3, 4]

То есть результат функции остаётся полноценным JSONB-значением, а не мёртвой строкой.

jsonb_build_array и json_build_array

У функции есть близкая родственница — json_build_array.

Разница в типе результата:

• jsonb_build_array возвращает jsonb;
• json_build_array возвращает json.

Синтаксис у них одинаковый:

SELECT json_build_array(1, 'two', true);

Результат по смыслу такой же:

[1, "two", true]

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

json больше похож на хранение исходного JSON-текста. jsonb — на рабочий формат для запросов.

Для статей, API-ответов и практических задач с PostgreSQL обычно разумный выбор — jsonb_build_array.

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

Путать JSON-массив и SQL-массив

Неправильно думать, что это одно и то же:

SELECT ARRAY[1, 2, 3];

и:

SELECT jsonb_build_array(1, 2, 3);

Первый запрос возвращает SQL-массив. Второй — JSONB-массив.

Они могут выглядеть похоже, но типы разные, функции для работы с ними разные, правила тоже разные.

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

Вот такой JSON компактный:

[42, "Ada", 95000.00]

Но что означает 95000.00? Зарплату? Лимит? Баланс? Сумму заказов?

Если порядок и смысл элементов нигде не зафиксированы, такой формат быстро превращается в загадку.

В сомнительных случаях лучше объект:

{
  "id": 42,
  "name": "Ada",
  "salary": 95000.00
}

Ждать, что NULL исчезнет

NULL не удаляется из массива:

SELECT jsonb_build_array('a', NULL, 'c');

Результат:

["a", null, "c"]

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

Например, при сборке из строк можно использовать WHERE value IS NOT NULL в подзапросе.

Забыть про coalesce после jsonb_agg

Если jsonb_agg не нашёл строк, он вернёт NULL, а не пустой массив.

Поэтому для списков в API часто пишут так:

SELECT coalesce(jsonb_agg(jsonb_build_array(id, status)), '[]'::jsonb)
FROM orders
WHERE user_id = 7;

Так клиент получит [], если заказов нет.

Как это выглядит в других СУБД

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

SELECT JSON_ARRAY(1, 'two', true);

Идея та же: передаёте значения, получаете JSON-массив. Для объектов рядом есть JSON_OBJECT.

В ClickHouse подход другой. Там часто работают с кортежами, массивами и сериализацией через функции вроде toJSONString. Но прямой привычной связки, полностью похожей на jsonb_build_array и jsonb_build_object в PostgreSQL, обычно не используют в том же стиле.

Если проект должен поддерживать несколько СУБД, лучше не размазывать сборку JSON по всему коду. Синтаксис отличается, и переносить такие запросы между движками дословно не получится.

Главное

jsonb_build_array собирает JSONB-массив из перечисленных значений.

Запомните основные правила:

• аргументы могут быть разных типов;
• порядок элементов сохраняется;
• SQL-NULL превращается в JSON null;
• результат имеет тип jsonb;
• функцию удобно сочетать с jsonb_agg;
• для понятного JSON с именами полей лучше использовать jsonb_build_object;
• to_jsonb(ARRAY[...]) подходит для уже готовых однородных SQL-массивов, а не для произвольного набора разных значений.

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