Using JSON_MODIFY() to Update Nested JSON in SQL Server

by

Working with JSON data in SQL Server has become quite common as more applications are relying heavily on semi-structured data. When your JSON includes nested objects or arrays, it’s useful to know how to make precise updates without rewriting the entire document. Fortunately, SQL Server makes this relatively simple with the JSON_MODIFY() function. This function lets you update specific values inside JSON text stored in a table, even deep within the structure.

In this article, we’ll walk through an example of how to use SQL Server’s JSON_MODIFY() function to update nested JSON.

Sample Data

We’ll start by creating a table that holds JSON data representing product information. Each product has basic details, a list of suppliers, and a nested “specs” object:

-- Create a table to store product data
CREATE TABLE Products (
    ProductID INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(100),
    ProductData NVARCHAR(MAX)
);
GO

-- Insert sample JSON data
INSERT INTO Products (ProductName, ProductData)
VALUES
('Smartwatch', 
 N'{
    "category": "Wearables",
    "price": 249.99,
    "specs": {
        "battery": "18 hours",
        "waterResistant": true,
        "sensors": ["heartRate", "gps", "accelerometer"]
    },
    "suppliers": [
        {"name": "TechSupply Co", "rating": 4.6},
        {"name": "Global Distributors", "rating": 4.3}
    ]
 }'),
('Gaming Laptop',
 N'{
    "category": "Computers",
    "price": 1499.99,
    "specs": {
        "cpu": "Intel i9",
        "ram": "32GB",
        "gpu": "RTX 4080"
    },
    "suppliers": [
        {"name": "ProHardware Ltd", "rating": 4.8},
        {"name": "IT World", "rating": 4.4}
    ]
 }');
GO

Now we’ve now got a Products table with a JSON column (ProductData) that contains structured data for each product.

Note: While SQL Server 2025 recently added a native JSON type, I’ve used NVARCHAR(MAX) in these examples. This keeps the code backward compatible, so it’ll work whether you’re on the latest version or an older one.

Updating a Nested JSON Property

Let’s say we want to update the battery life of the Smartwatch from “18 hours” to “24 hours”. The battery field lives inside the nested specs object, so we’ll need to target that path specifically.

Let’s start by selecting the value:

SELECT JSON_VALUE(ProductData, '$.specs.battery')
FROM Products
WHERE ProductName = 'Smartwatch';

Result:

18 hours

Here, we used the JSON_VALUE() function to select the existing value.

Now let’s switch to JSON_MODIFY() and update that value:

UPDATE Products
SET ProductData = JSON_MODIFY(ProductData, '$.specs.battery', '24 hours')
WHERE ProductName = 'Smartwatch';

That’s it. One line, and SQL Server updates only the nested field we targeted. The rest of the JSON structure remains untouched.

To confirm, we can run the earlier query again:

SELECT JSON_VALUE(ProductData, '$.specs.battery')
FROM Products
WHERE ProductName = 'Smartwatch';

Result:

24 hours

It has been updated as specified.

So we use dot notation (in this case $.specs.battery) to target the specific value that we want to update. It’s the same as when selecting the value.

Updating an Array Element

We can use the same principle to update arrays. Suppose we want to change the rating of the first supplier for the Gaming Laptop. Since suppliers is an array, we’ll need to use the array syntax for target that field. Arrays use zero-based indexing, and so we’ll need to use 0 to reach the right element.

Let’s start by selecting the value:

SELECT JSON_VALUE(ProductData, '$.suppliers[0].rating')
FROM Products
WHERE ProductName = 'Gaming Laptop';

Output:

4.8

OK, now let’s change that value and check it again:

-- Update the value
UPDATE Products
SET ProductData = JSON_MODIFY(ProductData, '$.suppliers[0].rating', 4.9)
WHERE ProductName = 'Gaming Laptop';

-- Select the updated value
SELECT JSON_VALUE(ProductData, '$.suppliers[0].rating')
FROM Products
WHERE ProductName = 'Gaming Laptop';

Output:

4.9

Done.

Adding a New Property

JSON_MODIFY() can also add properties that don’t exist yet. For example, let’s add a "stock" property at the root level:

UPDATE Products
SET ProductData = JSON_MODIFY(ProductData, '$.stock', 120)
WHERE ProductName = 'Smartwatch';

Output:

(1 row affected)

If the stock key doesn’t already exist, SQL Server creates it. This is a nice way to evolve your JSON schema without restructuring your data manually.

Let’s check our Smartwatch entry:

SELECT ProductData
FROM Products
WHERE ProductName = 'Smartwatch';

Result:

{
    "category": "Wearables",
    "price": 249.99,
    "specs": {
        "battery": "24 hours",
        "waterResistant": true,
        "sensors": [
            "heartRate",
            "gps",
            "accelerometer"
        ]
    },
    "suppliers": [
        {
            "name": "TechSupply Co",
            "rating": 4.6
        },
        {
            "name": "Global Distributors",
            "rating": 4.3
        }
    ],
    "stock": 120
}

Now it has the stock field as specified.

A Few Things to Note

  • JSON paths in SQL Server always start with $ to refer to the root of the JSON document.
  • You can navigate objects using dot notation ($.specs.battery) and arrays using brackets ($.suppliers[1]).
  • If a specified path doesn’t exist, SQL Server will create it (unless the missing node is part of an array index that’s out of range).
  • JSON_MODIFY() only returns the modified JSON text. It doesn’t automatically persist it unless you use it in an UPDATE statement.