SQL Server多表关联如何生成指定嵌套结构的JSON输出

多表直接JOIN会产生笛卡尔积，FOR JSON AUTO模式按照JOIN关联顺序自动生成嵌套层级，无法将两个独立的一对多关系映射为平级的嵌套数组，最终导致输出数据重复、层级结构不符合预期。

使用关联子查询分别构造subjects和sports两个独立的嵌套数组，配合FOR JSON PATH模式自定义字段名和层级，即可避免笛卡尔积问题，输出符合要求的JSON结构。

完整SQL语句 #

SELECT
    s.id,
    s.name,
    -- 构造科目+分数的嵌套数组
    (
        SELECT
            su.subject AS [name],
            m.marks
        FROM subjects su
        INNER JOIN marks m 
            ON su.subjectId = m.subjectId 
            AND su.studentId = m.studentId
        WHERE su.studentId = s.id
        FOR JSON PATH
    ) AS subjects,
    -- 构造运动项目的嵌套数组
    (
        SELECT
            sp.name
        FROM sports sp
        WHERE sp.studentId = s.id
        FOR JSON PATH
    ) AS sports
FROM students s
WHERE s.id = 1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

语句说明 #

• 使用FOR JSON PATH替代AUTO模式，支持手动控制JSON字段映射与结构，不会按照JOIN顺序自动生成错误的嵌套层级
• 两个关联子查询分别独立查询当前学生对应的科目分数、运动项目数据，从根源避免多表JOIN产生的笛卡尔积数据重复问题
• 子查询中使用别名[name]，将原表的subject字段、sports.name字段映射为JSON要求的name键
• 外层查询添加WITHOUT_ARRAY_WRAPPER参数，去掉默认生成的外层方括号，直接输出单个学生的JSON对象，匹配预期结构

输出结果 #

执行语句后将得到完全符合要求的JSON：

{
    "id": 1,
    "name": "Rusty",
    "subjects": [
        {
            "name": "math",
            "marks": 50
        },
        {
            "name": "science",
            "marks": 60
        }
    ],
    "sports": [
        {
            "name": "soccer"
        },
        {
            "name": "baseball"
        }
    ]
}

如果需要批量查询多个学生的嵌套JSON，去掉外层的WITHOUT_ARRAY_WRAPPER参数即可，每个学生会作为数组中的独立对象，各自携带对应的subjects和sports数组，不会出现数据重复。

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