`jsonb_agg` в PostgreSQL: как собрать строки в JSON-массив

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

Проще говоря, было много строк:

user_id | amount
--------+-------
1       | 500
1       | 1200
1       | 300

А стало одно значение:

[500, 1200, 300]

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

Без jsonb_agg приложение часто получает плоскую таблицу, а потом руками собирает дерево в коде: циклы, словари, группировка, риск ошибки и иногда неприятный N+1. С jsonb_agg база сразу возвращает готовый JSON-документ или вложенный массив.

Главная идея простая: если GROUP BY собирает строки в группы, то jsonb_agg собирает значения внутри каждой группы в JSON-массив.

Чем jsonb_agg отличается от array_agg

В PostgreSQL есть похожая функция array_agg. Она собирает значения в обычный SQL-массив.

Например:

SELECT
    user_id,
    array_agg(amount) AS amounts
FROM orders
GROUP BY user_id;

Но SQL-массив — это не JSON. У него свои правила, свои типы, и все элементы массива должны быть совместимы по типу.

jsonb_agg собирает результат именно в jsonb. Внутрь можно положить число, строку, объект, целую строку таблицы или результат функции вроде jsonb_build_object.

Поэтому jsonb_agg часто используют для JSON API. На выходе получается значение, которое уже похоже на ответ для клиента.

Например, не просто массив чисел:

[500, 1200, 300]

А массив объектов:

[
  {"id": 10, "amount": 500},
  {"id": 11, "amount": 1200},
  {"id": 12, "amount": 300}
]

Именно такие структуры обычно ждёт фронтенд.

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

Минимальный пример:

SELECT
    user_id,
    jsonb_agg(amount) AS amounts
FROM orders
GROUP BY user_id;

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

1. Строки группируются по user_id.
2. Для каждого пользователя PostgreSQL берёт все его amount.
3. jsonb_agg складывает эти значения в один JSON-массив.

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

user_id | amounts
--------+-----------------
1       | [500, 1200, 300]
2       | [700, 900]

Тип результата всегда будет jsonb, даже если внутри агрегируются обычные числа или строки.

Порядок элементов: обязательно используйте ORDER BY

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

Нельзя рассчитывать на то, что строки попадут в массив «как лежат в таблице». В SQL у таблицы нет надёжного физического порядка для результата. Сегодня запрос может вернуть один порядок, завтра другой, особенно после изменения плана выполнения, индексов или объёма данных.

Если порядок важен, пишите ORDER BY прямо внутри jsonb_agg:

SELECT
    user_id,
    jsonb_agg(amount ORDER BY created_at) AS amounts
FROM orders
GROUP BY user_id;

Так PostgreSQL сначала упорядочит значения внутри каждой группы по created_at, а потом соберёт их в JSON-массив.

Это отличается от внешнего ORDER BY.

Вот внешний ORDER BY:

SELECT
    user_id,
    jsonb_agg(amount) AS amounts
FROM orders
GROUP BY user_id
ORDER BY user_id;

Он сортирует строки итогового результата: сначала пользователь 1, потом пользователь 2, потом пользователь 3.

А вот ORDER BY внутри агрегата:

jsonb_agg(amount ORDER BY created_at)

Он сортирует элементы внутри самого JSON-массива.

Для API это принципиально. Если клиент ждёт заказы от новых к старым, порядок нужно закрепить в запросе.

Собираем массив объектов

В реальных задачах редко собирают просто числа. Обычно нужен массив объектов.

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

SELECT
    u.id,
    u.email,
    jsonb_agg(
        jsonb_build_object(
            'id', o.id,
            'amount', o.amount,
            'created_at', o.created_at
        )
        ORDER BY o.created_at DESC
    ) AS orders
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.email;

Здесь сразу несколько важных вещей.

jsonb_build_object создаёт JSON-объект для одного заказа:

{
  "id": 10,
  "amount": 500,
  "created_at": "2026-01-15T10:30:00"
}

А jsonb_agg собирает такие объекты в массив:

[
  {"id": 12, "amount": 300, "created_at": "2026-01-17T09:00:00"},
  {"id": 11, "amount": 1200, "created_at": "2026-01-16T14:20:00"},
  {"id": 10, "amount": 500, "created_at": "2026-01-15T10:30:00"}
]

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

Почему jsonb_build_object часто лучше, чем вся строка целиком

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

SELECT
    user_id,
    jsonb_agg(to_jsonb(o) ORDER BY created_at) AS orders
