How can I use JSON_MODIFY to update all elements in an array that match a some criteria

Question

I've got a table with a json column and that json contains an array of items. I want to update some of those items where a given criteria is met. So the path to the array is $.activities. And where type equals multichoice, I want to set data.insightsTitle to data.title.

There doesn't seem to be anyway to query the path (like good ol' xpath) so I've been scratching my head a bit.

Answer 1

I came up with this solution, seems to work and isn't too gross! It needs a loop as it is writing to the json column for each of the array items that matches.

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

So if you dont specify WITH you get this magic Key column which gives you the index and the full json for the array item. you then update just that block and the use they key to update the entire json string one by one for each matching array item. Enjoy.