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.
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.
