如何在SQL Server 2016中用SQL查询生成多级JSON输出？

当然可以！SQL Server 2016完全支持生成这种多级嵌套的JSON结构，用FOR JSON PATH语法就能轻松实现。下面结合你的需求给出具体实现：

核心思路 #

利用嵌套的FOR JSON PATH查询来构建层级结构：

1. 外层查询构造patient数组中的每个对象（包含key和data字段）
2. 内层子查询生成每个key对应的患者详情数组data
3. 最后用ROOT('patient')将结果包裹到指定的根节点中

假设表结构 #

首先假设你的patient_data表包含以下字段（如果实际字段名不同，对应替换即可）：

• group_key：用于区分不同分组的标识（对应示例中的key:"A"）
• name：患者姓名
• colored：颜色标记
• avatar_url：头像地址
• subtitle：职位/副标题
• patientid：患者ID

实现代码 #

场景1：仅生成单个分组（如key="A"） #

SELECT 
    'A' AS [key], -- 可直接指定分组key，也可从表中读取对应字段
    (
        SELECT 
            name,
            colored,
            avatar_url,
            subtitle,
            patientid
        FROM patient_data
        WHERE group_key = 'A' -- 过滤出该分组的患者数据
        FOR JSON PATH -- 生成内层data数组
    ) AS data
FOR JSON PATH, ROOT('patient') -- 生成外层patient数组

场景2：生成多个分组（自动根据表中group_key分组） #

如果你的表中有多个分组（比如key="A"、key="B"等），可以用关联子查询实现自动分组：

SELECT 
    pd1.group_key AS [key],
    (
        SELECT 
            name,
            colored,
            avatar_url,
            subtitle,
            patientid
        FROM patient_data pd2
        WHERE pd2.group_key = pd1.group_key -- 关联外层分组
        FOR JSON PATH
    ) AS data
FROM patient_data pd1
GROUP BY pd1.group_key -- 按分组key去重，避免重复生成分组项
FOR JSON PATH, ROOT('patient')

关键细节说明 #

• 关键字转义：因为key是SQL的保留关键字，所以需要用方括号[key]来标识字段名，确保生成的JSON键正确。
• JSON PATH语法：FOR JSON PATH会自动将查询结果转换为JSON数组，嵌套使用就能实现多级结构。
• ROOT选项：ROOT('patient')会将最终的JSON结果包裹在patient根数组中，完全匹配你需要的格式。

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