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

Charlieface · Accepted Answer · 2023-05-18 00:34:23Z

3

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

answered May 18, 2023 at 0:34

Charlieface

18.1k22 silver badges45 bronze badges

WAY TO GO CHARLIE!

Erik Darling
– Erik Darling

2023-05-18 00:42:50 +00:00
Commented May 18, 2023 at 0:42
2

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"')

Charlieface
– Charlieface

2023-05-18 00:49:37 +00:00
Commented May 18, 2023 at 0:49

Add a comment |

Stack Exchange Network

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

1 Answer 1

Your Answer

Hot Network Questions

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

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions