JSONB_AGG in PostgreSQL: Collect Grouped Rows into a JSON Array for JSON APIs

jsonb_agg — это агрегатная функция PostgreSQL, которая складывает значения всех строк группы в один массив jsonb. Берёте её, когда приложению нужен JSON с вложенными коллекциями: вместо того чтобы вытаскивать плоские строки и собирать дерево уже в коде — с циклами, словарями и неизбежным N+1, — вы получаете готовый массив прямо из базы, одним запросом.

Ключевое отличие от array_agg в том, что array_agg ограничен одним типом элемента, а jsonb_agg глотает что угодно: числа, объекты, целые строки таблицы. Именно поэтому она стала рабочей лошадкой JSON API — на выходе документ, готовый уйти клиенту без единой склейки на стороне сервиса. Чаще всего внутрь подставляют не голый скаляр, а jsonb_build_object(...) или row_to_json(...), чтобы каждый элемент массива был полноценным объектом.

Три вещи, на которых спотыкаются чаще всего и к которым мы вернёмся ниже: порядок элементов не гарантирован без ORDER BY внутри самого вызова; пустая группа даёт NULL, а не пустой массив []; и в MySQL с ClickHouse похожие функции ведут себя иначе. Разберём каждую по порядку.

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

Сигнатура ничем не отличается от любого другого агрегата: jsonb_agg(item ORDER BY created_at), обычно в паре с GROUP BY. В роли item редко встретишь голый скаляр — чаще туда подставляют row_to_json(...) или jsonb_build_object(...), чтобы каждый элемент массива оказался полноценным объектом, а не одиноким числом.

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

Несколько деталей, которые экономят часы отладки:

• Порядок элементов не гарантирован, пока вы не добавили ORDER BY внутрь самого агрегата. Физический порядок строк в таблице — иллюзия, на которую опираться нельзя.
• Тип результата — всегда jsonb, какое бы выражение ни стояло внутри.
• Строки со значением NULL попадают в массив как JSON null. Хотите от них избавиться — навесьте FILTER (WHERE expr IS NOT NULL).
• Пустая группа даёт NULL, а вовсе не пустой массив []. Это та самая мина, к которой мы ещё вернёмся.

Массив объектов с ORDER BY и FILTER

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

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

Здесь jsonb_build_object отдаёт вам полный контроль над именами ключей — удобно, когда контракт ответа зафиксирован. А to_jsonb(o) берёт строку целиком, со всеми колонками, и хорош там, где схема ещё гуляет. Выбор между ними — это, по сути, выбор между строгим API и гибким черновиком.

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

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

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

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

Пустая группа: COALESCE до '[]'

А вот и обещанная мина. Если у пользователя нет ни одного заказа, подзапрос вернёт NULL, а не []. Фронтенд, который ждёт массив и зовёт на нём .map(), на таком ответе захлебнётся. Лечится одним оборачиванием в COALESCE:

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

Грабли: FILTER способен превратить в NULL даже непустую группу. Если ни одна строка не прошла условие, jsonb_agg(...) FILTER (...) вернёт NULL, а не []. Поэтому COALESCE(..., '[]'::jsonb) нужен при любом FILTER — даже когда строки в группе заведомо есть, гарантии массива на выходе всё равно нет.

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

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

MySQL и ClickHouse

В MySQL 8 близкий аналог зовётся JSON_ARRAYAGG, а объекты под него лепят через JSON_OBJECT:

-- MySQL 8: array of objects, one per order
SELECT user_id,
       JSON_ARRAYAGG(JSON_OBJECT('id', id, 'amount', amount)) AS orders
FROM orders
GROUP BY user_id;

Отличия, о которые легко споткнуться при переезде:

• У JSON_ARRAYAGG нет встроенного ORDER BY — порядок придётся задавать заранее отсортированным подзапросом.
• FILTER тоже отсутствует; отбор делают через WHERE или условные выражения внутри агрегата.
• В ClickHouse похожий результат собирают из groupArray поверх tuple либо в связке с toJSONString.

Когда нужен предсказуемый JSON-документ с вложенными коллекциями и твёрдой гарантией [] вместо NULL, связка PostgreSQL jsonb_agg ... ORDER BY ... FILTER плюс COALESCE остаётся самым прямым путём от строк таблицы до ответа API.