如何用SQL单查询批量更新同行JSON多路径的status值

需要更新SQL表中某一行的JSON数据，将所有primary的status字段改为PASSED。

目标JSON结构 #

{
  "secondaries": [
    {
      "secondaryId": 1,
      "primaries": [
        {
          "primary": 1,
          "status": "UNKNOWN"
        },
        {
          "primary": 2,
          "status": "UNKNOWN"
        }
      ]
    }
  ]
}

SQL表创建及插入语句 #

CREATE TABLE testing(
   id VARCHAR(100),
   json nvarchar(max)
);

INSERT INTO testing values('123', '{"secondaries":[{"secondaryId":1,"primaries":[{"primary":1,"status":"UNKNOWN"},{"primary":2,"status":"UNKNOWN"}]}]}');

尝试的方案 #

先创建CTE获取所有需要更新的JSON路径：

with cte as (select id,
                      CONCAT('$.secondaries[', t.[key], ']', '.primaries[', t2.[key],
                             ']')  as primaryPath
               from testing
                        cross apply openjson(json, '$.secondaries') as t
                        cross apply openjson(t.value, '$.primaries') as t2
               where id = '123'
               and json_value(t.value, '$.secondaryId') = 1
)
select * from cte;

CTE能正确返回所有目标路径，但用以下语句更新时，仅其中一个primary的status被修改：

with cte as (select id,
                      CONCAT('$.secondaries[', t.[key], ']', '.primaries[', t2.[key],
                             ']')  as primaryPath
               from testing
                        cross apply openjson(json, '$.secondaries') as t
                        cross apply openjson(t.value, '$.primaries') as t2
               where id = '123'
               and json_value(t.value, '$.secondaryId') = 1
)
update testing
set json = JSON_MODIFY(json, cte.primaryPath + '.status', 'PASSED')
from testing
cross join cte 
where cte.id = testing.id;

select * from testing;

目前已有基于游标的可行方案，但希望用单条SQL查询实现，请问该如何操作？

游标方案代码 #

OPEN @getid
FETCH NEXT
FROM @getid INTO @id, @primaryPath
WHILE @@FETCH_STATUS = 0
    BEGIN
        update testing
        set json = JSON_MODIFY(json, @primaryPath + '.status', 'PASSED')
        where testing.id = @id

        FETCH NEXT
            FROM @getid INTO @id, @primaryPath
    END

CLOSE @getid
DEALLOCATE @getid

问题出在你的UPDATE语句中，cross join会让同一行数据被多次更新，但每次JSON_MODIFY都是基于原始的JSON值进行修改，而非上一次修改后的结果，所以最终只有最后一次修改生效。

要实现单条SQL完成所有更新，你可以通过重新构造整个JSON对象的方式来实现，具体SQL语句如下：

UPDATE testing
SET json = (
    SELECT 
        s.secondaryId,
        (
            SELECT 
                p.primary,
                'PASSED' AS status
            FROM OPENJSON(s.primaries)
            WITH (
                primary INT '$.primary',
                status NVARCHAR(50) '$.status'
            ) p
            FOR JSON PATH
        ) AS primaries
    FROM OPENJSON(json, '$.secondaries')
    WITH (
        secondaryId INT '$.secondaryId',
        primaries NVARCHAR(MAX) '$.primaries' AS JSON
    ) s
    WHERE s.secondaryId = 1
    FOR JSON PATH, ROOT('secondaries')
)
WHERE id = '123';

语句说明 #

• 外层通过OPENJSON解析secondaries数组，提取secondaryId和primaries（保留JSON格式）
• 内层对每个secondary的primaries数组再次解析，将status固定为PASSED后用FOR JSON PATH重新生成数组
• 最后通过FOR JSON PATH, ROOT('secondaries')重新组合成完整的JSON结构，覆盖原字段

这种方式一次性完成所有status的修改，无需多次调用JSON_MODIFY，也避免了游标循环的开销。

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