Home » SQL JSON_MODIFY Function

SQL JSON_MODIFY Function

The JSON_MODIFY is a JSON functions that allows you to update the value of a property in a JSON string. It introduced in SQL Server 2016.





JSON_MODIFY function returns the updated JSON string.

Using JSON_MODIFY function you can update an existing property value, delete a property value from JSON string, delete a property, and add a new Key and it’s value to the JSON string.

It returns an error when string is not in valid JSON format.

SYNTAX

JSON_MODIFY (expression, path, new_value)

expression: is the column or a variable that contains the JSON data string that you want to update.

Path: is a JSON path that specify the property that requires an update in the JSON string.

Path requires the following arguments:

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

    • 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 strictlax is the default mode.
    • append
      It is an optional modifier, that specifies that the new value should be appended to the array referenced by <json path>.
    • lax
      It 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. Lax is the default mode, If you don’t specify lax or strict.
    • strict
      It 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>
      It specifies the path for the property that you want to update.

new_value: is the new value that you require to update in the JSON.

Lets look at an example of using JSON_MODIFY function in SQL Server.

Here we have a simple JSON string as shown Below.

{"Item":"Iphone 5S", "Price":25000}

Updating the property value using JSON_MODIFY function

Following T-SQL uses JSON_MODIFY function that update the Item property value from ‘Iphone 5S’ to ‘Iphone 7’.

DECLARE @string_Json VARCHAR(150) 
= '{"Item":"Iphone 5S", "Price":25000}'

SELECT 
    JSON_MODIFY(@string_Json,
                          '$.Item', 'IPhone 7') AS [Updated Property Value];

As you can see, It returns the updated JSON string there you can see Item property value is updated from ‘Iphone 5S’ to ‘Iphone 7’.

Inserting a new property and value in JSON string using JSON_MODIFY function

Following T-SQL uses the JSON_MODIFY function which adds a new property named ‘Color’ and it’s value ‘Black’ to the JSON string.

DECLARE @string_Json VARCHAR(150) 
      = '{"Item":"Iphone 5S","Price":25000}'
DECLARE @path VARCHAR(100) = '$.Color'
DECLARE @newvalue VARCHAR(50) = 'Black'

SELECT 
      JSON_MODIFY(@string_Json,
                  @Path, @newvalue) AS [Updated Property Value];

Using JSON_MODIFY function appending a value to the JSON Array

You can append a new value to the JSON array, following T-SQL uses a JSON_MODIFY functions and appends a new value ‘Silver’ to the JSON array.

DECLARE @string_Json VARCHAR(150)
           = '{"Item":"Iphone 5S",
               "Price":25000,
               "Color":["Black", "Grey", "Gold"]} '
DECLARE @path VARCHAR(100) = 'append$.Color'
DECLARE @newvalue VARCHAR(50) = 'Silver'

SELECT
      JSON_MODIFY(@string_Json,
                   @Path, @newvalue) AS [Updated Property Value];

As you can see, it returns an updated JSON string appending a new value ‘Silver’ to the JSON string.

Using a strict path mode with JSON_MODIFY function

If you specified the strict path mode in JSON then it does not allow you to inserting a new property in JSON string as it specifies that the property referenced by Path must be in the JSON string, if it does not exist it returns an error.



Obviously, the property that you are going to insert, does not exists in JSON String.

Lets try to execute above T-SQL with strict path mode while adding a new property in JSON String.

DECLARE @string_Json VARCHAR(150) 
        = '{"Item":"Iphone 5S","Price":25000}'
DECLARE @path VARCHAR(100) = 'strict$.Color'
DECLARE @newvalue VARCHAR(50) = 'Black'

SELECT 
       JSON_MODIFY(@string_Json,
                   @Path, @newvalue) AS [Updated Property Value];

As you can see, it returns an error indicating that ‘Property cannot be found on the specified JSON path.’

So, you can not add new property when strict path mode is specified in JSON_MODIFY function. If strict path is specified then you can only modify an existing property in JSON string.

Using a lax path mode with JSON_MODIFY function

lax is the default path mode, It specifies that the property referenced by path does not have to exist. If the property is not present that why using JSON_MODIFY you can insert the new value on the specified path.

Lets use a lax path mode in above T-SQL, and you will see it adds a property Color and it’s value in JSON string.

DECLARE @string_Json VARCHAR(150) 
           = '{"Item":"Iphone 5S","Price":25000}'
DECLARE @path VARCHAR(100) = 'lax$.Color'
DECLARE @newvalue VARCHAR(50) = 'Black'

SELECT 
       JSON_MODIFY(@string_Json,
                          @Path, @newvalue) AS [Updated Property Value];

Deleting value from Property using JSON_MODIFY function

Lets delete the value from JSON array, to delete the value from JSON array you can do it either replacing that value with null or update the JSON array with new set of values.

Suppose you want to delete the value ‘Grey’ from JSON array, JSON string as given below.

'{"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]}'

To delete the value ‘Grey’ from above JSON array you need to specify the position of element in JSON array, the path of value in JSON array is $Color.[1].



DECLARE @string_Json VARCHAR(150)
           = '{"Item":"Iphone 5S",
                "Price":25000,
                 "Color":["Black", "Grey", "Gold"]}'
DECLARE @path VARCHAR(100) = '$.Color[1]'
DECLARE @newvalue VARCHAR(50) = null

SELECT
           JSON_MODIFY(@string_Json,
                                 @Path, @newvalue) AS [Updated Property Value];

As you can see, it replaces the value with null.

Lets see the another way to deleting the value from JSON array, in this way we will update the JSON array values with new set of values as shown below.

Removed the ‘Grey’ from new values, only kept ‘Black’ and ‘Gold’ in JSON array.

As you can see, returns the updated JSON string but adds the backslash for each element in the array, it happens as JSON_MODIFY function is considering the JSON object as a normal text instead of valid JSON.

It can be removed using JSON_QUERY() function, Lets use the JSON_QUERY() function to insert a set of values using JSON_MODIFY() function.

DECLARE @string_Json VARCHAR(150)
           = '{"Item":"Iphone 5S",
               "Price":25000,
               "Color":["Black", "Grey", "Gold"]}'
DECLARE @path VARCHAR(100) = '$.Color'
DECLARE @newvalue VARCHAR(50) = '["Black", "Gold"]'

SELECT
       JSON_MODIFY(@string_Json,
                   @Path, JSON_QUERY(@newvalue)) AS [Updated Property Value];

You can see, now value Grey is deleted from JSON array also there is no backslash.

Deleting a Property from JSON string

To deleting a property from JSON string, just pass a third argument value as null as shown below.

DECLARE @string_Json VARCHAR(150)
           = '{"Item":"Iphone 5S",
                 "Price":25000,
                 "Color":["Black", "Grey", "Gold"]}'
DECLARE @path VARCHAR(100) = '$.Color'
DECLARE @newvalue VARCHAR(50) = null

SELECT
      JSON_MODIFY(@string_Json,
                     @Path, JSON_QUERY(@newvalue)) AS [Updated Property Value];

As you can see, Property color is deleted from JSON string.

Also Read..

SQL Server For JSON clause

JSON_QUERY

JSON_MODIFY




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.