SQL Server JSON_MODIFY function is a powerful tool that allows you to modify JSON data stored in a column of a SQL Server table. This function was introduced in SQL Server 2016 and is part of the JSON functions family that was added to support the storage, manipulation, and querying of JSON data in SQL Server.

The JSON_MODIFY function takes three parameters: the JSON expression to modify, the JSON path to the element to modify, and the new value to replace the existing value with. The JSON path parameter is used to specify the location of the element that needs to be modified in the JSON expression. This path can include wildcards, indexes, and nested elements.

Syntax

Here is the basic syntax of the JSON_MODIFY function:

JSON_MODIFY ( expression , path , newValue )

Example

Let's consider an example where we have a table called Employee with a JSON column called PersonalInfo that contains the following JSON data:

CREATE TABLE table Employee
(EmployeeID INT, PersonalInfo NVARCHAR(max));

INSERT INTO Employee(EmployeeID, PersonalInfo) VALUES
(1, '{
"firstName":"John",
"lastName":"Doe",
"age":30,
"address":{
"street":"123 Main St",
"city":"Anytown",
"state":"CA",
"zip":"12345"
},
"phoneNumbers":[
{
"type":"home",
"number":"555-555-1212"
},
{
"type":"work",
"number":"555-555-2121"
}
]
}');

To modify the value of the firstName element from "John" to "Jane", we can use the following query:

UPDATE Employee
SET PersonalInfo = JSON_MODIFY(PersonalInfo, '$.firstName', 'Jane')
WHERE EmployeeID = 1;

The result would be:

{
"firstName":"Jane",
"lastName":"Doe",
"age":30,
"address":{
"street":"123 Main St",
"city":"Anytown",
"state":"CA",
"zip":"12345"
},
"phoneNumbers":[
{
"type":"home",
"number":"555-555-1212"
},
{
"type":"work",
"number":"555-555-2121"
}
]
}

In addition to modifying the value of an existing element, the JSON_MODIFY function can also be used to add or delete elements from a JSON expression.

In conclusion, the JSON_MODIFY function in SQL Server is a valuable tool for modifying JSON data stored in a table column. It allows you to easily manipulate JSON data, add, modify or delete elements, making it easier to work with JSON data in SQL Server.