如何批量更新SQL中包含特定JSON数据的行？

1. 明确JSON键路径的场景 #

如果清楚password键在JSON结构里的具体路径（比如顶级键$.password，或是嵌套子键$.account.credentials.password），直接结合UPDATE和JSON_MODIFY就能批量处理符合条件的行：

UPDATE MyDatabase
SET RouteValues = JSON_MODIFY(RouteValues, '$.password', '*****')
WHERE RouteValues LIKE '%password%'

如果密码键嵌套在子结构中（例如JSON为{"account": {"username": "johndoe", "password": "johndoePassword"}}），只需调整路径参数即可：

UPDATE MyDatabase
SET RouteValues = JSON_MODIFY(RouteValues, '$.account.password', '*****')
WHERE RouteValues LIKE '%password%'

2. 不确定密码键具体路径的场景 #

如果JSON结构不固定，密码键可能出现在不同层级，可分两步处理：

第一步：排查所有包含密码的JSON路径

SELECT 
    Id,
    [key] AS PasswordKey,
    path AS PasswordPath
FROM MyDatabase
CROSS APPLY OPENJSON(RouteValues)
WHERE [key] LIKE '%password%'

第二步：根据查到的路径批量更新

如果存在多个不同路径，可通过动态SQL批量执行更新：

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql += N'
UPDATE MyDatabase
SET RouteValues = JSON_MODIFY(RouteValues, ''' + REPLACE(PasswordPath, '''', '''''') + ''', ''*****'')
WHERE Id = ' + CAST(Id AS NVARCHAR(10)) + ';'
FROM (
    SELECT DISTINCT Id, path AS PasswordPath
    FROM MyDatabase
    CROSS APPLY OPENJSON(RouteValues)
    WHERE [key] LIKE '%password%'
) AS PasswordPaths

EXEC sp_executesql @sql

验证建议 #

执行更新前，建议先通过SELECT语句确认修改效果，避免误操作：

SELECT 
    Id,
    RouteValues AS OriginalJSON,
    JSON_MODIFY(RouteValues, '$.password', '*****') AS ModifiedJSON
FROM MyDatabase
WHERE RouteValues LIKE '%password%'

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