FROM orders AS o
GROUP BY user_id;

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

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

jsonb_build_object(
    'id', o.id,
    'amount', o.amount,
    'created_at', o.created_at
)

Так вы контролируете контракт ответа.

Если завтра в таблицу orders добавят техническую колонку internal_comment, она не попадёт в API случайно. Если колонку переименуют или появятся лишние поля, внешний ответ не изменится без вашего решения.

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

• для черновиков и внутренних запросов можно брать всю строку;
• для публичного API лучше явно собирать объект.

Фильтрация внутри агрегата через FILTER

Иногда нужно собрать не все строки группы, а только часть.

Например, собрать пользователю только оплаченные заказы:

SELECT
    u.id,
    u.email,
    jsonb_agg(
        jsonb_build_object(
            'id', o.id,
            'amount', o.amount,
            'created_at', o.created_at
        )
        ORDER BY o.created_at DESC
    ) FILTER (WHERE o.status = 'paid') AS paid_orders
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.email;

FILTER относится именно к агрегату. Он говорит: в этот конкретный jsonb_agg клади только строки, где o.status = 'paid'.

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

Например, оплаченные и отменённые заказы отдельно:

SELECT
    u.id,
    u.email,
    jsonb_agg(
        jsonb_build_object('id', o.id, 'amount', o.amount)
        ORDER BY o.created_at DESC
    ) FILTER (WHERE o.status = 'paid') AS paid_orders,
    jsonb_agg(
        jsonb_build_object('id', o.id, 'amount', o.amount)
        ORDER BY o.created_at DESC
    ) FILTER (WHERE o.status = 'cancelled') AS cancelled_orders
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.email;

Так не нужно писать два отдельных запроса или собирать массивы в приложении.

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

Если значение внутри агрегата равно SQL-NULL, оно попадёт в JSON-массив как JSON-null.

Пример:

SELECT jsonb_agg(value) AS items
FROM (
    VALUES (1), (NULL), (3)
) AS t(value);

Результат:

[1, null, 3]

Иногда это нормально. Например, если null действительно должен быть частью данных.

Но если NULL нужно убрать, используйте FILTER:

SELECT jsonb_agg(value) FILTER (WHERE value IS NOT NULL) AS items
FROM (
    VALUES (1), (NULL), (3)
) AS t(value);

Результат:

[1, 3]

Это важная разница. jsonb_agg не выкидывает NULL сам. Он честно кладёт их в массив.

Пустая группа возвращает NULL, а не пустой массив

Это одна из самых частых ловушек.

Если строк для агрегации нет, jsonb_agg возвращает SQL-NULL, а не пустой JSON-массив.

Например, если у пользователя нет заказов, подзапрос с jsonb_agg вернёт NULL.

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

[]

А не:

null

Фронтенд может ожидать массив и вызвать на нём метод вроде map. Если вместо массива прилетит null, код может упасть.

Поэтому в API-запросах почти всегда стоит оборачивать результат в COALESCE.

SELECT
    u.id,
    u.email,
    COALESCE(
        (
            SELECT jsonb_agg(
                jsonb_build_object(
                    'id', o.id,
                    'amount', o.amount
                )
                ORDER BY o.created_at
            )
            FROM orders AS o
            WHERE o.user_id = u.id
        ),
        '[]'::jsonb
    ) AS orders
FROM users AS u;

Теперь если заказов нет, PostgreSQL вернёт пустой массив:

[]

А не null.

Родительский документ с вложенным массивом

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

SELECT
    jsonb_build_object(
        'id', u.id,
        'email', u.email,
        'orders', COALESCE(
            (
                SELECT jsonb_agg(
                    jsonb_build_object(
                        'id', o.id,
                        'amount', o.amount,
                        'created_at', o.created_at
                    )
                    ORDER BY o.created_at
                )
                FROM orders AS o
                WHERE o.user_id = u.id
            ),
            '[]'::jsonb
        )
    ) AS user_doc
FROM users AS u;

На выходе каждая строка — готовый JSON-документ:

{
  "id": 1,
  "email": "a@example.com",
  "orders": [
    {"id": 10, "amount": 500, "created_at": "2026-01-15T10:30:00"},
    {"id": 11, "amount": 1200, "created_at": "2026-01-16T14:20:00"}
  ]
}

Если заказов нет:

{
  "id": 2,
  "email": "b@example.com",
  "orders": []
}

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

Почему это помогает избежать N+1

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

Сначала приложение запрашивает пользователей:

