See sample below. How would I use JSON_MODIFY or other way to modify all the "disc" values to "100" without having to update each array item in a loop?
create table #temp_data (json_text nvarchar(max))
insert into #temp_data select
'
"curr":"USD",
"items":[
{
"line":1,
"disc":10,
},
{
"line":2,
"disc":11
},
{
"line":3,
"disc":12,
}
]
}'
select * from #temp_data
We don't actually need to parse the rest of the JSON, we only need the $.items part. So we can APPLY that property with OPENJSON, then reassemble it with JSON_MODIFY:
UPDATE t
SET json_text = JSON_MODIFY(t.json_text, '$.items', v.items)
FROM temp_data t
CROSS APPLY (
SELECT line, disc = 100
FROM OPENJSON(t.json_text, '$.items') WITH (line int) AS items
FOR JSON PATH
) v(items);
In the WITH block, we need to add all properties we are not modifying, then in the inner SELECT we add any columns we want to change.
One way would be to use the traditional SQL DML UPDATE to replace the json_text with a new piece of JSON derived from the existing. Something like this
JSON_MODIFY could be added if that's a requirement.
drop table if exists #temp_data;
go
create table #temp_data (json_text nvarchar(max));
insert into #temp_data(json_text) values(
N'{
"curr":"USD",
"items":[
{
"line":1,
"disc":10
},
{
"line":2,
"disc":11
},
{
"line":3,
"disc":12
}
]
}');
update #temp_data
set json_text=( select json_value(d.json_text, N'strict $.curr') curr,
(select j2.line, j2.disc+100 as disc
from #temp_data td
cross apply openjson(td.json_text)
with(curr nvarchar(20),
items nvarchar(max) as json) j1
cross apply openjson(j1.items)
with(line int,
disc int) j2
for json path) items
from #temp_data d
for json path, without_array_wrapper);
select * from #temp_data;
{
"curr": "USD",
"items": [
{
"line": 1,
"disc": 210
},
{
"line": 2,
"disc": 211
},
{
"line": 3,
"disc": 212
}
]
}
It's not possible to use a wildcard in a JSON_MODIFY() call, so one possible approach are the following steps:
$.items array from the stored JSON using OPENJSON() and explicit schema including the line key.disc key for each item of this array.FOR JSON PATH.JSON_MODIFY().Table:
create table #temp_data (json_text nvarchar(max))
insert into #temp_data (json_text)
VALUES (N'
{
"curr":"USD",
"items":[
{"line":1, "disc":10},
{"line":2, "disc":11},
{"line":3, "disc":12}
]
}')
Statement:
UPDATE #temp_data
SET json_text = JSON_MODIFY(
json_text,
'$.items',
(
SELECT line, 100 AS disc
FROM OPENJSON(json_text, '$.items') WITH (line int '$.line')
FOR JSON PATH
)
)
Result:
json_text
{
"curr":"USD",
"items":[{"line":1,"disc":100},{"line":2,"disc":100},{"line":3,"disc":100}]
}