SQL Server能否跨层级更新指定属性的JSON元素？

可以实现。通过递归CTE遍历JSON的所有层级定位目标节点，再结合JSON_MODIFY逐个更新，全程使用SQL Server原生工具，不需要扩展。

示例场景 #

假设我们有如下JSON，包含3个name为target_name的节点，分别在根节点、数组、深层嵌套结构中：

{
  "name": "target_name",
  "value": 1,
  "items": [
    {
      "name": "other",
      "value": 2
    },
    {
      "name": "target_name",
      "value": 5
    }
  ],
  "nested": {
    "inner": {
      "name": "target_name",
      "value": 7,
      "extra": "data"
    }
  }
}

实现代码 #

-- 初始化测试JSON变量
DECLARE @json NVARCHAR(MAX) = N'{
  "name": "target_name",
  "value": 1,
  "items": [
    {
      "name": "other",
      "value": 2
    },
    {
      "name": "target_name",
      "value": 5
    }
  ],
  "nested": {
    "inner": {
      "name": "target_name",
      "value": 7,
      "extra": "data"
    }
  }
}';

-- 递归CTE遍历所有层级，找出符合条件的节点路径和当前值
WITH recursive_json AS (
    -- 根节点层级筛选
    SELECT
        CAST('$' AS NVARCHAR(MAX)) AS path,
        value AS json_value,
        JSON_VALUE(value, '$.name') AS node_name,
        JSON_VALUE(value, '$.value') AS node_value
    FROM OPENJSON(@json)
    WHERE JSON_VALUE(value, '$.name') = 'target_name'

    UNION ALL

    -- 递归遍历子节点（处理嵌套对象和数组）
    SELECT
        CAST(
            rj.path + 
            CASE 
                WHEN ISJSON(j.value) = 1 THEN '.' + j.[key] 
                ELSE '' 
            END 
            AS NVARCHAR(MAX)
        ) AS path,
        j.value AS json_value,
        JSON_VALUE(j.value, '$.name') AS node_name,
        JSON_VALUE(j.value, '$.value') AS node_value
    FROM recursive_json rj
    CROSS APPLY OPENJSON(rj.json_value) j
    WHERE ISJSON(j.value) = 1
    AND JSON_VALUE(j.value, '$.name') = 'target_name'
)
-- 临时存储所有需要修改的节点信息
SELECT path, node_value INTO #temp_paths FROM recursive_json;

-- 游标循环更新每个目标节点的value值
DECLARE @path NVARCHAR(MAX), @current_value INT;
DECLARE path_cursor CURSOR FOR SELECT path, node_value FROM #temp_paths;

OPEN path_cursor;
FETCH NEXT FROM path_cursor INTO @path, @current_value;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 对目标节点的value执行加1操作
    SET @json = JSON_MODIFY(@json, CONCAT(@path, '.value'), @current_value + 1);
    FETCH NEXT FROM path_cursor INTO @path, @current_value;
END;

CLOSE path_cursor;
DEALLOCATE path_cursor;

-- 输出更新后的JSON
SELECT @json AS updated_json;

-- 清理临时表
DROP TABLE #temp_paths;

代码说明 #

1. 递归CTE遍历：recursive_json会逐层扫描JSON结构，收集所有name属性等于target_name的节点，记录它们的JSON路径（比如$、$.items[1]、$.nested.inner）和当前的value值。
2. 游标循环更新：因为JSON_MODIFY每次只能修改一个节点，所以用游标逐个读取路径，对每个节点的value执行加1操作。
3. 原生工具依赖：全程使用OPENJSON、JSON_VALUE、JSON_MODIFY等SQL Server原生函数，满足Azure环境无扩展的限制。

注意事项 #

• 确保目标节点都是包含name和value属性的JSON对象，否则JSON_VALUE无法正确解析。
• 如果JSON中有大量符合条件的节点，游标可能会有一定性能开销，适合中小规模的JSON数据处理。
• 递归CTE会自动处理数组和嵌套对象，无论目标节点在哪个层级都能被定位到。

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