SQL Server JSON_MODIFY() Function

Summary: in this tutorial, you will learn how to use the SQL Server JSON_MODIFY() function to update the value of a property in a JSON string.

Introduction to SQL Server JSON_MODIFY() function

The JSON_MODIFY() function allows you to update a JSON string and returns the updated JSON string.

JSON_MODIFY ( expression , path , newValue )Code language: SQL (Structured Query Language) (sql)

In this syntax:

• expression: This is a JSON string, a variable that holds the JSON string, or a column that contains the JSON string.
• path: This is a JSON path that locates the element you want to modify.
• newValue: This is the new value for the update. Its type must be VARCHAR, NVARCHAR, or TEXT.

The path has the following syntax:

[append] [ lax | strict ] $.<json path>Code language: SQL (Structured Query Language) (sql)

• append: The append option instructs the function to append the newValue to the JSON array specified by the json_path.
• lax: If the property specified by the json_path does not exist, the function will attempt to insert newValue to the JSON. Additionally, the function deletes the specified key if the new value is NULL.
• strict: This mode instructs the function to return an error if json_path does not exist.
• json_path: This JSON path specifies the property in the JSON string to update.

Note that the JSON_MODIFY() function can:

• Update the value of an existing property.
• Insert a new key/value pair.
• Delete an existing key.

SQL Server JSON_MODIFY() function examples

Let’s explore some examples of using the JSON_MODIFY() function.

1) Updating a property

The following example uses the JSON_MODIFY() function to update a value in a JSON string:

SELECT 
  JSON_MODIFY(
    '{"name": "John", "age": 22}', '$.age', 
    25
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
---------------------------
{"name": "John", "age": 25}Code language: SQL (Structured Query Language) (sql)

2) Inserting a new key/value pair

The following example uses the JSON_MODIFY() function to insert a value in a JSON string:

SELECT 
  JSON_MODIFY(
    '{"name": "John", "age": 30}', '$.city', 
    'New York'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
---------------------------------------------
{"name": "John", "age": 30,"city":"New York"}Code language: SQL (Structured Query Language) (sql)

3) Deleting a new key/value pair

The following example uses the JSON_MODIFY() function to delete the age property from a JSON string:

SELECT 
  JSON_MODIFY(
    '{"name": "John", "age": 30}', '$.age', 
    NULL
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
----------------
{"name": "John"}Code language: SQL (Structured Query Language) (sql)

Summary

• Use the JSON_MODIFY() function to insert, update, and delete a property from a JSON string.