How do I use JSON_MODIFY to turn an int json value into a one-length array?

Question

We have grids that we have custom filters for and you can save the filters as a JSON object to a particular table, tblUserPersistedValue (along with a user field). So we are updating the page and the filters are going from single-select IDs to multi-select IDs on four different filters.

Curent JSON:

'{
"ProductGroupID":1,
"TerminalID":1,
"CarrierID":1221,
"RegionID":21,
"DriverDateFilter":0,
"DriverStartDate":"0001-01-01T00:00:00",
"DriverEndDate":"0001-01-01T00:00:00"
}';

Where You see "TerminalID":1, I would like to change it to "TerminalID":[1] so we don't break any user's settings when we make the update. Unfortunately i am unable to figure out how to create an int array with JSON_MODIFY. I am almost there, but it's wrapping the value i'm saving in quotes, and that won't parse into an int array during deserialization.

I have never worked with json directly in sql before and I must be missing something simple. How do i fix it? If it's impossible to overwrite the existing entry, I could also make a new TerminalIDs entry to parse into and remove the old one.

The ultimate goal is to do this for every record in the table, once for each of four fields.

Using only JSON functions and without string manipulations - SELECT @json = JSON_MODIFY(@json, '$.TerminalID', JSON_MODIFY(N'[]', 'append $', CONVERT(int, JSON_VALUE(@json, '$.TerminalID')))). — Zhorov
– Zhorov, Commented Apr 28, 2020 at 7:58

lptr · Accepted Answer · 2020-04-27 19:44:53Z

2

declare @j nvarchar(max) = N'{"ProductGroupID":1,"TerminalID":1,"CarrierID":1221,"RegionID":21,"DriverDateFilter":0,"DriverStartDate":"0001-01-01T00:00:00","DriverEndDate":"0001-01-01T00:00:00"}';
select @j = json_modify(@j, '$.TerminalID', json_query(concat('[', json_value(@j, '$.TerminalID') ,']')));
select @j;

answered Apr 27, 2020 at 19:44

lptr

6,8582 gold badges10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Lord Over a year ago

I just managed to get this to work using json_modify just as you answered this. You get the rep.

Collectives™ on Stack Overflow

How do I use JSON_MODIFY to turn an int json value into a one-length array?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related