SQL使用FOR JSON PATH生成指定嵌套JSON结果异常如何修正

问题原因 #

你当前使用的SQL外层查询直接遍历全表sample_table，没有对orderNumber做去重处理，因此表内有多少行数据，外层就会生成多少个独立的orderNumber对象，最终导致相同订单号的条目被拆分。

修正后的查询语句 #

SELECT
    (SELECT DISTINCT orderNumber as orderNumber,
        (SELECT orderName
         FROM sample_table b
         WHERE a.orderNumber = b.orderNumber
         FOR JSON PATH) AS orders
     FROM sample_table a 
     FOR JSON PATH, ROOT('data')
    ) AS orderdata

如果你更习惯用分组语法，也可以将外层的DISTINCT替换为GROUP BY，效果完全一致：

SELECT
    (SELECT orderNumber as orderNumber,
        (SELECT orderName
         FROM sample_table b
         WHERE a.orderNumber = b.orderNumber
         FOR JSON PATH) AS orders
     FROM sample_table a 
     GROUP BY orderNumber
     FOR JSON PATH, ROOT('data')
    ) AS orderdata

修正逻辑说明 #

在外层查询中添加去重/分组逻辑后，外层查询会自动合并重复的orderNumber，每个唯一订单号仅生成一个外层对象，子查询会将该订单号下所有的订单名称聚合到orders数组中，最终输出你需要的嵌套JSON结构。

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