By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Declare @ouptputJson nvarchar(max)='[
{
"Name": "BSData",
"Value": ""
},
{
"Name": "ISData",
"Value": "{\"WC\":{\"IL\":\"false\",\"SCB\":\"false\"},\"RC\":[{\"PrimaryKeys\":\"\",\"BackupMode\":\"NONE\"}],\"SIData\":{\"builderId\":\"4567\",\"column\":{\"EP_Key\":\"5678\"}}}"
}
]';
SELECT j1.Name,
Value = CASE WHEN j1.Name = 'ISData'
THEN JSON_MODIFY(j1.value, '$.SIData.column.Battery', 'Epr Oxygen')
ELSE j1.Value
END
FROM OPENJSON (@ouptputJson)
WITH (
Name nvarchar(100),
Value nvarchar(max)
) j1
FOR JSON PATH;
| JSON_F52E2B61-18A1-11d1-B105-00805F49916B |
|---|
| [{"Name":"BSData","Value":""},{"Name":"ISData","Value":"{"WC":{"IL":"false","SCB":"false"},"RC":[{"PrimaryKeys":"","BackupMode":"NONE"}],"SIData":{"builderId":"4567","column":{"EP_Key":"5678","Battery":"Epr Oxygen"}}}"}] |