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
Leave a Reply