Back
Syntax
Study
Editor
Mode:
HTML
CSS
JavaScript
PHP
Reset
Run »
HTML / CSS / JS
-- Build a nested JSON response in one query SELECT json_build_object( 'id', u.id, 'name', u.name, 'email', u.email, 'orders', COALESCE( json_agg( json_build_object( 'id', o.id, 'total', o.total, 'status', o.status, 'items', ( SELECT json_agg( json_build_object('product', p.name, 'qty', oi.qty, 'price', oi.unit_price) ) FROM order_items oi JOIN products p ON p.id = oi.product_id WHERE oi.order_id = o.id ) ) ORDER BY o.created_at DESC ) FILTER (WHERE o.id IS NOT NULL), '[]'::json ) ) AS user_with_orders FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE u.id = 1 GROUP BY u.id, u.name, u.email; -- json_object_agg — build an object from aggregated pairs SELECT json_object_agg(name, price) FROM products WHERE category_id = 1; -- {"Keyboard": 79.99, "Mouse": 39.99}
Result
Open