Як зібрати відповідь API прямо в SQL через json_agg і jsonb_build_object?

PostgreSQL може зібрати вкладену JSON-структуру одним запитом - без N+1 і без складання масивів у застосунку.

SELECT jsonb_build_object(
    'id', u.id,
    'name', u.name,
    'orders', coalesce(
        (SELECT jsonb_agg(
                    jsonb_build_object('id', o.id, 'total', o.total, 'created_at', o.created_at)
                    ORDER BY o.created_at DESC
                )
         FROM orders o
         WHERE o.user_id = u.id),
        '[]'::jsonb
    )
) AS payload
FROM users u
WHERE u.id = 42;

Основні функції:

• jsonb_build_object(k1, v1, ...) - об'єкт з пар ключ-значення;
• jsonb_build_array(...) - масив;
• jsonb_agg(expr ORDER BY ...) - агрегат: рядки групи → JSON-масив;
• jsonb_object_agg(key, value) - агрегат: рядки → JSON-об'єкт;
• row_to_json(t) / to_jsonb(t) - увесь рядок у JSON.

Пастки:

• jsonb_agg по порожній групі повертає NULL, а не [] - звідси coalesce(..., '[]').
• jsonb не зберігає порядок ключів (впорядковує їх), а json - зберігає. Якщо порядок полів у відповіді важливий, - json_build_object / json_agg.
• Числа numeric потрапляють у JSON як числа з повною точністю; клієнт на JavaScript може їх округлити - гроші краще віддавати рядками чи в копійках.

Коли це доречно:

• Ендпоінти з глибоко вкладеними даними, де збирання в застосунку дає багато запитів.
• Експорти й звіти, де формат стабільний.
• Сервіси на кшталт PostgREST і Hasura будують усі відповіді саме так.

Коли - ні: у звичайному Laravel-застосунку форматування відповіді належить API Resources: там логіка видимості полів, перетворень і версій. SQL-збирання JSON - інструмент для гарячих місць, де виміряно, що звичайний підхід надто повільний.

Докладніше в документації: Агрегатні функції