主页 / dba / 问题 / 295836
Accepted
HeyJude
Asked: 2021-07-19 04:58:26 +0800 CST

使用新的 json 值更新 nvarchar 列，而不在新值中添加引号

  • 772

我有下表，其中[Data]是类型nvarchar(max)，它的值是有效的 json 字符串：

ID 数据
1 { "info" : { "done": true, "chosenOptions": ["one", "two"] } }
2 { "info" : { "done": true, "chosenOptions": ["one"] } }
3 { "info" : { "done": true, "chosenOptions": [] } }
4 { "info" : { "done": true } }

我想更新所有Data不包含chosenOptions或 where chosenOptionsis 的行[]。

  • 假设每个Datajson 值都有几个其他属性，而不是此处显示的属性，如果这很重要的话。

我的查询：

declare @defaultValue Nvarchar(100);
set @defaultValue = JSON_QUERY('["one", "two"]')    

update myTable
set [Data] = JSON_QUERY(JSON_MODIFY([Data], '$.info.chosenOptions', @defaultValue))
where JSON_QUERY([data], '$.info.chosenOptions') IS NULL or 
      JSON_QUERY([data], '$.info.chosenOptions') = '[]'

结果chosenOptions值是字符串而不是数组（请忽略数组值的转义，这在这里不太重要）：

ID 数据
1 { "info" : { "done": true, "chosenOptions": ["one", "two"] } }
2 { "info" : { "done": true, "chosenOptions": ["one"] } }
3 { "info" : { "done": true, "chosenOptions": "[\"one\", \"two\"]" } }<-- 注意它是一个字符串，而不是数组
4 { "info" : { "done": true, "chosenOptions": "[\"one\", \"two\"]" } }<-- 注意它是一个字符串，而不是数组

我认为这是一种预期的行为，但是如何正确更新该数组？

sql-server json

1 个回答

  1. Best Answer

    JSON_QUERYwith no path 用于防止转义有效的 JSON 字符串。您需要将其直接放在JSON_MODIFY调用中以使其正常工作，不能将其放在函数参数之外。

    文档中指出了这一点：

    JSON_MODIFY如果值的类型是NVARCHAR或，则转义新值中的所有特殊字符VARCHAR。FOR JSON如果文本值是由、JSON_QUERY或生成的格式正确的 JSON，则不会对其进行转义JSON_MODIFY。

    您还可以通过将 JSONWHERE放入CROSS APPLY. 不要试图为JSON_MODIFY参数尝试这个，正如我提到的那样它不起作用

    declare @defaultValue nvarchar(100) = '["one", "two"]';

update t
set [Data] =
  JSON_MODIFY([Data], '$.info.chosenOptions', JSON_QUERY(@defaultValue))
from myTable t
cross apply (values(
  JSON_QUERY([Data], '$.info.chosenOptions')
)) v(array)
where v.array IS NULL or v.array = '[]';

    SQL小提琴

    • 1

相关问题