NEW T-SQL FEATURES IN SQL SERVER 2016 – XVII | JSON_MODIFY()

In this post we shall check out the new features Microsoft introduced in SQL Server 2016 | JSON_MODIFY()

New feature – XVII | JSON_MODIFY()


This function is used to update the value of a property in a JSON string and returns the updated JSON string.

Syntax

--

JSON_MODIFY ( expression , path , newValue )   

--

Notes

1. JSON_MODIFY returns an error if expression doesn’t contain valid JSON.

2. JSON_MODIFY returns an error if the format of path isn’t valid.

3. The function returns a JSON fragment of type nvarchar(max).

4. It returns the updated value of expression as properly formatted JSON text.

Examples

/* Example 1 | Update the Name : Pawan to Name : PK */

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
PRINT @J

--Update command...JSON_MODIFY
SET @J=JSON_MODIFY(@j,'$.name','PK')
PRINT @J
GO

OUTPUT

Old Value 
--------------
{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"]
,"IsDatabaseDeveloper":true}

New Updated Value
---------------------
{"name":"PK","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"]
,"IsDatabaseDeveloper":true}


--

/* Example 2 – Insert another tag(json) | PrimarySkill : SQL */

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
PRINT @J
--Insert command...JSON_MODIFY
SET @J=JSON_MODIFY(@j,'$.PrimarySkill','SQL')
PRINT @J
GO

OUTPUT

Old Value 
--------------
{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"]
,"IsDatabaseDeveloper":true}

New Updated Value
--------------
{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true,"PrimarySkill":"SQL"}

--

/* Example 3 – Delete tag(json) | IsDatabaseDeveloper */

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
PRINT @J
--DELETE command...JSON_MODIFY
SET @J=JSON_MODIFY(@j,'$.IsDatabaseDeveloper',NULL)
PRINT @J
GO

OUTPUT

Old Value 
--------------
{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"]
,"IsDatabaseDeveloper":true}

New Updated Value
--------------

{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"]}


--

/* Example 4 – Add another value to SkillSet Lets say Power BI */

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
PRINT @J
--DELETE command...JSON_MODIFY
SET @J=JSON_MODIFY(@j,'append $.SkillSet','Power BI')
PRINT @J
GO

OUTPUT

Old Value 
--------------

{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}

New Updated Value
--------------

{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS","Power BI"],"IsDatabaseDeveloper":true}

--

/* Example 5 – Multiple operations – Change, Insert, Delete and Append */

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
PRINT @J


--Multiple Operations | Embed JSON_MODIFY inside JSON_MODIFY.
SET @J=JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(@j,'append $.SkillSet','Power BI')
					,'$.IsDatabaseDeveloper',NULL),'$.PrimarySkill','SQL'),'$.name','PK')
PRINT @J
GO

OUTPUT

Old Value 
--------------

{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"]
,"IsDatabaseDeveloper":true}


New Updated Value
--------------

{"name":"PK","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS","Power BI"]
,"PrimarySkill":"SQL"}

--

/* Example 6 – Update JSON Array or Object inside JSON string */

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
PRINT @J

SET @J=JSON_MODIFY(@j,'$.SkillSet','["Microsoft Data Platform","Microsoft Azure Platform"]')
PRINT @J
GO

OUTPUT

Old Value 
--------------

{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"]
,"IsDatabaseDeveloper":true}

New Updated Value
--------------

{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10
,"SkillSet":"[\"Microsoft Data Platform\",\"Microsoft Azure Platform\"]"
		,"IsDatabaseDeveloper":true}

--

/* Example 7 – Increase an Integer Value */

--

DECLARE @j NVARCHAR(MAX)
SET @j='{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"],"IsDatabaseDeveloper":true}';
PRINT @J

SET @J = JSON_MODIFY(@J, '$.Class',  JSON_VALUE(@J,'$.Class') +1 )
PRINT @J
GO

OUTPUT

Old Value 
--------------

{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":10,"SkillSet":["SQL","SSIS","SSRS","SSAS"]
,"IsDatabaseDeveloper":true}


New Updated Value
--------------
{"name":"Pawan","MiddleName":"Kumar","Surname":"Khowal","Class":11,"SkillSet":["SQL","SSIS","SSRS","SSAS"]
,"IsDatabaseDeveloper":true}

--

Refer Microsoft LINK below for more details-

1. https://docs.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com