Modify JSON data using JSON

After discussing about how MS SQL Server can help us to format MS SQL data in JSON format and how to read JSON data in MS SQL Server, in this article we will see how we can modify JSON data in MS SQL Server using JSON_MODIFY function.

JSON_MODIFY is a newly added function in MS SQL Server 2016 with the purpose to modify JSON data. It can be used for:

updating the value of an existing property,
add a new element to an existing array,
insert a new property and its value and finally
delete a property

Syntax:

JSON_MODIFY (expression, path, newValue)

expression - An expression. Typically the name of a variable or a column that contains JSON text
path - A JSON path expression that specifies the property to update
newValue - The new value for the property specified by path

path has the following syntax:

[append] [lax | strict] $.<json path>

append - Optional modifier that specifies that the new value should be appended to the array referenced by <json path>.
Lax - Specifies that the property referenced by <json path> does not have to exist. If the property is not present, JSON_MODIFY tries to insert the new value on the specified path. Insertion may fail if the property can't be inserted on the path. If you don't specify lax or strict, lax is the default mode.
Strict - Specifies that the property referenced by <json path> must be in the JSON expression. If the property is not present, JSON_MODIFY returns an error.
<json path> - Specifies the path for the property to update.

The function returns the updated value of expression as properly formatted JSON text.

Adding a new JSON property

In few sequential examples we will use the following JSON value to see how we can add new JSON properties:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "John Doe",
"URL": "http:\/\/www.microsoft.com"
}';

Let’s try to add a new node “Currently presenting” with a Boolean value meaning if the speaker is currently presenting.

Example:

SELECT JSON_MODIFY(@JSON_data, '$."Currently presenting"', 1)
/*
{
    "Name": "John Doe",
    "URL": "http:\/\/www.microsoft.com",
    "Currently presenting": 1
}*/

The result is a new node added to our document but notice that the data type in the JSON document isn’t a Boolean value like we were hooping. The resulting data type is a numeric value. The problem is that we didn’t exactly specified (cast) the value and the SQL engine concluded that the input value is a number. Let’s cast the value in Boolean data type and see the result.

Example:

SELECT JSON_MODIFY(@JSON_data, '$."Currently presenting"', CAST(1 AS BIT))
/*
{
    "Name": "John Doe",
    "URL": "http:\/\/www.microsoft.com",
    "Currently presenting": true
}*/

Now the result value is “true”, and this means that we managed to get Boolean value as we wanted from the beginning. Let’s use the strict option and try to accomplish the same thing.

Example:

SELECT JSON_MODIFY(@JSON_data, 'strict $."Currently presenting"', CAST(1 AS BIT))

Since we used the strict option and we tried to modify a non-existing node named “Currently presenting” an exception is raised:

"Msg 13608, Level 16, State 2, Line 28
Property cannot be found on the specified JSON path."

The strict option requires that the node we want to modify already exist in the JSON document.

In the next example we will try to add a new node called “MeetupList” that will be a array containing meetup lists hold at MS SQL User Group Zagreb.

Example:

DECLARE @MeetupList NVARCHAR(256) = N'["New SQL 2016/2017 functions","SQL & JSON"]';
SELECT JSON_MODIFY(@JSON_data, '$.Meetups', @MeetupList);
/*
{
    "Name": "John Doe",
    "URL": "http:\/\/www.microsoft.com",
    "Meetups": "[\"New SQL 2016\/2017 functions\",\"SQL & JSON\"]"
}*/

As we can see the new node has been added but the resulting type of the list is not an object (array) but the SQL engine interpreted the value as pure text (and escaped). This is like the Boolean value “Currently presenting” before in this article. But, the solution for this problem is to use the JSON_QUERY function because we can’t cast the data in non-existing JSON type.

Example:

DECLARE @MeetupList NVARCHAR(256) = N'["New SQL 2016/2017 functions","SQL & JSON"]';
SELECT JSON_MODIFY(@JSON_data, '$.Meetups', JSON_QUERY(@MeetupList));
/*
{
    "Name": "John Doe",
    "URL": "http:\/\/www.microsoft.com",
    "Meetups": [
        "New SQL 2016/2017 functions",
        "SQL & JSON"
    ]
}*/

The resulting type is now a JSON object. That’s exactly what we want to accomplish. Great!

We can use the append option to add a new element in an existing list. Let's add a new meetup named "TBD" in the existing list of meetups we already have.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{  
    "Name": "John Doe",  
    "URL": "http:\/\/www.microsoft.com",
    "Meetups":["New SQL 2016/2017 functions","SQL & JSON"]
}';
SELECT JSON_MODIFY(@JSON_data, 'append $.Meetups', 'TBD');
/*
{  
    "Name": "John Doe",  
    "URL": "http:\/\/www.microsoft.com",
    "Meetups":["New SQL 2016/2017 functions","SQL & JSON","TBD"]
}*/

Removing a JSON property

In the following example we will try to remove the “FavoriteDrinks” node from the JSON document.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "John Doe",
"URL": "http:\/\/www.microsoft.com",
"FavoriteDrinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}]
,"Meetups":["New SQL 2016/2017 functions","SQL & JSON"]}';
SELECT JSON_MODIFY(@JSON_data, '$.FavoriteDrinks', NULL);
/*
{
    "Name": "John Doe",
    "URL": "http:\/\/www.microsoft.com",
    "Meetups": [
        "New SQL 2016/2017 functions",
        "SQL & JSON"
    ]
}*/

