sql server – Update an nvarchar column with a new json value, without adding quotes to the new value

I’ve got the following table, where (Data) is of type nvarchar(max) and its values are valid json strings:

Id Data
1 { "info" : { "done": true, "chosenOptions": ("one", "two") } }
2 { "info" : { "done": true, "chosenOptions": ("one") } }
3 { "info" : { "done": true, "chosenOptions": () } }
4 { "info" : { "done": true } }

I want to update all rows whose Data doesn’t include chosenOptions, or where chosenOptions is ().

  • Assume that each Data json value has several other properties than those shown here, if that matters.

My query:

declare @defaultValue Nvarchar(100);
set @defaultValue = JSON_QUERY('("one", "two")')    

update myTable
set (Data) = JSON_QUERY(JSON_MODIFY((Data), '$.info.chosenOptions', @defaultValue))
where JSON_QUERY((data), '$.info.chosenOptions') IS NULL or 
      JSON_QUERY((data), '$.info.chosenOptions') = '()'

The resultant chosenOptions value is a string instead of an array (please ignore the escaping of the array values, this is less important here):

Id Data
1 { "info" : { "done": true, "chosenOptions": ("one", "two") } }
2 { "info" : { "done": true, "chosenOptions": ("one") } }
3 { "info" : { "done": true, "chosenOptions": "("one", "two")" } } <– note that it’s a string, not array
4 { "info" : { "done": true, "chosenOptions": "("one", "two")" } } <– note that it’s a string, not array

I assume that it’s an expected behavior, but how can this array be updated properly?