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

Joins When No Join Types Are Valid

Hugo Kornelis has a brain-teaser for us: The query below can be executed in any version of the AdventureWorks sample database. Don’t bother understanding the logic, there is none. It is merely constructed to show how SQL Server handles what appears to be an impossible situation. 1 2 3 4 SELECT          d1.Name, d2.GroupName FROM            HumanResources.Department AS d1 FULL OUTER […]

Read More

Implementation Matters: CTEs In Postgres And SQL Server

Brent Ozar looks at a couple of places where Postgres and SQL Server differ in implementation details: In SQL Server, if you write this query: 1 2 3 4 With AllPosts AS (SELECT * FROM StackOverflow.dbo.Posts) SELECT *   FROM AllPosts   WHERE Id = 1; SQL Server builds a query plan for the entire operation at […]

Read More


December 2017
« Nov Jan »