The JSON_MODIFY function in SQL Server is used to modify a value in a JSON string and return the updated JSON string. It allows you to update specific properties or elements within a JSON document without having to completely rewrite the entire JSON structure.
Here is a detailed example to illustrate the usage of JSON_MODIFY:
Suppose we have a table named Employee with a column Details that stores JSON data.
Let's say we want to update the salary of an employee with EmployeeID = 1.
-- Sample JSON data in the Employee table
CREATE TABLE Employee (
EmployeeID INT,
Details NVARCHAR(MAX)
);
INSERT INTO Employee (EmployeeID, Details)
VALUES (1, '{"name": "John Doe", "salary": 50000, "department": "IT"}');
-- Update the salary using JSON_MODIFY
UPDATE Employee
SET Details = JSON_MODIFY(Details, '$.salary', 60000)
WHERE EmployeeID = 1;
In this example, the JSON_MODIFY function updates the salary property of the JSON object where EmployeeID = 1 to 60000. The '$' signifies the root of the JSON object, and $.salary specifies the path to the salary property within the JSON structure.
Remember, JSON_MODIFY is just one of the many JSON functions available in SQL Server for handling JSON data efficiently. It provides a powerful way to manipulate JSON documents directly within SQL queries.