JSON Support Is The Best New Developer Feature in SQL 2016 — Part 3: Updating, Adding, and Deleting JSON
This is the third article in my series about learning how to use SQL Server 2016’s new JSON functions. If you haven’t already, you can read Part 1 — Parsing JSON and Part 2 — Creating JSON.
So far we’ve looked at how to parse existing JSON objects and how to create new JSON objects. Today I want to look at the easy ways to modify JSON objects as well as the (mostly) easy ways to delete elements from a JSON object.
SQL Server 2016 offers us the new JSON_MODIFY() function for updating existing JSON strings. It’s pretty simple to use for replacing existing values in a JSON string:
The JSON_MODIFY() function works similar to a SQL REPLACE() function: the first argument specifies what data we are modifying, the second argument selects which property we are going to replace via XPath syntax, and the third argument specifies what we are replacing the value with. Pretty easy!
Replacing values with JSON is simple. Adding new values into existing JSON is also fairly simple using JSON_MODIFY():
Replacing data and adding new data is pretty easy with JSON_MODIFY(). This new SQL Server 2016 function ain’t no two trick pony though — it allows deletion of data as well!
Deleting properties in a JSON object is fairly straightforward: all you have to do is run the function with the same arguments as our modification example, except this time passing in NULL as our replacement value:
Up to this point, JSON_MODIFY() has worked great for modifying, adding to, and deleting properties from our JSON data. However, there is one serious flaw with JSON_MODIFY() and that’s deleting JSON array values — instead of deleting the value from the array and then shifting the rest of the array over, it simply replaces the array value with a NULL:
I hate that solution. FOR XML PATH() to rebuild or JSON array? Ugly, ugly, ugly. I have been impressed with all of the new JSON functionality in SQL Server 2016 except for the deletion of array elements.
Deleting properties should be the same as deleting array elements with JSON_MODIFY(): the property and array element should be completely removed from the JSON object, not just replaced with a NULL. I opened a Microsoft Connect issue for this bug here, please vote for it if you want to see this problem fixed as well: https://connect.microsoft.com/SQLServer/feedback/details/3120404
Microsoft’s been pretty good about fixing bugs lately, so let’s hope this gets fixed in SQL Server vNext!
Have a better workaround for deleting array elements completely from a JSON object in SQL Server 2016? Please let me know!
