SQL JSON update

Question

如果我有这个JSON

{
"hello1": "1",
"hello2": "2"
}

我想从这个JSON 中更新它

{
"hello2": "3"
}

我希望结果是

{
"hello1": "1",
"hello2": "3"
}

有没有一种方法可以做到这一点，而不需要遍历SQL中的每个字段？

我目前的尝试是手动更新每个值，如下所示：

SET @OldJSON = JSON_MODIFY(
@OldJSON,
'$.hello1', 
ISNULL(JSON_VALUE(@JSON,'$.hello1'), JSON_VALUE(@OldJSON,'$.hello1'))
)

Answer 1

获得预期结果的可能选项有：

• JSON_MODIFY()和SELECT语句
• JSON_MODIFY()与动态语句

JSON_MODIFY()和SELECT语句：

正如文档中所提到的，在SQL Server 2017(14.x(和Azure SQL数据库中，您可以提供一个变量(在这种情况下是一个表达式(作为path的值。因此，您可以轻松地为每个新的key/value对构建一条路径。

DECLARE @old varchar(1000) = '{"hello1":"1","hello2":"2","hello3":"3"}'
DECLARE @new varchar(1000) = '{"hello2":"a","hello3":"b"}'
SELECT @old = JSON_MODIFY(
@old,
CONCAT('$."', n.[key], '"'),
COALESCE(n.[value], o.[value])
)
FROM OPENJSON(@old) o
JOIN OPENJSON(@new) n ON n.[key] = o.[key]

JSON_MODIFY()和动态语句：

场景几乎相同，但每次更新都在单独的语句中，动态生成：

DECLARE @old varchar(1000) = '{"hello1":"1","hello2":"2","hello3":"3"}'
DECLARE @new varchar(1000) = '{"hello2":"a","hello3":"b"}'
DECLARE @stm nvarchar(max)
SELECT @stm = STRING_AGG(
CONCAT(
'SELECT @old = JSON_MODIFY(@old, ''$."', n.[key], '"'', ''',
COALESCE(n.[value], o.[value]), ''')'
),
'; '
)   
FROM OPENJSON(@old) o
JOIN OPENJSON(@new) n ON n.[key] = o.[key]
DECLARE @err int
EXEC @err = sp_executesql @stm, N'@old varchar(1000) OUTPUT', @old OUTPUT
IF @err = 0 PRINT 'Success' ELSE PRINT 'Error'

结果：

两种方法都返回以下JSON:

{"hello1":"1","hello2":"a","hello3":"b"}

添加新的keyvalue对：

如果新的JSON内容包含新的keyvalue对，只需使用FULL JOIN:

DECLARE @old varchar(1000) = '{"hello1":"1","hello2":"2","hello3":"3"}'
DECLARE @new varchar(1000) = '{"hello2":"a","hello4":"b"}'
SELECT @old = JSON_MODIFY(
@old,
CONCAT('$."', n.[key], '"'),
COALESCE(n.[value], o.[value])
)
FROM OPENJSON(@old) o
FULL JOIN OPENJSON(@new) n ON n.[key] = o.[key]