As you can see in the resulting document, the node is removed. Notice that to accomplish this task we are using the same JSON_MODIFY function but instead a new value in the third argument of the function we are simply passing the NULL value.

With similar approach we can use JSON_MODIFY to remove just one element from an element containing a JSON array (object). Let’s try to remove the first element from the “Meetups” node.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "John Doe",
"URL": "http:\/\/www.microsoft.com"
,"Meetups":["New SQL 2016/2017 functions","SQL & JSON"]}';
SELECT JSON_MODIFY(@JSON_data, '$.Meetups[0]', NULL);
/*
{
    "Name": "John Doe",
    "URL": "http:\/\/www.microsoft.com",
    "Meetups": [
        null,
        "SQL & JSON"
    ]
}*/

To accomplish this kind of request we are adding square brackets [] after the node name containing the ordinal number (from 0 to n) of the element we want to remove [x]. The result of this action is a NULL value for the removed element. To be able to totally remove the element we must replace the whole node with the new value.

Example:

DECLARE @MeetupList NVARCHAR(256) = N'["SQL & JSON"]';
DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "John Doe",
"URL": "http:\/\/www.microsoft.com"
,"Meetups":["New SQL 2016/2017 functions","SQL & JSON"]}';
SELECT JSON_MODIFY(@JSON_data, '$.Meetups', JSON_QUERY(@MeetupList));
/*
{
    "Name": "John Doe",
    "URL": "http:\/\/www.microsoft.com",
    "Meetups": [
        "SQL & JSON"
    ]
}*/

Updating the value of a JSON property

We can use JSON_MODIFY to update values of JSON properties. Let’s start with a simple task. Let’s update the first element of our meetup list array and replace its value to another one.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "John Doe",
"URL": "http:\/\/www.microsoft.com"
,"Meetups":["New SQL 2016/2017 functions","SQL & JSON"]}';
SELECT JSON_MODIFY(@JSON_data, '$.Meetups[0]', 'Something about new SQL functions');
/*
{
"Name": "John Doe",
"URL": "http:\/\/www.microsoft.com"
,"Meetups":["Something about new SQL functions","SQL & JSON"]}*/

Let’s try to replace a value in a non-existing array “Lectures”.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "John Doe",
"URL": "http:\/\/www.microsoft.com"
}';
SELECT JSON_MODIFY(@JSON_data, '$.Lectures[0]', 'Something about new SQL functions');
/*
{
"Name": "John Doe",
"URL": "http:\/\/www.microsoft.com"
}*/

There was no problems and no replacement’s because we did not specify the strict option and by default the lax mode was used. Now, let’s use the strict option and see what happens.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "John Doe",
"URL": "http:\/\/www.microsoft.com"
}';
SELECT JSON_MODIFY(@JSON_data, 'strict $.Lectures[0]', 'Something about new SQL functions');

"Msg 13608, Level 16, State 2, Line 28
Property cannot be found on the specified JSON path."

The error was expected because using the strict option.

We can use the strict option in combination with the NULL value. Instead of removing the whole node in this way, we will update the value of the node to NULL. Let’s try to update the “FavoriteDrinks” node to NULL.

Example:

DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "John Doe",
"URL": "http:\/\/www.microsoft.com",
"FavoriteDrinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}]
,"Meetups":["New SQL 2016/2017 functions","SQL & JSON"]}';
SELECT JSON_MODIFY(@JSON_data, 'strict $.FavoriteDrinks', NULL);
/*
{
"Name": "John Doe",
"URL": "http:\/\/www.microsoft.com",
"FavoriteDrinks": null
,"Meetups":["New SQL 2016/2017 functions","SQL & JSON"]
}*/

Multiple changes

For multiple changes in the same query we must multiple call JSON_MODIFY function. The function does not support multiple changes in the same call. This remember me for the old good string REPLACE(REPLACE…. Function 😊. Let’s try to remove the “FavoriteDrinks” node and add a new “Meetups” node in the same query.

Example:

DECLARE @MeetupList NVARCHAR(256) = N'["New SQL 2016/2017 functions","SQL & JSON"]';
DECLARE @JSON_data NVARCHAR(MAX) = N'{
"Name": "John Doe",
"URL": "http:\/\/www.microsoft.com",
"FavoriteDrinks": [
{"Name": "Gin and tonic","Drink": "Occasionally"},
{"Name": "Craft beer","Drink": "Occasionally"},
{"Name": "Coffe with milk","Drink": "Daily"},
{"Name": "Cold water","Drink": "Daily"}]
}';
SELECT JSON_MODIFY(JSON_MODIFY(@JSON_data, '$.Meetups', JSON_QUERY(@MeetupList)), '$.FavoriteDrinks', NULL);
/*
{
    "Name": "John Doe",
    "URL": "http:\/\/www.microsoft.com",
    "Meetups": [
        "New SQL 2016/2017 functions",
        "SQL & JSON"
    ]
}*/

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (4.50 kb)

IS [NOT] DISTINCT FROM (The Distinct Predicate)

MS SQL GENERATE_SERIES

MS SQL TRIM, LTRIM, RTRIM

MS SQL STRING_SPLIT

LEAST()

GREATEST()

HierarchyID data type Performance, tips & tricks

HierarchyID data type Methods

Hierarchical data in MS SQL

Last Actual Plan with sys.dm_exec_query_plan_stats

Modify JSON data using JSON_MODIFY

Adding a new JSON property

Removing a JSON property

Updating the value of a JSON property

Multiple changes

Related posts