次の方法で共有

Injecting JSON objects into existing JSON text with JSON_MODIFY function

  • [アーティクル]

JSON_MODIFY function that is available in SQL Server 2016+ enables you to modify existing text formatted as JSON and add or update existing values. One common problem that happens is inserting unexpected value while trying to update existing JSON text and inject JSON object in the text. In this post I will show you one common mistake that happens when people are using JSON_MODIFY and the solution of the problem.

Problem

Recently I found this question on stack overflow. The problem was in appending a new JSON object to the existing JSON array:

 UPDATE TheTable
SET TheJSON = JSON_MODIFY(TheJSON, 'append $', N'{"id": 3, "name": "Three"}')
WHERE Condition = 1;

JSON_MODIFY function should take the array value from TheJSON column (the first argument), append the third argument into the first argument, and write the appended array back in TheJSON column.

However, the unexpected results in this case is the fact that JSON_MODIFY didn't appended a JSON object {"id": 3, "name": "Three"} to the array. Instead, JSON_MODIFY appended a new JSON string literal  "{\"id\": 3, \"name\": \"Three\"}" to the end of the array.

This might be surprising result if you don't know how JSON_MODIFY function works.

Explanation

JSON_MODIFY function see the third argument as NVARCHAR literal, and assumes that you want to add a new string to the end of the array. Therefore, it will take this string, escape all special characters such as backslashes, and append the new string value to the existing array. This is expected behavior, because JSON_MODIFY cannot know is the content in your string really JSON or just a plain text. In theory, SQL Server could parse the third argument to determine is it properly formatted JSON, but we are not making that kind of guesses.

If you provide a string literal as the third parameter it will be always treated and injected as JSON string literal.

Solution

The only way to append a JSON object to the existing JSON array is to "tell" JSON_MODIFY function that the input string is properly formatted/valid JSON fragment. The following functions/operators in SQL Server return valid JSON:

  • FOR JSON clause if it is used without WITHOUT_ARRAY_WRAPPER option
  • JSON_QUERY function
  • JSON_MODIFY function

If you use any of these operators in the third argument of JSON_MODIFY function, JSON_MODIFY will know that it should not treat the result of these operators as plain text and it will inject the JSON object as-is.

In the example above, solution would be:

 UPDATE TheTable
SET TheJSON = JSON_MODIFY(TheJSON, 'append $', JSON_QUERY(N'{"id": 3, "name": "Three"}'))
WHERE Condition = 1;

In this case JSON_QUERY behaves as CAST to JSON because it will just return the original value, but SQL Server will known that it is properly formatted JSON, and it will not escape characters and insert string literal.

Conclusion

If you want to set, update or append JSON object values using JSON_MODIFY - beware that you need to specify that the third parameter is valid JSON if you want to avoid escaping. This is a common error that I find when the people are working with JSON_MODIFY function.