JSON_AGG and JSONB_AGG in PostgreSQL: Build a JSON Array for an API Response

Знакомая боль: запрос с JOIN выдаёт по строке на каждый заказ пользователя, а фронтенду на выходе нужен один объект «пользователь со списком заказов». Классический путь — вытащить плоскую выборку и склеить её в коде, плодя циклы, словари и N+1. JSON_AGG и JSONB_AGG переворачивают задачу: это агрегатные функции, которые сворачивают строки группы в единственный JSON-массив. «Много строк на ключ» превращается в «одну строку с готовым JSON», и базе остаётся отдать вложенный ответ API целиком, без единой строчки сборки в приложении.

JSON_AGG против JSONB_AGG

Обе функции собирают значения в JSON-массив, но дальше расходятся в том, как этот массив хранится:

• JSON_AGG возвращает тип json — это текст «как набрали»: порядок ключей сохраняется, дубликаты остаются на месте.
• JSONB_AGG возвращает jsonb — разобранное бинарное представление. Дубликаты ключей схлопываются (побеждает последний), лишние пробелы выкидываются, а доступ к полям заметно быстрее.
• Для подавляющего большинства ответов API тяните руку к JSONB_AGG: он компактнее, и дальше по конвейеру его проще фильтровать, индексировать и докручивать.

SELECT JSONB_AGG(t ORDER BY t.id) AS tasks
FROM tasks t;

Передайте в агрегат всю строку t целиком — и получите массив объектов, где ключами станут имена столбцов. Быстрее способа «отдать таблицу как JSON» попросту нет.

ORDER BY внутри агрегата

А вот первая ловушка. Порядок элементов в массиве ничем не гарантирован, пока вы не пропишете ORDER BY прямо внутри вызова. Планировщик волен собирать группу как ему удобнее, и сегодняшний «случайно правильный» порядок завтра поедет после смены плана. Не доверяйте физическому порядку строк в таблице — задавайте сортировку явно:

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

Обратите внимание: ORDER BY живёт внутри скобок агрегата, а не во внешнем запросе. Внешний ORDER BY упорядочит строки результата, но к порядку элементов внутри каждого массива не имеет никакого отношения — на этом спотыкаются чаще всего.

Объект на строку через jsonb_build_object

Голый массив скаляров на практике нужен редко — почти всегда хочется массив объектов. Здесь в дело вступает jsonb_build_object: он лепит JSON-объект из пар «ключ — значение», а JSONB_AGG сворачивает эти объекты в массив:

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

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

Вложенный ответ API одним запросом

Дальше кубики складываются в полноценный документ «пользователь со списком заказов» — за один проход, без N+1 и без сборки на стороне приложения:

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

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

SELECT
    m.name AS manager,
    JSONB_AGG(
        jsonb_build_object('name', e.name, 'salary', e.salary)
        ORDER BY e.salary DESC
    ) AS reports
FROM employees m
JOIN employees e ON e.manager_id = m.id
GROUP BY m.id, m.name;

FILTER, чтобы пропустить NULL

Есть тонкость, которая больно бьёт при LEFT JOIN. JSONB_AGG честно тащит в массив и NULL: строка без совпадения подсунет в результат элемент null. Чтобы такого мусора в ответе не было, навесьте FILTER:

SELECT
    u.id,
    JSONB_AGG(o.id) FILTER (WHERE o.id IS NOT NULL) AS order_ids
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

Грабли: если у пользователя заказов нет вовсе, JSONB_AGG(...) FILTER (...) вернёт не пустой массив [], а NULL. А клиенты API, как правило, терпеть не могут null там, где по контракту ждут массив, — и валятся на десериализации. Лекарство простое: оберните вызов в COALESCE(JSONB_AGG(...) FILTER (...), '[]'::jsonb), и на выходе всегда будет массив, пусть и пустой.

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;

Что держать в голове про MySQL:

• У JSON_ARRAYAGG нет встроенного ORDER BY — нужный порядок придётся задавать заранее отсортированным подзапросом.
• Разделения на json и jsonb тут нет: тип всегда JSON и уже хранится в бинарном нормализованном виде.

В ClickHouse прямого аналога не завезли: массив собирают через groupArray и затем сериализуют либо опираются на функции работы с JSON-строками. Так что когда вам нужен предсказуемый вложенный JSON с сортировкой и фильтрацией под ответ API, связка PostgreSQL и JSONB_AGG ... ORDER BY ... FILTER остаётся самым коротким путём от таблицы до готового документа.