説明

1.　注文ヘッダ、注文明細のテーブル定義。

-- 注文ヘッダ
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    shipping_address VARCHAR(255),
    status VARCHAR(50)
);

-- 注文明細
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    product_name VARCHAR(255),
    quantity INT NOT NULL,
    unit_price INT
);

2.　WITHでorder_itemsテーブルから各注文に対応する明細をJSONB形式でまとめるサブクエリを作成。
3.　注文ヘッダの情報とサブクエリで作成した注文明細のJSONBを結合して取得。

-- 各注文に対応する明細をJSONBとしてまとめるサブクエリ
WITH order_items_json AS (
    SELECT
        order_id,
        jsonb_agg(jsonb_build_object(
            'order_item_id', order_item_id,
            'product_id', product_id,
            'product_name', product_name,
            'quantity', quantity,
            'unit_price', unit_price
        )) AS order_items
    FROM
        order_items
    GROUP BY
        order_id
)

-- メインクエリでLEFT JOINを使用してサブクエリと結合
SELECT
    o.order_id,
    o.order_date,
    o.customer_id,
    o.shipping_address,
    o.status,
    oij.order_items
FROM
    orders o
LEFT JOIN
    order_items_json oij ON o.order_id = oij.order_id;