如何在存储过程中使用FOR JSON PATH生成指定嵌套JSON数组

你需要的动态键名JSON结构无法直接通过原生FOR JSON PATH自动生成——FOR JSON PATH/AUTO的输出键名默认绑定查询列名，属于固定值，需要通过字符串拼接配合FOR JSON PATH生成子节点的方式实现，以下是可直接使用的存储过程代码。

存储过程代码（适配SQL Server 2017及以上、Azure SQL Database，与Azure Logic App兼容性最优） #

CREATE OR ALTER PROCEDURE [dbo].[usp_GenerateStudentResultJson]
AS
BEGIN
    SET NOCOUNT ON;

    -- 拼接生成最终根级JSON数组
    SELECT CONCAT('[', STRING_AGG(SingleStudentNode, ','), ']') AS ResultJson
    FROM (
        -- 按学生ID分组，生成每个学生对应的独立JSON对象
        SELECT 
            CONCAT(
                '{"',
                StudentID,
                '":',
                -- 用FOR JSON PATH生成当前学生的科目-成绩数组
                (
                    SELECT
                        -- 统一科目名格式：首字母大写、其余小写，匹配示例输出格式
                        CONCAT(UPPER(LEFT(subject, 1)), LOWER(SUBSTRING(subject, 2, LEN(subject)))) AS subject,
                        result
                    FROM [dbo].[TempStudentJsonData] s_inner
                    WHERE s_inner.StudentID = s_outer.StudentID
                    FOR JSON PATH
                ),
                '}'
            ) AS SingleStudentNode
        FROM [dbo].[TempStudentJsonData] s_outer
        GROUP BY StudentID
    ) AS StudentNodes
END
GO

调用方式 #

直接在Logic App的SQL连接器操作中执行该存储过程即可，返回的ResultJson字段就是符合要求的JSON字符串：

EXEC [dbo].[usp_GenerateStudentResultJson]

注意事项 #

• 你给出的期望JSON示例存在字段名不一致问题：学号506995节点下成绩字段为result，506996、506997节点下写为reason，上述代码统一使用result作为字段名，避免下游Logic App解析逻辑出错。如果确实需要保留reason字段，可以在内层子查询中通过条件判断分别输出不同别名，不过非常不建议设计这种非统一结构的JSON。
• 如果你的数据库版本低于SQL Server 2017（不支持STRING_AGG函数），可以将根数组拼接逻辑替换为FOR XML PATH实现，核心子节点生成逻辑不变，替换后的查询片段如下：

SELECT CONCAT('[', STUFF(
    (
        SELECT ',' + SingleStudentNode
        FROM (
            SELECT 
                CONCAT(
                    '{"',StudentID,'":',
                    (
                        SELECT CONCAT(UPPER(LEFT(subject, 1)), LOWER(SUBSTRING(subject, 2, LEN(subject)))) AS subject, result
                        FROM [dbo].[TempStudentJsonData] s_inner
                        WHERE s_inner.StudentID = s_outer.StudentID
                        FOR JSON PATH
                    ),'}'
                ) AS SingleStudentNode
            FROM [dbo].[TempStudentJsonData] s_outer
            GROUP BY StudentID
        ) t
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
), ']') AS ResultJson

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