re:re:actable
Урок 083 минуты чтения

Как собирать строки в список и JSON - string_agg и json_agg

На этой странице
Кратко

string_agg(выражение, разделитель ORDER BY …) склеивает значения группы в строку (в MySQL — GROUP_CONCAT). array_agg собирает массив, а json_agg / jsonb_agg — JSON-массив, часто из целых строк или объектов json_build_object(...). Это позволяет одним запросом вернуть «пост с массивом тегов» или «заказ с позициями» без N+1 и без размножения строк. Порядок элементов задают внутри агрегата (ORDER BY запроса на него не влияет), NULL пропускаются у string_agg, но попадают в array_agg/json_agg; после LEFT JOIN пустая группа даёт [null], поэтому используют FILTER (WHERE … IS NOT NULL) и COALESCE(…, '[]').

Вопрос

Как собрать значения группы в строку, массив или JSON?

string_agg

SELECT p.id, p.title, string_agg(t.name, ', ' ORDER BY t.name) AS tags
FROM posts p
JOIN post_tags pt ON pt.post_id = p.id
JOIN tags t       ON t.id = pt.tag_id
GROUP BY p.id, p.title;
-- 1 | Индексы в PostgreSQL | db, performance, sql
string_agg(DISTINCT city, ', ' ORDER BY city)   -- уникальные значения

array_agg

SELECT user_id, array_agg(id ORDER BY created_at DESC) AS order_ids
FROM orders GROUP BY user_id;
-- 1 | {11,10}

json_agg для API

SELECT
  o.id,
  o.total,
  COALESCE(
    json_agg(
      json_build_object('sku', p.sku, 'title', p.title, 'qty', oi.quantity)
      ORDER BY p.title
    ) FILTER (WHERE oi.order_id IS NOT NULL),
    '[]'
  ) AS items
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN products p     ON p.id = oi.product_id
WHERE o.user_id = $1
GROUP BY o.id;
{ "id": 10, "total": 500, "items": [{ "sku": "KB-1", "title": "Keyboard", "qty": 1 }] }

Без FILTER у заказа без позиций будет [{"sku": null, ...}], без COALESCE — null вместо пустого массива.

Через LATERAL или подзапрос

SELECT p.id, p.title,
       (SELECT COALESCE(json_agg(t.name ORDER BY t.name), '[]')
        FROM post_tags pt JOIN tags t ON t.id = pt.tag_id
        WHERE pt.post_id = p.id) AS tags
FROM posts p
ORDER BY p.created_at DESC
LIMIT 20;

Так агрегация выполняется только для 20 постов страницы, и не нужен GROUP BY по всем столбцам.

json или jsonb

json_aggjsonb_agg
Порядок ключей и пробелыСохраняетНормализует
Дубли ключейСохраняетПоследний выигрывает
Для ответа APIДостаточноНужен, если дальше обрабатываете операторами jsonb

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

  • Сортировать через ORDER BY запроса, а не внутри агрегата.
  • [null] после LEFT JOIN без FILTER.
  • Использовать строку через запятую как формат хранения, а не только вывода.
  • Строить огромный JSON на сотни тысяч элементов в одном ответе.

Что важно помнить

  • string_agg — для отображения, array_agg/json_agg — для данных.
  • Порядок — внутри агрегата.
  • FILTER (WHERE x IS NOT NULL) + COALESCE(..., '[]') после LEFT JOIN.

Мини-шпаргалка

  • string_agg(x, ', ' ORDER BY x).
  • array_agg(x ORDER BY y).
  • json_agg(json_build_object(...)).
  • MySQL: GROUP_CONCAT.

Документация

PostgreSQL: Aggregate Functions, PostgreSQL: JSON Functions.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам