如何在一对多关系中筛选映射至单条记录的行？

要实现将一对多关系的数据整合成指定的嵌套JSON格式，以商品与尺寸价格的关联场景为例，可利用MySQL的JSON聚合函数完成：

核心实现SQL #

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'id', p.id,
        'name', p.name,
        'sizes', JSON_ARRAYAGG(
            JSON_OBJECT(
                'size', s.size,
                'price', s.price
            )
        )
    )
) AS result
FROM products p
JOIN sizes s ON p.id = s.product_id
GROUP BY p.id, p.name;

代码说明 #

• JOIN sizes s ON p.id = s.product_id：关联商品表与尺寸表，建立一对多的关联关系
• JSON_OBJECT('size', s.size, 'price', s.price)：将单条尺寸记录转换为指定结构的JSON对象
• JSON_ARRAYAGG(...)：把同一商品对应的所有尺寸JSON对象聚合为数组，对应sizes字段
• 外层JSON_OBJECT：构造包含商品ID、名称及尺寸数组的完整商品JSON结构
• 最外层JSON_ARRAYAGG：将所有商品的JSON对象聚合为一个数组，得到预期的输出格式

筛选特定记录 #

如果需要筛选符合条件的数据（比如仅保留价格大于2000的尺寸），只需添加WHERE子句：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'id', p.id,
        'name', p.name,
        'sizes', JSON_ARRAYAGG(
            JSON_OBJECT(
                'size', s.size,
                'price', s.price
            )
        )
    )
) AS result
FROM products p
JOIN sizes s ON p.id = s.product_id
WHERE s.price > 2000
GROUP BY p.id, p.name;

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