Modify a JSON key value based on another key's value in SQL Server

Question

I have a table with a column containing JSON objects. Within these objects, I need to locate the key type with a value of 3. Once found, I need to update the key price with a new value of 99 in the nested JSON Object data. The price for the other types should remain unchanged. I would greatly appreciate any assistance. Thank you!

-- Create the new table
CREATE TABLE ModifiedTable (
 accountIdentifier UNIQUEIDENTIFIER,
 settings NVARCHAR(MAX)
);
-- Insert the modified values
INSERT INTO ModifiedTable (accountIdentifier, settings)
Values (
 '8E9B45D7-8AEC-EA11-8B03-000D3A12F259',
 '[{"type":3,"data":{"required":false,"price":0.5,"display_name":false}},{"type":5,"data":{"required":true,"scaling_factor":2.5, "price":1,"date_format":"yyyy-MM-dd"}}]'
),
(
 'C03D12B1-8BEC-EA11-8B03-000D3A12F259',
 '[{"type":7,"data":{"required":true,"scaling_factor":1.75,"tooltip":"Sample tooltip", "price":1}},{"type":4,"data":{"required":false,"scaling_factor":1.2,"char_limit":50,"multi_line":true}},{"type":3,"data":{"required":false,"price":0.7,"display_name":false}}]'
);
-- Select from the modified table
SELECT * FROM ModifiedTable;
--drop table ModifiedTable

Answer 1

You need to find the path using calls to OPENJSON and/or JSON_VALUE.

Then you can use JSON_MODIFY to change the object. Note that this only works when you need to change one proprty per row.

UPDATE mt
SET settings = JSON_MODIFY(mt.settings, '$[' + j.[key] + '].data.price', 99)
FROM ModifiedTable mt
CROSS APPLY (
 SELECT array.[key]
 FROM OPENJSON(mt.settings) array
 WHERE JSON_VALUE(array.value, '$.type') = '3'
) j;

db<>fiddle

If you need to change multiple values per row then you need to rebuild the whole JSON.

Unfortunately, SQL Server does not support JSON_AGG so you need to use STRING_AGG instead.

UPDATE mt
SET settings = '[' + j.json + ']'
FROM ModifiedTable mt
CROSS APPLY (
 SELECT
 json = STRING_AGG(
 IIF(JSON_VALUE(array.value, '$.type') = '3',
 JSON_MODIFY(array.value, '$.data.price', 99),
 array.value
 ),
 ','
 ) WITHIN GROUP (ORDER BY CAST(array.[key] AS int))
 FROM OPENJSON(mt.settings) array
) j;

db<>fiddle

• Erik Reasonable Rates Darling: WAY TO GO CHARLIE!
• Charlieface: Annoys me that there is no JPath support, this should be as easy as SET settings = JSON_MODIFY(mt.settings, '$[*] ? ($.type == 3) .data.price', 99) or like XQuery SET settings.modify('replace value of (/root[type/text() = "3"]/data/price)[1] with "0.99"')