2

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?

1 Answer 1

2

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.