JSON_MODIFY FUNCTION IN SQL SERVER 2016


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.

JSON_MODIFY Function

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

JSON_MODIFY Function

With this blog post, I have completed writing about the 4 new JSON functions introduced in SQL Server 2016.  

JSON Functions 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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: