ARRAY_AGG and JSON_AGG (PostgreSQL)

-- All tag names per user, as a real PG array
SELECT user_id,
       ARRAY_AGG(tag_name ORDER BY tag_name) AS tags
FROM   user_tags
GROUP  BY user_id;

-- As a JSON array of objects — perfect for one-shot API responses
SELECT u.id, u.name,
       JSON_AGG(JSON_BUILD_OBJECT(
         'id', o.id, 'amount', o.amount, 'date', o.created_at
       ) ORDER BY o.created_at DESC) AS recent_orders
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
GROUP  BY u.id, u.name;

-- Build a JSON object keyed by user id
SELECT JSON_OBJECT_AGG(id, name) AS users_by_id
FROM   users;

-- Filter NULLs out of the array (LEFT JOIN with no matches → NULL element)
SELECT u.id,
       ARRAY_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;