Updating a column in sql
Add JSON object, Adding Property to JSON, Append Value to JSON Array, JSON_MODIFY, JSON_MODIFY function, JSON_MODIFY function in Sql, JSON_MODIFY function with Examples, JSON_MODIFY in Sql 2016, JSON_MODIFY in Sql Server 2016, Msg 13608, Msg 13610, Property cannot be found on the specified JSON path, Removing JSON Property, Setting JSON Property Value to NULL, Sql, Sql 2016, Sql Server 2016, The argument 2 of the “JSON_MODIFY” must be a string literal., Update JSON Column value in Sql, Update JSON in Sql, Update JSON Property Value, Updating JSON column value WHERE json_string : Is the JSON String which needs to be updated json_path : Is the location of the property in the json_string, whose value needs to be updated new_value : The new value for the property in the json_string Let us understand this function with extensive list of examples.
EXAMPLE 1: Updating JSON Property Value In this example the JSON_MODIFY function is used to update the First Name property value from Basavaraj to Basav RESULT: [ALSO READ] ISJSON Function in Sql Server 2016 EXAMPLE 2: Updating JSON property value where JSON_MODIFY function json_string and new_value are variables This example is same as Example 1, the only difference here is instead of passing JSON string as a constant assigning it to the variable @json_string and passing this variable to the function.
[ALSO READ] JSON_QUERY Function in Sql Server 2016 EXAMPLE 5: This example shows how setting a NULL value for the JSON property will remove that property from the JSON string RESULT: [ALSO READ] FOR JSON Clause in Sql Server 2016 Example 6: This example explains how we can set a NULL value for a JSON property In the example 5 we saw that setting a NULL value for a JSON property resulted in that property getting deleted from the JSON string.
But if we have requirement where we need to set the NULL value for a JSON property value then in the JSON path we have to specify strict JSON path mode as in the below script: RESULT: [ALSO READ] OPENJSON Function in Sql Server 2016 EXAMPLE 7: This example shows how we can append a value to the JSON Array.
Execute the below statement to add the hobby Tennis to the Hobbies array: RESULT: [ALSO READ] STRING_SPLIT function in Sql Server 2016 EXAMPLE 8: This example shows how we can add a JSON object to the JSON string.
Execute the following statement to add Address which is of type JSON object to the JSON string: RESULT: From the result we can see that JSON_MODIFY function is escaping the double quote in the JSON object which is getting added.
Customer( Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Detail NVARCHAR(MAX)) GO INSERT INTO dbo.The JSON Path mode lax is the default path mode and if path mode is not specified it will consider it as lax.