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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

JSON_QUERY with no path is used to prevent escaping of valid JSON strings. You need to place this directly within the JSON_MODIFY call for it to work properly, you cannot place it outside of the function parameter.

This is pointed out in the documentation:

JSON_MODIFY escapes all special characters in the new value if the type of the value is NVARCHAR or VARCHAR. A text value is not escaped if it is properly formatted JSON produced by FOR JSON, JSON_QUERY, or JSON_MODIFY.

You can also save on re-querying the JSON twice in the WHERE, by placing it in CROSS APPLY. Don’t be tempted to try this for the JSON_MODIFY parameter, it doesn’t work as I have mentioned

declare @defaultValue nvarchar(100) = '["one", "two"]';

update t
set [Data] =
  JSON_MODIFY([Data], '$.info.chosenOptions', JSON_QUERY(@defaultValue))
from myTable t
cross apply (values(
  JSON_QUERY([Data], '$.info.chosenOptions')
)) v(array)
where v.array IS NULL or v.array = '[]';

SQL Fiddle

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply