求SQL Azure中从JSON路径表构建JSON对象的高效实现方案

针对你的需求，这里提供一个替代WHILE循环的原生SQL方案，完全满足业务要求且性能更优：

实现步骤 #

1. 筛选每个JSON路径的最新记录 #

用窗口函数ROW_NUMBER()按路径分组，只保留ID最大的记录，解决重复路径的覆盖问题：

WITH LatestEntries AS (
    SELECT 
        JsonPath,
        JsonValue,
        -- 按路径分组，ID降序排序，取第一条即为最新数据
        ROW_NUMBER() OVER (PARTITION BY JsonPath ORDER BY ID DESC) AS RowRank
    FROM [dbo].[JsonData]
)
SELECT JsonPath, JsonValue
INTO #TempLatest
FROM LatestEntries
WHERE RowRank = 1;

2. 批量生成JSON_MODIFY调用链 #

利用STRING_AGG()把所有修改操作拼接成连续的JSON_MODIFY语句，一次性生成最终JSON：

DECLARE @FinalJson NVARCHAR(MAX) = N'{}';
DECLARE @ModifyStmt NVARCHAR(MAX);

-- 拼接所有JSON修改操作
SELECT @ModifyStmt = STRING_AGG(
    CONCAT(
        'JSON_MODIFY(', 
        QUOTENAME(@FinalJson, ''''), 
        ', ', QUOTENAME(JsonPath, ''''), 
        ', ', 
        -- 判断值类型：原生JSON直接传入，字符串添加引号
        CASE WHEN ISJSON(JsonValue) = 1 THEN JsonValue ELSE QUOTENAME(JsonValue, '''') END,
        ')'
    ),
    ' '
)
FROM #TempLatest;

-- 执行拼接后的语句，生成最终JSON
EXEC sp_executesql 
    N'SELECT @Result = ' + @ModifyStmt,
    N'@Result NVARCHAR(MAX) OUTPUT',
    @Result = @FinalJson OUTPUT;

-- 输出结果
SELECT @FinalJson AS GeneratedJson;

DROP TABLE #TempLatest;

业务要求适配说明 #

• 支持所有合法JSON路径：JSON_MODIFY支持的路径格式（包括嵌套对象、数组索引等）都能直接处理，拼接过程完整保留路径语法。
• 重复路径取最新数据：通过窗口函数提前过滤，每个路径仅保留ID最大的记录，避免重复覆盖。
• 原生类型存储：用ISJSON()判断值类型，JSON格式的值（数字、布尔、嵌套JSON等）直接传入，字符串则添加引号，确保生成的JSON使用正确原生类型。
• 高效处理100-500行数据：STRING_AGG和窗口函数均为SQL Azure原生高效函数，避免WHILE循环的逐行开销，处理500行数据的性能远优于循环方案，且无需递归。

补充说明 #

如果JSON路径层级固定且简单，也可尝试OPENJSON配合PIVOT实现，但对于任意深度的动态路径，上述拼接JSON_MODIFY的方案通用性更强，完全覆盖需求。

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