SELECT id, email
FROM users;

Потом для каждого пользователя отдельно запрашивает заказы:

SELECT id, amount
FROM orders
WHERE user_id = 1;

Потом:

SELECT id, amount
FROM orders
WHERE user_id = 2;

Потом для следующего пользователя, и так далее.

Если пользователей сто, приложение может сделать один запрос за пользователями и ещё сто запросов за заказами. Это и есть неприятный сценарий N+1.

С jsonb_agg можно собрать вложенную структуру одним SQL-запросом. База сама соединит и сгруппирует данные, а приложение получит уже готовую форму.

Конечно, это не значит, что всегда нужно собирать весь API-ответ в SQL. Но для отчётов, админок, внутренних API и аккуратных JSON-документов это очень сильный инструмент.

JOIN или коррелированный подзапрос

Массив можно собрать через обычный JOIN и GROUP BY:

SELECT
    u.id,
    u.email,
    jsonb_agg(
        jsonb_build_object(
            'id', o.id,
            'amount', o.amount
        )
        ORDER BY o.created_at
    ) AS orders
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.email;

Но у такого варианта есть особенность: обычный JOIN покажет только пользователей, у которых есть заказы.

Если нужны все пользователи, включая тех, у кого заказов нет, можно взять LEFT JOIN:

SELECT
    u.id,
    u.email,
    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 orders
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.email;

Здесь есть важная деталь:

FILTER (WHERE o.id IS NOT NULL)

Без неё пользователь без заказов может получить массив с одним объектом, где поля будут null.

Нам это не нужно. Нам нужен честный пустой массив.

Поэтому для LEFT JOIN полезная связка такая:

COALESCE(
    jsonb_agg(...) FILTER (WHERE child.id IS NOT NULL),
    '[]'::jsonb
)

Это почти готовый шаблон для вложенных коллекций.

Пример: менеджер и массив подчинённых

jsonb_agg полезен не только для заказов.

Например, соберём каждому менеджеру список его сотрудников:

SELECT
    jsonb_build_object(
        'manager', m.name,
        'reports', COALESCE(
            (
                SELECT jsonb_agg(
                    jsonb_build_object(
                        'id', e.id,
                        'name', e.name,
                        'salary', e.salary
                    )
                    ORDER BY e.salary DESC
                )
                FROM employees AS e
                WHERE e.manager_id = m.id
            ),
            '[]'::jsonb
        )
    ) AS team
FROM employees AS m;

Так можно получить документ вида:

{
  "manager": "Alice",
  "reports": [
    {"id": 7, "name": "Bob", "salary": 250000},
    {"id": 8, "name": "Carol", "salary": 220000}
  ]
}

Если у менеджера нет подчинённых, reports будет пустым массивом.

Пример: категории и товары

Допустим, есть категории и товары:

CREATE TABLE categories (
    id bigint,
    name text
);

CREATE TABLE products (
    id bigint,
    category_id bigint,
    name text,
    price numeric
);

Соберём каждую категорию вместе с товарами:

SELECT
    jsonb_build_object(
        'id', c.id,
        'name', c.name,
        'products', COALESCE(
            jsonb_agg(
                jsonb_build_object(
                    'id', p.id,
                    'name', p.name,
                    'price', p.price
                )
                ORDER BY p.price DESC
            ) FILTER (WHERE p.id IS NOT NULL),
            '[]'::jsonb
        )
    ) AS category_doc
FROM categories AS c
LEFT JOIN products AS p ON p.category_id = c.id
GROUP BY c.id, c.name;

Такой запрос сразу готовит структуру для витрины: категория и список товаров внутри неё.

FILTER тоже может дать NULL

Есть ещё одна ловушка.

Даже если группа не пустая, FILTER может не пропустить ни одной строки. Тогда jsonb_agg тоже вернёт NULL.

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

SELECT
    user_id,
    jsonb_agg(id) FILTER (WHERE status = 'paid') AS paid_order_ids
FROM orders
GROUP BY user_id;

Для такого пользователя paid_order_ids будет NULL.

Если вы хотите получить пустой массив, снова нужен COALESCE:

SELECT
    user_id,
    COALESCE(
        jsonb_agg(id) FILTER (WHERE status = 'paid'),
        '[]'::jsonb
    ) AS paid_order_ids
FROM orders
GROUP BY user_id;

Запомните правило: если результат должен быть массивом всегда, оборачивайте jsonb_agg в COALESCE.

Как не собрать дубликаты

