JSON Aggregation and Querying

-- Aggregate orders with their items as JSON array
SELECT
  o.id AS order_id,
  o.created_at,
  o.total,
  json_agg(
    json_build_object(
      'product', p.name,
      'quantity', oi.quantity,
      'price', oi.price
    ) ORDER BY p.name
  ) AS items
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY o.id;

-- Query JSONB column
SELECT id, data->>'name' AS name
FROM events
WHERE data @> '{"type": "purchase"}'  -- contains
  AND (data->>'amount')::int > 100;

-- Build nested JSON response in one query
SELECT json_build_object(
  'user', json_build_object('id', u.id, 'name', u.name),
  'posts', (
    SELECT json_agg(json_build_object('id', p.id, 'title', p.title))
    FROM posts p WHERE p.user_id = u.id
  ),
  'stats', json_build_object(
    'post_count', (SELECT count(*) FROM posts WHERE user_id = u.id),
    'joined', u.created_at
  )
) AS profile
FROM users u
WHERE u.id = 1;