如何调整SQL语句将查询结果转换为指定结构的嵌套JSON格式

SQL Server的FOR JSON语法本身不支持直接将行值转换为JSON对象的键，因此你需要先拼接出符合要求的EndOfMonthData结构，再通过JSON_QUERY标记为原生JSON对象避免转义，完整实现代码如下：

WITH cte AS (
    SELECT CenterId, EOMONTH(MIN(Change_date)) AS EOM_Date, EOMONTH(MAX(Change_date)) AS finish
    FROM #tempCenters
    GROUP BY CenterId
    
    UNION ALL 
    
    SELECT CenterId, EOMONTH(DATEADD(MONTH, 1, EOM_Date)), finish
    FROM cte
    WHERE EOM_Date < finish
),
-- 预处理得到每个中心每个月末的会员数，同时把日期转成ISO格式字符串作为JSON键
cte_monthly_member AS (
    SELECT DISTINCT 
        cte.CenterId, 
        FIRST_VALUE(Members) OVER(PARTITION BY cte.CenterId, cte.EOM_Date ORDER BY tc.Change_date DESC) AS Members, 
        CONVERT(VARCHAR(10), cte.EOM_Date, 23) AS EOM_Date_Str
    FROM cte 
    LEFT JOIN #tempCenters tc ON cte.CenterId = tc.CenterId AND cte.EOM_Date >= tc.Change_date
)
-- 按中心分组拼接EndOfMonthData的JSON结构
SELECT 
    CenterId AS centerId,
    JSON_QUERY(
        '{' + STRING_AGG(QUOTENAME(EOM_Date_Str, '"') + ':' + CAST(Members AS DECIMAL(18,2)), ',') + '}'
    ) AS EndOfMonthData
FROM cte_monthly_member
GROUP BY CenterId
ORDER BY CenterId
FOR JSON PATH, ROOT('data')

兼容低版本SQL Server（2016及以下，无STRING_AGG函数） #

如果使用的SQL Server版本不支持STRING_AGG，可以替换成FOR XML PATH方式拼接字符串：

WITH cte AS (
    SELECT CenterId, EOMONTH(MIN(Change_date)) AS EOM_Date, EOMONTH(MAX(Change_date)) AS finish
    FROM #tempCenters
    GROUP BY CenterId
    
    UNION ALL 
    
    SELECT CenterId, EOMONTH(DATEADD(MONTH, 1, EOM_Date)), finish
    FROM cte
    WHERE EOM_Date < finish
),
cte_monthly_member AS (
    SELECT DISTINCT 
        cte.CenterId, 
        FIRST_VALUE(Members) OVER(PARTITION BY cte.CenterId, cte.EOM_Date ORDER BY tc.Change_date DESC) AS Members, 
        CONVERT(VARCHAR(10), cte.EOM_Date, 23) AS EOM_Date_Str
    FROM cte 
    LEFT JOIN #tempCenters tc ON cte.CenterId = tc.CenterId AND cte.EOM_Date >= tc.Change_date
)
SELECT 
    c1.CenterId AS centerId,
    JSON_QUERY(
        '{' + STUFF((
            SELECT ',' + QUOTENAME(EOM_Date_Str, '"') + ':' + CAST(Members AS DECIMAL(18,2))
            FROM cte_monthly_member c2
            WHERE c2.CenterId = c1.CenterId
            ORDER BY EOM_Date_Str
            FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)'), 1, 1, '') + '}'
    ) AS EndOfMonthData
FROM cte_monthly_member c1
GROUP BY c1.CenterId
ORDER BY c1.CenterId
FOR JSON PATH, ROOT('data')

核心逻辑说明 #

• 用QUOTENAME(EOM_Date_Str, '"')给日期字符串加双引号，保证JSON键的合法性
• 拼接后的键值对字符串通过JSON_QUERY包裹，告知FOR JSON该内容为原生JSON结构，不会被转义
• 输出结果完全符合你提供的目标JSON结构要求

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