如何在SQL中为指定JSON对象添加不存在的键值对

现有如下SQL变量@outputJson：

DECLARE @outputJson nvarchar(max)='[
  {
    "Name": "BSData",
    "Value": ""
  },
  {
    "Name": "ISData",
    "Value": "{\"WC\":{\"IL\":\"false\",\"SCB\":\"false\"},\"RC\":[{\"PrimaryKeys\":\"\",\"BackupMode\":\"NONE\"}],\"SIData\":{\"builderId\":\"4567\",\"column\":{\"EP_Key\":\"5678\"}}}"
  }
]'

需求：在@outputJson中，找到ISData对应Value里的column对象，若不存在Battery键则添加"Battery":"Epr Oxygen"。

之前的尝试代码错误地将Battery添加到了column对象外部，且生成了数组格式，核心问题是JSON_MODIFY的路径指定错误，且误用了append关键字（该关键字用于数组元素添加）。

正确实现代码 #

DECLARE @outputJson nvarchar(max)='[
  {
    "Name": "BSData",
    "Value": ""
  },
  {
    "Name": "ISData",
    "Value": "{\"WC\":{\"IL\":\"false\",\"SCB\":\"false\"},\"RC\":[{\"PrimaryKeys\":\"\",\"BackupMode\":\"NONE\"}],\"SIData\":{\"builderId\":\"4567\",\"column\":{\"EP_Key\":\"5678\"}}}"
  }
]'

SELECT
  j1.Name,
  Value = CASE 
    WHEN j1.Name = 'ISData' THEN
      -- 检查内层JSON的column对象是否存在Battery键，不存在则添加
      CASE WHEN JSON_VALUE(j1.Value, '$.SIData.column.Battery') IS NULL THEN
        JSON_MODIFY(j1.Value, '$.SIData.column.Battery', 'Epr Oxygen')
      ELSE
        j1.Value
      END
    ELSE
      j1.Value
    END
FROM OPENJSON (@outputJson)
WITH (
  Name nvarchar(100),
  Value nvarchar(max)
) j1
FOR JSON PATH;

代码说明 #

1. 键存在性检查：通过JSON_VALUE(j1.Value, '$.SIData.column.Battery')判断内层JSON的column对象中是否存在Battery键，返回NULL则表示不存在
2. 精准路径修改：使用JSON_MODIFY指定正确的嵌套路径$.SIData.column.Battery，直接添加键值对（无需append）
3. 重新生成JSON：通过FOR JSON PATH将处理后的结果重新组合为外层JSON数组，与原格式一致

执行后预期结果 #

[
  {
    "Name": "BSData",
    "Value": ""
  },
  {
    "Name": "ISData",
    "Value": "{\"WC\":{\"IL\":\"false\",\"SCB\":\"false\"},\"RC\":[{\"PrimaryKeys\":\"\",\"BackupMode\":\"NONE\"}],\"SIData\":{\"builderId\":\"4567\",\"column\":{\"EP_Key\":\"5678\",\"Battery\":\"Epr Oxygen\"}}}"
  }
]

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