使用T-SQL的JSON_MODIFY按筛选条件修改JSON指定键值对

问题背景 #

数据库表中存在JSON类型列，存储结构示例如下（注意同数字键12345在不同FilterType下重复属于业务设计，不是冗余数据）：

{
  "Id": 123,
  "Filters": [
    {
      "FilterType": "Category",
      "Values": {
        "23098": "Power Tools",
        "12345": "Groceries"
      }
    },
    {
      "FilterType": "Distributor",
      "Values": {
        "98731": "Acme Distribution",
        "12345": "Happy Star Supplies"
      }
    }
  ]
}

需求为将所有FilterType = 'Category'的数组块下，键12345对应的原值Groceries统一替换为Foodstuffs，严禁修改Distributor块下同名键关联的值，同时需要兼容存在N个符合条件的Category块的场景。

核心瓶颈说明 #

• SQL Server原生JSON路径不支持按数组元素属性做条件过滤（即不支持$.Filters[?(@.FilterType='Category')]类的路径语法），无法直接在JSON_MODIFY中通过单一路径定位目标节点
• 同名数字键跨FilterType重复，必须严格按父级FilterType做修改范围隔离

可直接使用的T-SQL实现 #

-- 请将YourTable、JsonData替换为实际业务中的表名、JSON列名，WHERE后拼接你已经实现的目标记录筛选逻辑
UPDATE YourTable
SET JsonData = JSON_MODIFY(
    JsonData,
    '$.Filters',
    JSON_QUERY(
        (
            SELECT
                CASE
                    -- 仅当当前筛选块类型为Category、且存在12345键时才做值替换
                    WHEN JSON_VALUE(filterElem.value, '$.FilterType') = 'Category'
                        AND JSON_VALUE(filterElem.value, '$.Values."12345"') IS NOT NULL
                    THEN JSON_MODIFY(filterElem.value, '$.Values."12345"', 'Foodstuffs')
                    -- 其余所有块（包括Distributor、无12345键的Category块）全部原封不动保留
                    ELSE filterElem.value
                END
            FROM OPENJSON(JsonData, '$.Filters') AS filterElem
            -- 按原数组下标排序，保证重组后的数组顺序和原始数据完全一致
            ORDER BY filterElem.[key]
            FOR JSON PATH
        )
    )
)
WHERE
    -- 此处粘贴你已经写好的目标数据筛选条件，避免全表更新
    1=1

关键逻辑说明 #

• 借助OPENJSON拆分Filters数组为逐行元素，不需要硬编码数组下标，自动适配任意数量的数组元素
• 修改前双重校验元素属性，从逻辑上完全隔离非Category类型的块，不会误改Distributor下的同名键值
• 路径中"12345"加双引号是因为键名为数字开头，必须显式包裹才能被JSON引擎正确识别
• 外层JSON_QUERY用于标记返回内容为JSON结构化数据，避免FOR JSON生成的数组被转义为普通字符串
• 可在执行UPDATE前先用SELECT替换UPDATE部分，预览修改结果是否符合预期，示例校验语句：

SELECT
    JSON_VALUE(JsonData, '$.Filters[0].Values."12345"') AS Category12345Value,
    JSON_VALUE(JsonData, '$.Filters[1].Values."12345"') AS Distributor12345Value
FROM YourTable
-- 你的筛选条件

正常修改后，Category对应值为Foodstuffs，Distributor对应值仍为Happy Star Supplies。

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