SQL查询中如何将Product表字段转为JSON并整合分组结果？

完整SQL查询（MySQL版本） #

SELECT 
    s.order_date,
    JSON_OBJECT(
        'item_id', p.item_id,
        'item_category', p.item_category,
        'item_price', p.item_price,
        'item_name', p.item_name,
        'date_created', p.date_created,
        'date_updated', p.date_updated
    ) AS detail,
    COUNT(s.cust_id) AS total_customer,
    SUM(s.total_price) AS total_price
FROM Sales s
INNER JOIN Product p ON s.item_id = p.item_id
WHERE s.status = 'Sold'
GROUP BY s.order_date, detail

关键说明 #

1. 表关联：通过INNER JOIN将Sales表和Product表用item_id关联，获取商品的完整字段信息
2. JSON构造：使用MySQL内置的JSON_OBJECT()函数，将Product表的指定字段按键值对拼接成JSON格式字符串，对应结果中的detail列
3. 分组逻辑：保留原有的分组统计逻辑，由于detail是每个分组的唯一标识（示例数据中每个订单日期对应单个商品），需将其加入GROUP BY子句以符合SQL标准分组规则
4. 过滤条件：保留status = 'Sold'的过滤规则，仅统计已完成的订单

其他数据库适配 #

如果使用PostgreSQL，只需将JSON_OBJECT()替换为PostgreSQL的json_build_object()函数即可：

SELECT 
    s.order_date,
    json_build_object(
        'item_id', p.item_id,
        'item_category', p.item_category,
        'item_price', p.item_price,
        'item_name', p.item_name,
        'date_created', p.date_created,
        'date_updated', p.date_updated
    ) AS detail,
    COUNT(s.cust_id) AS total_customer,
    SUM(s.total_price) AS total_price
FROM Sales s
INNER JOIN Product p ON s.item_id = p.item_id
WHERE s.status = 'Sold'
GROUP BY s.order_date, detail

特殊场景处理 #

如果同一订单日期存在多个不同商品，需要将多个商品信息合并为JSON数组，可使用JSON_ARRAYAGG()（MySQL）或json_agg()（PostgreSQL），示例（MySQL）：

SELECT 
    s.order_date,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'item_id', p.item_id,
            'item_category', p.item_category,
            'item_price', p.item_price,
            'item_name', p.item_name,
            'date_created', p.date_created,
            'date_updated', p.date_updated
        )
    ) AS detail,
    COUNT(DISTINCT s.cust_id) AS total_customer,
    SUM(s.total_price) AS total_price
FROM Sales s
INNER JOIN Product p ON s.item_id = p.item_id
WHERE s.status = 'Sold'
GROUP BY s.order_date

内容的提问来源于stack exchange，提问作者Sargeras21