JSON_MODIFY FUNCTION IN SQL SERVER 2016

By Samir Behara on September 26, 2017 • ( 0 )

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

view raw JSON_MODIFY() Function in SQL Server 2016 hosted with ❤ by GitHub

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