SQL Server 如何构造符合指定输出格式的自定义JSON对象

核心问题：你需要的JSON顶级键为动态的语言编码值，SQL Server 原生FOR JSON语法不直接支持将行字段值作为对象的键名，因此需要通过字符串拼接+子查询的方式构造目标结构。

调整后的查询语句 #

DECLARE @JsonData NVARCHAR(MAX)

-- 先构造每个语言对应的键值对，再拼接成完整JSON
SELECT @JsonData = N'{' + STRING_AGG(
    QUOTENAME(LangCode, '"') + N':[' + PageJson + N']',
    N','
) + N'}'
FROM (
    -- 按语言分组，生成每个语言下所有页面的JSON内容
    SELECT 
        linfo.Code AS LangCode,
        (
            SELECT 
                -- 构造页面级的键值对：页面名作为键，对应翻译键值对作为值
                JSON_QUERY(N'{' + STRING_AGG(
                    QUOTENAME(RTE.Name, '"') + N':' + KeyValueJson,
                    N','
                ) + N'}') AS PageContent
            FROM ResourceTranslationEntity RTE
            CROSS APPLY (
                -- 生成单页面下所有翻译键值对的JSON
                SELECT N'{' + STRING_AGG(
                    QUOTENAME(RT.[key], '"') + N':' + QUOTENAME(RTV.Value, '"'),
                    N','
                ) + N'}' AS KeyValueJson
                FROM ResourceTranslation RT
                INNER JOIN ResourceTranslationValues RTV ON RT.Id = RTV.ResourceTranslationId
                WHERE RT.ResourceTranslationEntityId = RTE.Id 
                    AND RTV.LanguageInfoId = linfo.Id
            ) PageKeys
            GROUP BY RTE.Name
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        ) AS PageJson
    FROM LanguageInfo linfo
    WHERE linfo.Id IN (1,2)
    GROUP BY linfo.Id, linfo.Code
) LangJson

SELECT @JsonData AS [Data]

关键调整点说明 #

• 使用STRING_AGG拼接动态键值对，解决FOR JSON不支持动态键的问题
• 用QUOTENAME对键名、文本值加引号，避免JSON格式错误
• 加入WITHOUT_ARRAY_WRAPPER参数去掉不需要的数组外层括号
• 用JSON_QUERY标记内部JSON片段，避免生成时被自动转义

如果你的SQL Server版本低于2017（不支持STRING_AGG），可以改用FOR XML PATH的方式实现字符串拼接。

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