In my previous blog posts, we learned about the ISJSON() , JSON_VALUE() and JSON_QUERY() functions in details.
ISJSON() – Verifies that the input text has valid JSON data
JSON_VALUE() – Extracts scalar value from the JSON text.
JSON_QUERY() – Extracts an object or array from the JSON text.
However there might be scenarios where you might need to manipulate/modify the JSON string. In SQL Server 2016, there is a new function named JSON_MODIFY which will assist you to modify the value of a property in the JSON string and return the modified string.
Let us now see a demonstration of the JSON_MODIFY() function –
| — JSON_MODIFY Demonstration to update the value of a property in JSON string | |
| DECLARE @json nvarchar(max) | |
| SET @json =N' | |
| { | |
| "Person": [ | |
| { | |
| "Name": { | |
| "FirstName": "Catherine", | |
| "LastName": "Abel" | |
| }, | |
| "Address": { | |
| "State": "VA", | |
| "Zip": "24153" | |
| } | |
| } | |
| ] | |
| }' | |
| PRINT @json | |
| IF (ISJSON(@json) = 1) | |
| PRINT 'It is a Valid JSON' –Valid JSON | |
| ELSE | |
| PRINT 'It is an Invalid JSON string' | |
| PRINT JSON_MODIFY (@json, '$.Person[0].Address.Zip' , '35043'); | |
| PRINT @json |
When you execute the query above, you would find that the JSON_MODIFY function will successfully modify the Zip field of the Person object from 24153 to 35043 —
With this blog post, I have completed writing about the 4 new JSON functions introduced in SQL Server 2016.
Stay tuned to learn about more features of SQL Server 2016 and how it has been nothing less than a revelation in itself.
Categories: SQL Server, SQL Server 2016
My PASS Summit 2017 Experience 
PASS Summit Day 1 Microsoft Keynote – Recap 
STRING_SPLIT function in SQL Server 2016 
Compress/Decompress Functions and Performance considerations 
Leave a Reply