Using JSON_MODIFY To Modify Existing JSON

Jovan Popovic shows off the JSON_MODIFY function in SQL Server:

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

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.

Read on to see how JSON_MODIFY works and why this doesn’t quite do what the poster thought.

Related Posts

Window Functions In WHERE Clauses

Shane O’Neill covers an annoying but necessary thing to remember around window functions: Now, a new learner of SQL comes along with the requirement to find the last 2 rows per PartitionId. They are diligent and enthusiastic and have just read about Windows Functions. They think to themselves Wow! This is great! I can do […]

Read More

Query Tuning With The APPLY Operator

Daniel Janik walks through using the APPLY operator to tune a couple of queries: Recently we were doing a project that heavily focused on query tuning and many tables had various outer joins. My co-worker pointed out that many of these could be converted to an apply rather than a join. Apply gives you both […]

Read More


December 2017
« Nov Jan »