如何在SQL中更新JSON字符串内的指定字段值？

现有如下JSON字符串：

[
  {
    "Name": "TGName",
    "Value": ""
  },
  {
    "Name": "ISD",
    "Value": "{\"WC\":{\"IL\":\"false\",\"SCB\":\"false\"},\"RC\":[{\"PrimaryKeys\":\"\",\"BackupMode\":\"NONE\"}],\"SIData\":{\"builderId\":\"4567\",\"row\":{\"Category\":\"Epr Oxygen\",\"EP_Key\":\"5678\"}}}"
  }
]

需要将其中Category键的值从"Epr Oxygen"更新为"Damper"。

解决方案 #

在SQL Server中，可以通过JSON_MODIFY函数结合JSON解析逻辑完成嵌套字段的更新，以下是完整实现代码：

DECLARE @inputWiCate nvarchar(max)='[
  {
    "Name": "TGName",
    "Value": ""
  },
  {
    "Name": "ISD",
    "Value": "{\"WC\":{\"IL\":\"false\",\"SCB\":\"false\"},\"RC\":[{\"PrimaryKeys\":\"\",\"BackupMode\":\"NONE\"}],\"SIData\":{\"builderId\":\"4567\",\"row\":{\"Category\":\"Epr Oxygen\",\"EP_Key\":\"5678\"}}}"
  }
]'

-- 1. 提取ISD对应的嵌套JSON内容
DECLARE @isdNestedJson nvarchar(max)
SELECT @isdNestedJson = Value
FROM OPENJSON(@inputWiCate)
WITH (Name nvarchar(100), Value nvarchar(max))
WHERE Name = 'ISD'

-- 2. 更新嵌套JSON中的Category值
SET @isdNestedJson = JSON_MODIFY(
    @isdNestedJson,
    '$.SIData.row.Category',
    'Damper'
)

-- 3. 将更新后的嵌套JSON替换回原数组
SET @inputWiCate = JSON_MODIFY(
    @inputWiCate,
    '$.Name="ISD".Value',
    @isdNestedJson
)

-- 输出验证结果
PRINT @inputWiCate

关键说明 #

• JSON_MODIFY是SQL Server修改JSON的核心函数，支持通过嵌套路径（如$.SIData.row.Category）定位目标字段，直接替换为新值。
• 先分离出ISD对应的嵌套JSON进行修改，再替换回原数组，避免直接操作复杂嵌套结构时的路径定位错误。
• 如果需要将结果持久化到表中，只需将最终的@inputWiCate值写入目标表的对应字段即可。

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