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
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.  

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: