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 –
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
— 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
Leave a Reply