Как собирать строки в список и 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, sqlstring_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_agg | jsonb_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.