JSON_MODIFY


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.

Talk to us?

Post your blog