Иногда после JOIN одна и та же строка может размножиться.

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

В такой ситуации jsonb_agg честно соберёт все повторившиеся строки.

Если нужно убрать дубликаты, можно использовать DISTINCT:

SELECT
    user_id,
    jsonb_agg(DISTINCT status) AS statuses
FROM orders
GROUP BY user_id;

Для простых значений это работает хорошо.

С объектами тоже возможно:

SELECT
    user_id,
    jsonb_agg(
        DISTINCT jsonb_build_object(
            'id', id,
            'amount', amount
        )
    ) AS orders
FROM orders
GROUP BY user_id;

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

Часто лучше сначала подготовить чистый набор строк в CTE, а потом агрегировать.

WITH clean_orders AS (
    SELECT DISTINCT
        id,
        user_id,
        amount,
        created_at
    FROM orders
)
SELECT
    user_id,
    jsonb_agg(
        jsonb_build_object(
            'id', id,
            'amount', amount
        )
        ORDER BY created_at
    ) AS orders
FROM clean_orders
GROUP BY user_id;

Так запрос обычно читается спокойнее: сначала убрали лишнее, потом собрали JSON.

Когда не стоит злоупотреблять jsonb_agg

jsonb_agg удобен, но это не волшебная кнопка «сделать весь бекенд в SQL».

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

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

Хороший ориентир такой: jsonb_agg отлично подходит для вложенных коллекций разумного размера. Например, заказы одного пользователя, товары одной категории, подчинённые одного менеджера, элементы одного отчёта.

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

Аналог в MySQL

В MySQL 8 близкий аналог называется JSON_ARRAYAGG.

Пример:

SELECT
    user_id,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', id,
            'amount', amount
        )
    ) AS orders
FROM orders
GROUP BY user_id;

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

Но есть отличия, о которых важно помнить при переносе запросов.

В MySQL нет такого же удобного FILTER, как в PostgreSQL. Обычно фильтрацию делают через WHERE, CASE или отдельные подзапросы.

С порядком тоже нужно быть внимательным: не стоит полагаться на случайный порядок строк. При переносе PostgreSQL-запросов с jsonb_agg(... ORDER BY ...) проверяйте синтаксис и поведение именно вашей версии MySQL.

Аналог в ClickHouse

В ClickHouse похожие задачи часто решают через groupArray.

Например, можно собрать массив значений:

SELECT
    user_id,
    groupArray(amount) AS amounts
FROM orders
GROUP BY user_id;

Для более сложных структур используют кортежи, именованные кортежи, map или последующую сериализацию в JSON.

Но подход всё равно отличается от PostgreSQL. В PostgreSQL jsonb_agg сразу живёт в мире JSON и хорошо сочетается с jsonb_build_object, FILTER, ORDER BY внутри агрегата и COALESCE.

Поэтому для задач вида «собрать аккуратный JSON-документ прямо из SQL» PostgreSQL даёт очень прямой и выразительный инструмент.

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

Первая ошибка — забыть ORDER BY внутри jsonb_agg.

Вот так порядок элементов не гарантирован:

jsonb_agg(amount)

А так порядок закреплён:

jsonb_agg(amount ORDER BY created_at)

Вторая ошибка — ждать пустой массив, а получить NULL.

Если строк нет или FILTER не пропустил ни одной строки, результат будет NULL.

Надёжный вариант для API:

COALESCE(jsonb_agg(...), '[]'::jsonb)

Третья ошибка — при LEFT JOIN собрать объект из пустой правой строки.

Плохо:

jsonb_agg(jsonb_build_object('id', o.id))

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

Лучше:

COALESCE(
    jsonb_agg(jsonb_build_object('id', o.id)) FILTER (WHERE o.id IS NOT NULL),
    '[]'::jsonb
)

Четвёртая ошибка — собирать слишком большие массивы без ограничений.

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

Главное

jsonb_agg собирает значения из нескольких строк в один JSON-массив типа jsonb.

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

Чтобы элементы массива шли в предсказуемом порядке, пишите ORDER BY внутри самой функции.

Чтобы собрать массив объектов, используйте jsonb_build_object внутри jsonb_agg.

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

Если на выходе всегда нужен массив, а не NULL, оборачивайте результат в COALESCE(..., '[]'::jsonb).

jsonb_agg — это способ превратить плоские строки таблицы в аккуратный JSON для приложения. Вместо того чтобы собирать дерево в коде, вы можете попросить базу сделать это там, где данные уже лежат.