Aggregate Rows into JSON Arrays and Objects (PostgreSQL)

SELECT
    o.order_id,
    o.order_date,
    o.customer_id,
    JSON_AGG(
        JSON_BUILD_OBJECT(
            'product_name', oi.product_name,
            'quantity', oi.quantity,
            'price', oi.price
        )
    ) AS order_items_json
FROM
    orders o
JOIN
    order_items oi ON o.order_id = oi.order_id
GROUP BY
    o.order_id, o.order_date, o.customer_id
ORDER BY
    o.order_id;

How it works: This PostgreSQL query aggregates related `order_items` into a single JSON array for each `order`. It uses `JSON_BUILD_OBJECT` to create a JSON object for each item (containing `product_name`, `quantity`, and `price`) and then `JSON_AGG` to collect these objects into a JSON array. This is useful for fetching complex, nested data structures in a single query, reducing the need for multiple database round-trips.