By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table so75416277 (
id bigint not null primary key,
json nvarchar(max) not null
)
insert so75416277 values (1, '{
"HeaderInfo":
{
"Name": "ABC",
"Period": "2010",
"Code": "123"
},
"HData":
[
{ "ID1": "1", "Value": "$1.00", "Code": "A", "Desc": "asdf" },
{ "ID1": "2", "Value": "$1.00", "Code": "B", "Desc": "pqr" },
{ "ID1": "3", "Value": "$1.00", "Code": "C", "Desc": "xyz" }
]
}')
, (2, '{
"HeaderInfo":
{
"Name": "ABC",
"Period": "2010",
"Code": "123"
},
"HData":
[
{ "ID1": "76", "Value": "$1.00", "Code": "X", "Desc": "asdf" },
{ "ID1": "25", "Value": "$1.00", "Code": "Y", "Desc": "pqr" },
{ "ID1": "52", "Value": "$1.00", "Code": "Z", "Desc": "lmno" },
{ "ID1": "52", "Value": "$1.00", "Code": "B", "Desc": "xyz" }
]
}')
2 rows affected
declare @id bigint = 2
, @code nvarchar(8) = 'B'
, @value nvarchar(8) = '$2.00'
select *
from so75416277 a
CROSS APPLY OPENJSON (json, '$.HData') HData
CROSS APPLY OPENJSON (HData.Value, '$')
WITH (
ID1 bigint
, Value nvarchar(8)
, Code nvarchar(8)
, [Desc] nvarchar(8)
) as HDataItem
WHERE id = @id
AND HDataItem.Code = @Code
| id | json | key | value | type | ID1 | Value | Code | Desc |
|---|---|---|---|---|---|---|---|---|
| 2 | { "HeaderInfo": { "Name": "ABC", "Period": "2010", "Code": "123" }, "HData": [ { "ID1": "76", "Value": "$1.00", "Code": "X", "Desc": "asdf" }, { "ID1": "25", "Value": "$1.00", "Code": "Y", "Desc": "pqr" }, { "ID1": "52", "Value": "$1.00", "Code": "Z", "Desc": "lmno" }, { "ID1": "52", "Value": "$1.00", "Code": "B", "Desc": "xyz" } ] } |
3 | { "ID1": "52", "Value": "$1.00", "Code": "B", "Desc": "xyz" } | 5 | 52 | $1.00 | B | xyz |
declare @id bigint = 2
, @code nvarchar(8) = 'B'
, @value nvarchar(8) = '$2.00'
update a
set json = JSON_MODIFY(json, '$.HData[' + HData.[key] + '].Value', @value)
from so75416277 a
CROSS APPLY OPENJSON (json, '$.HData') HData
CROSS APPLY OPENJSON (HData.Value, '$')
WITH (
ID1 bigint
, Value nvarchar(8)
, Code nvarchar(8)
, [Desc] nvarchar(8)
) as HDataItem
WHERE id = @id
AND HDataItem.Code = @Code
1 rows affected
select * from so75416277
| id | json |
|---|---|
| 1 | { "HeaderInfo": { "Name": "ABC", "Period": "2010", "Code": "123" }, "HData": [ { "ID1": "1", "Value": "$1.00", "Code": "A", "Desc": "asdf" }, { "ID1": "2", "Value": "$1.00", "Code": "B", "Desc": "pqr" }, { "ID1": "3", "Value": "$1.00", "Code": "C", "Desc": "xyz" } ] } |
| 2 | { "HeaderInfo": { "Name": "ABC", "Period": "2010", "Code": "123" }, "HData": [ { "ID1": "76", "Value": "$1.00", "Code": "X", "Desc": "asdf" }, { "ID1": "25", "Value": "$1.00", "Code": "Y", "Desc": "pqr" }, { "ID1": "52", "Value": "$1.00", "Code": "Z", "Desc": "lmno" }, { "ID1": "52", "Value": "$2.00", "Code": "B", "Desc": "xyz" } ] } |