如何将SQL Server查询结果输出为指定多层嵌套结构的自定义JSON

你可以通过嵌套子查询+每层独立使用FOR JSON PATH的方式实现多级嵌套结构，正确写法如下：

SELECT 
    p.ProductId AS [Product],
    (
        SELECT 
            s.SizeId,
            (
                SELECT 
                    d.Date, 
                    d.Price
                FROM #Prices d
                WHERE d.ProductId = p.ProductId AND d.SizeId = s.SizeId
                ORDER BY d.Date
                FOR JSON PATH
            ) AS PerDate
        FROM #Prices s
        WHERE s.ProductId = p.ProductId
        GROUP BY s.SizeId
        ORDER BY s.SizeId
        FOR JSON PATH
    ) AS UnitSizes
FROM #Prices p
GROUP BY p.ProductId
ORDER BY p.ProductId
FOR JSON PATH, ROOT('Products')

实现逻辑说明 #

你原先的单级路径写法只能生成一层嵌套结构，无法生成「UnitSizes数组下每个元素再嵌套PerDate数组」的二级结构，上述写法逻辑如下：

• 最内层子查询：匹配当前外层的ProductId和SizeId，筛选对应所有日期和价格，直接生成PerDate字段对应的JSON数组
• 中间层子查询：匹配当前外层的ProductId，按SizeId去重后生成UnitSizes字段对应的JSON数组，每个数组元素包含SizeId和对应的PerDate数组
• 最外层查询：按ProductId去重后生成最外层的Products数组，配合ROOT参数生成最终的包裹结构

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