SQL Server 2019中如何将多表关联结果合并为结构化JSON输出

逐字段指定表名.字段别名时，FOR JSON PATH的路径解析逻辑不会自动合并跨位置的同名父节点：只要同属一个父对象的字段没有在SELECT列表中连续排列，引擎就会生成新的同名键，最终输出带重复键的非法JSON结构。
你期望的表.* AS '表.*'语法在SQL Server中不被直接支持，而JSON_OBJECT+JSON_ARRAY的简化写法是SQL Server 2022新增特性，2019版本无法使用。

该方案兼容SQL Server 2016及以上所有正式版本，无需逐一枚举字段，即可自动将单表所有字段嵌套到对应表名的JSON对象下：

SELECT 
    -- 嵌套tableOne所有字段为tableOne对象
    (SELECT tableOne.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS tableOne,
    -- 嵌套tableTwo所有字段为tableTwo对象
    (SELECT tableTwo.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS tableTwo,
    -- 关联更多表时按相同格式追加即可
    (SELECT tableThree.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS tableThree
FROM tableOne
INNER JOIN tableTwo 
  ON tableOne.fId = tableTwo.id
-- 最终输出结构化JSON
FOR JSON PATH;

方案说明 #

• 无需手动逐字段写别名，表.*会自动读取查询上下文中该表的所有返回字段，后续表结构新增/删除字段时无需修改JSON生成逻辑
• 子查询必须加WITHOUT_ARRAY_WRAPPER参数，否则子查询返回的单对象结果会被包裹在数组中，不符合结构预期
• 如果表之间是一对多关联（例如1条tableOne记录对应多条tableTwo记录），只需要去掉对应子查询的WITHOUT_ARRAY_WRAPPER参数，该字段会自动生成为嵌套的JSON数组，无需额外编写聚合逻辑

逐字段写法的修正方式 #

如果你坚持使用逐字段指定别名的写法，只需要保证同属一个表的字段在SELECT列表中连续排列，中间不穿插其他表的字段，即可避免重复键问题，示例如下：

SELECT 
-- tableOne的所有字段集中放在一起
tableOne.attrA AS 'tableOne.attrA',
tableOne.attrB AS 'tableOne.attrB',
-- tableTwo的所有字段集中放在一起，不要和tableOne的字段穿插
tableTwo.attrA AS 'tableTwo.attrA',
tableTwo.attrB AS 'tableTwo.attrB'
FROM tableOne
JOIN tableTwo 
  ON tableOne.fId = tableTwo.id
FOR JSON PATH

该写法维护成本较高，表结构变更时需要同步修改SQL，仅适合字段固定的简单查询场景。

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