Rajendra Gupta
Variable in the JSON_MODIFY() function

Modifying JSON data using JSON_MODIFY() in SQL Server

May 4, 2020 by

This article explores JSON_MODIFY() function to modify JSON Data in the SQL Server.

Introduction

Java Script Object Notation is a popular language in major NoSQL databases and applications for mobile development. SQL Server 2016 introduced native support for JSON. Suppose you define a variable in SQL Server and it holds JSON key-value pairs.

We use JSON_MODIFY() function to update the JSON string. It can update the following items:

  • Update existing property value
  • Add a new element in an existing array
  • Delete a property from JSON string
  • Delete a property

Syntax of JSON_MODIFY() function

JSON_MODIFY (expression, path, newValue)

  • Expression: It is the JSON Data string that we want to update. It might be a variable or a column containing JSON
  • Path: We specify the property that requires an update in the JSON string. It requires the following arguments:

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

    • Append: It is an optional argument, and it specifies a new value that should be appended to the array
    • Lax: it is the default mode in the path argument. Suppose we specify a property in the path argument that does not exist, in this case, the JSON_MODIFY function tries to insert the specified new value. It might give you an error message in case it cannot insert the value
    • Strict: In the strict mode, if the property we specified does not exist, it does not try to insert the value. You get an error message
    • Json_path: it contains the property path that we wish to update

  • New value: It is the new value that we require to update in the JSON

Let’s understand the usage of the JSON_MODIFY() function using examples.

Example 1: Update JSON property value

In the below example, we have key-value pair for Brand and Product key. Suppose you wish to update the product value in this JSON, we can use the following code using JSON_MODIFY().

It returns the updated JSON string in the output.

Update JSON Data property value

In this example, note the following things.

  • First argument expression contains original JSON {“Brand”:”HP”,”Product”:”Laptop”}
  • $.Product is the property path that we want to update
  • A laptop is a new value that we want to update in the $.Product key

Example 2: Get original and updated JSON data

Suppose for comparison, we want both original (before the update) and updated (after update) JSON data in the output. In this query, we declared a variable and stored JSON into it. Later, we used JSON_MODIFY() function to get the updated JSON.

In the output, we get both original and updated JSON.

Get original and updated JSON string

In the below query, note the following:

  • Variable @path contains the required value for the update
  • Specify variable in the JSON_MODIFY() function

Variable in the JSON_MODIFY() function

Example 3: Add a new JSON property

In this example, we specify a new property $.Quantity along with its value. As we know, by default, JSON_MODIFY() function uses lax path mode, so it inserts this new property because it does not exist in the original JSON string.

You can see it inserts the property using JSON_MODIFY() function if the property does not exist.

Add a new JSON property

Let’s specify strict path mode for the above example, and it does not insert the property in JSON as it does not exist.

Property element error

Example 4: Add a new JSON property containing an Array

In the previous example, we added a new property that contains a value. We can have an array in the JSON as well.

In the below query, we defined a variable @DataArray and defined an array to have multiple values.

Add a new JSON property containing an Array

In the output, we verify that it adds a new node in JSON data, but we see a backslash symbol for each element in the array. It is not the desired JSON array. We need to use another JSON function JSON_QUERY() to insert an array using the JSON_MODIFY() function.

We get the property array in the output after using the JSON_QUERY() function.

Insert an array using the JSON_MODIFY() function

Example 5: Append a value to JSON array

In the previous example, we inserted a new array in the JSON string. Suppose we want to insert a new value in the existing JSON array. We can use JSON_MODIFY() function for this as well.

In the below query, we insert Printer in the accessories array. For this purpose, we use argument Append before the array property.

You can look for additional value in the updated JSON.

Append a value to JSON array

Example 6: Append a JSON object in the JSON data

We can have a nested JSON object as well inside a JSON. For example, a JSON string can contain another JSON string in its property.

For example, suppose we want to add a JSON object that contains seller information in the existing JSON. We need to specify the new JSON in the third parameter. For simplicity purposes, it’s better to declare a variable and contain JSON into it similar to the below code.

Append a JSON object in the JSON

In the above query output, we see that SQL Server escaped double-quotes and we get backslash in the new JSON. As highlighted earlier, we can use JSON_QUERY() function and get the valid JSON output, as shown below.

JSON_QUERY() function

In the below code, we have an existing array [Accessories] with few values. Let’s add the new JSON into this Accessories array. For this purpose, we use append argument along with the [$. Accessories ] in the second argument. You can view the output shows JSON in the array.

Append argument

Example 7: Removing a JSON property

Sometimes, we may want to remove an existing JSON property. To remove a JSON node or property, pass the NULL value in the third argument. We can also remove a specific value from the JSON array.

In the below query, we want to remove the keyboard from the Accessories array. We specify the following in the query.

  • Specify the position of an element in the JSON array. The first element exists at zero position. For the keyboard, the value specifies the path as $.Accessories[0]
  • Specify a NULL value in the third argument

Once we execute the above JSON script, it replaced the array element with NULL. Are we ok with this NULL value? No, Right!

  • JSON_MODIFY() should not replace a value with a NULL value
  • It should obliterate the element

Removing a JSON property

We can replace the array with a new set of values. It helps to eliminate NULL values in the output.

In the below query, we replaced the existing array with a new array that does not contain the element we do not require.

In the output, we can verify that replacing an array solved the problem.

Eliminate NULL values

In the below query, we remove Products property from the JSON data. We do not get NULL value in the output in this case, and it removed the node as shown below.

Remove Products property

Example 8: Update a JSON property

We can update an existing property using the JSON_MODIFY() function as well. For example, suppose someone entered the wrong value in the Accessories array in the zero position. The below code updates the element with a new specified value.

Update a JSON property

Imagine what is the output of the below code. Here, we try to update an array element, but the array does not exist in the JSON data.

We do not get any error message. It shows similar JSON in both original and updated JSON. You should recall here that by default, JSON_MODIFY() function uses lax path mode.

lax path mode

If we rerun the previous code with a small change. We modified path mode from lax to strict. This time it does not work and complains about invalid property in the JSON path.

Strict path mode

Example 9: Rename a Key

We have seen many use cases of JSON_MODIFY() function in the previous examples. Suppose, you want to rename a key available in the JSON Data. It is like rename an existing column in a relational database table.

In the below code, we use Nested JSON_MODIFY() functions along with a combination of JSON_VALUE function. Look at the query carefully. We created a new key and dropped the existing key after copying the value in it. It is a little complicated, but JSON_MODIFY() does not have a direct option to rename a key.

Rename a Key

Example 10: Multiple changes

We can perform multiple changes in the JSON data. JSON_MODIFY() does not support multiple updates in a single function call. To overcome this problem, we can use nested JSON_MODIFY() functions.

In the below query, we update the following values.

  • It updates the contents of the JSON array
  • It updates the value for the Product key

Multiple changes

Conclusion

In this article, we overviewed JSON_MODIFY() function and its usage to update JSON data using various examples. I hope it should be a knowledge article for you to understand the JSON function – JSON_MODIFY().

Rajendra Gupta
1,375 Views