如何在SQL Server中使用FOR JSON PATH生成指定结构自定义JSON

你需要的JSON结构核心是存在两层动态属性键（外层的A/B/C分组键、内层的01/02/03条目键），原生FOR JSON PATH无法直接通过静态列别名生成动态键，需要结合字符串聚合+JSON_QUERY避免JSON转义来实现，具体步骤如下：

前置说明 #

• 示例基于SQL Server 2017及以上版本（支持STRING_AGG），2016版本可替换为FOR XML PATH字符串拼接方案，后文会给出兼容写法
• 所有拼接生成的JSON片段必须用JSON_QUERY包裹，否则FOR JSON会自动转义双引号，把嵌套JSON输出为普通字符串
• 用STRING_ESCAPE处理键名中的特殊字符，避免生成非法JSON

步骤1：准备测试源表 #

假设你的业务数据存储结构如下，可根据实际表名/字段名替换：

CREATE TABLE #SourceData (
    GroupKey VARCHAR(10), -- 对应外层A/B/C类的分组键
    ItemKey VARCHAR(10),  -- 对应分组内01/02/03类的条目键
    ID INT,
    Name VARCHAR(50)
)
-- 插入测试数据
INSERT INTO #SourceData VALUES
('A','01',1,'Test'),
('A','02',2,'Test2'),
('A','03',3,'Test3'),
('B','01',4,'Test4'),
('B','02',5,'Test5'),
('B','03',3,'Test3')

步骤2：完整SQL实现 #

直接执行以下SQL即可输出符合要求的JSON结构：

SELECT N'{' + STRING_AGG(
    N'"' + STRING_ESCAPE(GroupKey,'json') + N'":' + GroupValue,
    N','
) + N'}' AS ResultJson
FROM (
    -- 按分组聚合内部条目，生成每个分组对应的数组结构
    SELECT
        GroupKey,
        JSON_QUERY(N'[' + STRING_AGG(
            N'{"' + STRING_ESCAPE(ItemKey,'json') + N'":' + 
            (SELECT ID, Name FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
            N','
        ) + N']') AS GroupValue
    FROM #SourceData
    GROUP BY GroupKey
) t
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

低版本兼容（SQL Server 2016 无STRING_AGG） #

将子查询中的STRING_AGG替换为FOR XML PATH拼接逻辑即可：

SELECT N'{' + STUFF((
    SELECT N',"' + STRING_ESCAPE(GroupKey,'json') + N'":' + GroupValue
    FROM (
        SELECT
            GroupKey,
            JSON_QUERY(N'[' + STUFF((
                SELECT N',{"' + STRING_ESCAPE(ItemKey,'json') + N'":' + 
                (SELECT ID, Name FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
                FROM #SourceData s2
                WHERE s2.GroupKey = s1.GroupKey
                FOR XML PATH(''), TYPE
            ).value('.','NVARCHAR(MAX)'),1,1,'') + N']') AS GroupValue
        FROM #SourceData s1
        GROUP BY GroupKey
    ) t2
    FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,'') + N'}' AS ResultJson
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

输出效果 #

执行后生成的合法JSON结构如下（和你预期结构一致，修正了你示例中多余逗号的语法错误）：

{
    "A": [
        {"01": {"ID":1,"Name":"Test"}, "02": {"ID":2,"Name":"Test2"}, "03": {"ID":3,"Name":"Test3"}}
    ],
    "B": [
        {"01": {"ID":4,"Name":"Test4"}, "02": {"ID":5,"Name":"Test5"}, "03": {"ID":3,"Name":"Test3"}}
    ]
}

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