Using the new preview modify keyword to change JSON

This page has 0 threads | Add post

Blogs

Many people around the world enjoy and benefit from our blogs. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

Some other pages relevant to these blogs include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

Using JSON in SQL Server, including the new SQL 2025 functions Part eight of an eight-part series of blogs
SQL Server 2025 introduces a new JSON data type and some new JSON functions - we thought we'd use this as an excuse to create this masterclass on working with JSON in SQL Server JSON, SQL Server and the example for this series of blogs Loading JSON data from our Star Wars example API Storing JSON in a table using the new SQL Server 2025 json data type Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY) Extracting data from JSON - part 2 (using OPENJSON) Converting JSON data into a SQL Server table Joining T-SQL and JSON data Using the new preview modify keyword to change JSON (this blog)

Using JSON in SQL Server, including the new SQL 2025 functions

Part eight of an eight-part series of blogs

SQL Server 2025 introduces a new JSON data type and some new JSON functions - we thought we'd use this as an excuse to create this masterclass on working with JSON in SQL Server

JSON, SQL Server and the example for this series of blogs
Loading JSON data from our Star Wars example API
Storing JSON in a table using the new SQL Server 2025 json data type
Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY)
Extracting data from JSON - part 2 (using OPENJSON)
Converting JSON data into a SQL Server table
Joining T-SQL and JSON data
Using the new preview modify keyword to change JSON (this blog)

Posted by Andy Brown on 27 November 2025

Although it's apparently currently in preview, SQL Server 2025 now allows you to modify JSON. This is easiest to show by example. Suppose we have our standard list of films:

The second column contains the JSON for the films.

This is how the JSON starts:

We want to change the director of the first film in the results section of the JSON to Bobby the Owl.

Here's some SQL to do this:

-- change the director of the first film to Bobby the Owl

UPDATE tblFilm

SET FilmJson.modify('$.results[0].director','Bobby the Owl')

-- prove this worked

SELECT

tblFilm.FilmId,

f.Title,

f.Director

FROM

tblFilm

CROSS APPLY OPENJSON(FilmJson, '$.results')

WITH(

Title nvarchar(200) '$.title',

Director nvarchar(200) '$.director'

) AS f

And here's what this would then show:

The director of the first film has been changed.

You can specify any valid Json path as an argument to the modify method.

Parts of this blog
JSON, SQL Server and the example for this series of blogs Loading JSON data from our Star Wars example API Storing JSON in a table using the new SQL Server 2025 json data type Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY) Extracting data from JSON - part 2 (using OPENJSON) Converting JSON data into a SQL Server table Joining T-SQL and JSON data Using the new preview modify keyword to change JSON (this blog)

Parts of this blog