arrays - 如何使用 JSON_MODIFY 更新数组中符合某些条件的所有元素

Question

我有一个包含 json 列的表，该 json 包含一个项目数组。我想更新其中一些满足给定条件的项目。所以数组的路径是 $.activities 并且 type 是 'multichoicei want to set 'data.insightsTitle to data .title.

似乎无论如何都无法查询路径(比如好的 ol' xpath)所以我一直在摸不着头脑。

Answer 1

我想出了这个解决方案，似乎可行而且不太恶心!它需要一个循环，因为它正在为每个匹配的数组项写入 json 列。

begin tran t1

DECLARE @Count int = -1

while @Count != 0
begin
update dp
    set definition = JSON_MODIFY(dp.definition, '$.activities[' + (CAST(src.[key] as nvarchar(6)) COLLATE SQL_Latin1_General_CP1_CI_AS) + ']', JSON_MODIFY(src.value, '$.data.insightsTitle', JSON_VALUE(src.value, '$.data.title')))
from
    deviceprofile dp
join (
select dp.deviceprofileid, g.*--dp.definition
from deviceprofile dp
CROSS APPLY OPENJSON(dp.definition, '$.activities') g
) src on dp.deviceprofileid = src.deviceprofileid
where JSON_VALUE(src.value, '$.type') = 'multichoice' and JSON_VALUE(src.value, '$.data.insightsTitle') is null

select @count = count(distinct dp.deviceprofileid)
from deviceprofile dp
CROSS APPLY OPENJSON(dp.definition, '$.activities') WITH (type nvarchar(100), insightsTitle nvarchar(256) '$.data.insightsTitle') g
where g.type = 'multichoice' and g.insightsTitle is null

print @count
end

rollback tran

因此，如果您不指定 WITH，您将获得这个神奇的 Key 列，它为您提供索引和数组项的完整 json。然后，您只更新该 block 并使用它们键为每个匹配的数组项一个一个地更新整个 json 字符串。享受吧。