Tag Archives: Msg 13608

JSON_MODIFY Function in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , , , , , , , , , , , , ,

JSON_MODIFY is one of the new JSON function introduced in Sql Server 2016. This function can be used to update the value of the property in a JSON string and returns the updated JSON string.

SYNTAX:

JSON_MODIFY (json_string, json_path , new_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 FirstName property value from Basavaraj to Basav

SELECT
 JSON_MODIFY('{"FirstName":"Basavaraj","Last Name":"Biradar"}',
                   '$.FirstName','Basav') AS 'Updated JSON'

RESULT:
JSON_MODIFY Example 1

[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. And the new value is set to the variable @new_value and passed it to the function.

DECLARE @json_string VARCHAR(100) 
	= '{"FirstName":"Basavaraj","Last Name":"Biradar"}',
	@new_value VARCHAR(50) = 'Basav'
SELECT JSON_MODIFY(@json_string,'$.FirstName',
                     @new_value) AS 'Updated JSON'

RESULT:
JSON_MODIFY Example 2

In the above example we can see that only the json_path is a string literal. Let us assign this also to a variable and pass it to the JSON_MODIFY function.

DECLARE @json_string VARCHAR(100) 
		= '{"FirstName":"Basavaraj","Last Name":"Biradar"}',
	@json_path VARCHAR(50) = '$.FirstName',
@new_value VARCHAR(50) = 'Basav'
SELECT JSON_MODIFY(@json_string, @json_path, 
                     @new_value) AS 'Updated JSON'

RESULT:

Msg 13610, Level 16, State 2, Line 5
The argument 2 of the “JSON_MODIFY” must be a string literal.

From the result we can see that the JSON PATH expression should always be a string literal

[ALSO READ] JSON_VALUE Function in Sql Server 2016

EXAMPLE 3: Adding Id property and it’s value to the JSON string

The below script adds the Id property to the JSON string

SELECT JSON_MODIFY(
   '{"FirstName":"Basavaraj","Last Name":"Biradar"}'
   , '$.Id', 1) AS 'Updated JSON'

RESULT:
JSON_MODIFY Insert Property Example 3

[ALSO READ] lax and strict JSON Path modes in Sql Server 2016

EXAMPLE 4: Impact of JSON Path mode on adding a new property to the existing JSON string

Execute the following statement which is same as the example 3 script, the only difference is in the json_path the JSON path mode strict is specified.

SELECT JSON_MODIFY(
      '{"FirstName":"Basavaraj","Last Name":"Biradar"}'
      , '$.Id', 1) AS 'Updated JSON'

RESULT:

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

From the result we can see that the strict path mode will not allow adding of a new property to the JSON string

Execute the following statement which is same as the above example script, the only difference is in the JSON path mode. Here in this statement the JSON path mode is lax and it is the default path mode when it is not specified.

SELECT JSON_MODIFY(
        '{"FirstName":"Basavaraj","Last Name":"Biradar"}'
		,'lax$.Id', 1 )	 AS 'Updated JSON'

RESULT:
JSON_MODIFY Insert Property lax path mode Example 4

From the result we can see that lax JSON Path mode allows adding a new JSON property. The JSON Path mode lax is the default path mode and if path mode is not specified it will consider it as lax. We can see that in example 3 when path mode is not specified we are able to add the new property.

[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

SELECT JSON_MODIFY(
        '{"Id":1,"FirstName":"Basavaraj","Last Name":"Biradar"}'
		,'$.Id', NULL)	 AS 'Updated JSON'

RESULT:
Removing an Existing Property Example 5

[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:

SELECT JSON_MODIFY(
        '{"Id":1,"FirstName":"Basavaraj","Last Name":"Biradar"}'
		,'strict$.Id', NULL)	 AS 'Updated JSON'

RESULT:
Setting NULL value for a JSON property Example 6

[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:

SELECT JSON_MODIFY(
            '{"Id":1,"Name":"Basavaraj",
            "Hobbies":["Blogging","Cricket"]}',
            'append $.Hobbies','Tennis') AS 'Updated JSON'

RESULT:
Adding value to the JSON Array Example 7

[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:

SELECT JSON_MODIFY(
	'{"Id":1,"Name":"Basavaraj"}'
	,'$.Address'
	,'{"State":"KA","Country":"India"}') AS 'Updated JSON'

RESULT:
Adding JSON Object to JSON string Example 8

From the result we can see that JSON_MODIFY function is escaping the double quote in the JSON object which is getting added. The reason for this is JSON_MODIFY function is treating the JSON object as a normal text instead of valid JSON.

But if you want to avoid the escaping the JSON object which is getting added, use the JSON_QUERY function as shown below:

SELECT JSON_MODIFY(
    '{"Id":1,"Name":"Basavaraj"}'
    ,'$.Address'
    ,JSON_QUERY('{"State":"KA","Country":"India"}')) 
           AS 'Updated JSON'

RESULT:
Adding JSON Object to JSON string Example 8 1

[ALSO READ] DATEDIFF_BIG Function in Sql Server 2016

EXAMPLE 9: This example shows how we can modify the property value of JSON string stored in the Table Column.

Create a Customer table as shown in the below image with sample data by the following script

Customer Table
Script:

CREATE DATABASE SqlHintsJSONModify
GO
USE SqlHintsJSONModify
GO
CREATE TABLE dbo.Customer( 
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name NVARCHAR(50), Detail NVARCHAR(MAX))
GO
INSERT INTO dbo.Customer ( Name, Detail )
VALUES 
  ('Basavaraj','{"Address":{"State":"KA","Country":"India"}}'),
  ('Kalpana','{"Address":{"State":"MH","Country":"India"}}')

Execute the following statement and observe that the customer Kalpana’s state property value in the Detail column is MH.

SELECT * FROM dbo.Customer WHERE Name = 'Kalpana'

RESULT:
DataBeforeUpdate

Execute the following statement to update the Customer Kalpana’s State to KA from MH.

UPDATE Customer
SET Detail = JSON_MODIFY(Detail , '$.Address.State','KA')
WHERE Name = 'Kalpana'

Now execute the following statement to check whether the customer Kalpana’s state value is updated to KA from MH.

SELECT * FROM dbo.Customer WHERE Name = 'Kalpana'

RESULT:
DataAfterUpdate

[ALSO READ]

New features in SQL SERVER 2016

lax and strict JSON Path modes in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , , , , , , , , , , , , ,

In this article let us understand lax and strict JSON Path modes in Sql Server 2016 in the context of a JSON_VALUE, JSON_QUERY and OPENJSON functions with extensive list of examples.

[ALSO READ]:

lax and strict JSON Path modes influence on the JSON_VALUE function output

JSON_VALUE is a scalar function that returns the scalar value from the input JSON text from the specified JSON path location.


SYNTAX of JSON_VALUE function: 

JSON_VALUE ( json_string,  [json_path_mode]json_path )

WHERE:

json_string is the JSON string from which the scalar value will be extracted.

json_path is the location of the scalar value in the json_string.

json_path_mode is optional in the json_path. json_path can be prefixed by the json_path_mode. json_path_mode can be lax or strict. Lax is the default path mode, if json_path is invalid (i.e. it is not present in the json_string) then it returns null, but if path mode is strict it will raise an error.

Let us understand the JSON Path Modes with below extensive list of examples:

Example 1: Let us try to get a non-existent property from a JSON string

SELECT JSON_VALUE(
 '{"FirstName":"Basavaraj","LastName":"Biradar"}',
 '$.City') 'City'

RESULT:
JSON Path mode lax and strict Example 1

From the result it is clear that JSON_Value function returns a NULL value if it is not able find a value at the specified JSON path. In this case path is trying to find a property City at the root level which is not present so it has returned the value NULL.

The default the path mode is lax, because of that we got NULL as a result if the JSON path is not present in the JSON string.

Example 2: This example demonstrates how we can specify the JSON path mode. Let us re-write the previous example with JSON path mode as strict

SELECT JSON_VALUE(
 '{"FirstName":"Basavaraj","LastName":"Biradar"}',
 'strict$.City') 'City'

RESULT:

Msg 13608, Level 16, State 1, Line 1
Property cannot be found in specified path.

From the result it is clear that if JSON path mode is strict then the JSON_VALUE function will return an error instead of returning a NULL value in case if it is not able to find value at the specified JSON path. In the above example the JSON path expression ‘strict$.City’ starts with the word strict, this keyword enforces the JSON path mode as strict. If we don’t specify this keyword by default it is lax. Specifying lax is optional as it is the default JSON path mode.

Example 3: Let us execute the previous example by explicitly specifying the lax JSON path mode

SELECT JSON_VALUE(
 '"FirstName":"Basavaraj","LastName":"Biradar"}',
 'lax$.City') 'City'

RESULT:
JSON Path mode lax and strict Example 3

From the result it is clear that lax is the default JSON path mode, if it is specified or not specified and JSON path doesn’t exists in the JSON string then it returns NULL value.

Example 4: This is an example of strict JSON path mode where the JSON_VALUE function returning a scalar value as the JSON path is valid.

SELECT JSON_VALUE(
 '{"FirstName":"Basavaraj","LastName":"Biradar"}',
 'strict$.FirstName') 'FirstName'

RESULT:
JSON Path mode lax and strict Example 4

Example 5: In this example let us see whether JSON path mode lax or strict is case sensitive. Re-Write the previous example mby specifying the JSON path mode strict in upper case and verify the result

SELECT JSON_VALUE(
 '{"FirstName":"Basavaraj","LastName":"Biradar"}',
 'STRICT$.FirstName') 'FirstName'

RESULT:

Msg 13607, Level 16, State 3, Line 1
JSON path is not properly formatted. Unexpected character found at position 0.

From the result it is clear that we need specify the JSON path modes lax or strict only in lower case.

lax and strict JSON Path modes influence on the JSON_QUERY function output

Example 1: Try to get a JSON scalar value (i.e. non-JSON object or array)

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'$.Name') Name

RESULT:
JSON_QUERY Sql Example 3

Example 2:Let us try to re-execute the example 1 by re-setting the JSON path mode as strict

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'strict$.Name') Name

RESULT:

Msg 13608, Level 16, State 2, Line 4
Property cannot be found in specified path.

Let us try executing the above query by explicitly specifying the default JSON path mode lax

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'lax$.Name') Name

RESULT:
JSON_QUERY Sql Example 4 2

lax and strict JSON path modes influence on the OPENJSON JSON function output

let us understand the influence of the lax/strict JSON path modes on the OPENJSON function output with extensive list of examples

Example 1: In the below example the json_path ‘$.City’ mentioned in the OPENJSON function doesn’t exists in the input JSON string. And in this json_path the JSON path mode has not be specified explicitly, so it goes with default JSON path mode lax.

DECLARE @json_string NVARCHAR(MAX) = N'
{"Id":1,"Name":"Basavaraj",
  "Address":{"State":"KA","Country":"India"}}'
SELECT [key],value
FROM OPENJSON(@json_string, '$.City')

RESULT:
OPENJSON lax and strict JSON path modes Example 1

Let us execute this example by specifying the JSON path mode as strict

DECLARE @json_string NVARCHAR(MAX) = 
	N'{"Id":1,"Name":"Basavaraj",
	   "Address":{"State":"KA","Country":"India"}}'
SELECT [key],value
FROM OPENJSON(@json_string, 'strict$.City')

RESULT:

Msg 13608, Level 16, State 3, Line 4
Property cannot be found in specified path.

Let us execute this example by explicitly specifying the default JSON path mode lax

DECLARE @json_string NVARCHAR(MAX) = 
	N'{"Id":1,"Name":"Basavaraj",
	   "Address":{"State":"KA","Country":"India"}}'
SELECT [key],value
FROM OPENJSON(@json_string, 'lax$.City')

RESULT:
OPENJSON lax and strict JSON path modes Example 1 2

Example 2: In the below OPENJSON function with explicit schema declaration, DOB column value JSON path ‘$.Dob’ doesn’t exists in the input JSON string. In the json_path the JSON path mode has not be specified explicitly, so it goes with default JSON path mode which is lax.

DECLARE @json_string NVARCHAR(MAX) = 
 N'{"Customers":
    [{"Id":1,"Name":"Basavaraj",
            "Address":{"State":"KA","Country":"India"}},
     {"Id":2,"Name":"Kalpana",
            "Address":{"State":"NY","Country":"United State"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Customers')
WITH(EmployeeId INT '$.Id', EmployeeName Varchar(100) '$.Name',
		DOB Varchar(10) '$.Dob')

RESULT:
OPENJSON lax and strict JSON path modes Example 2

Let us execute the previous query by specifying the JSON path mode as strict i.e. ‘strict$.Dob’

DECLARE @json_string NVARCHAR(MAX) = N'
{"Customers":
    [{"Id":1,"Name":"Basavaraj",
      "Address":{"State":"KA","Country":"India"}},
     {"Id":2,"Name":"Kalpana",
      "Address":{"State":"NY","Country":"United State"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Customers')
WITH(EmployeeId INT '$.Id', EmployeeName Varchar(100) '$.Name',
		DOB Varchar(10) 'strict$.Dob')

RESULT:

Msg 13608, Level 16, State 6, Line 9
Property cannot be found in specified path.

Let us execute the above query by specifying the default JSON path mode explicitly ‘lax$.Dob’

DECLARE @json_string NVARCHAR(MAX) = N'
{"Customers":
    [{"Id":1,"Name":"Basavaraj",
      "Address":{"State":"KA","Country":"India"}},
     {"Id":2,"Name":"Kalpana",
      "Address":{"State":"NY","Country":"United State"}}
    ]
 }'
SELECT *
FROM OPENJSON(@json_string,'$.Customers')
WITH(EmployeeId INT '$.Id', EmployeeName Varchar(100) '$.Name',
		DOB Varchar(10) 'lax$.Dob')

RESULT:
OPENJSON lax and strict JSON path modes Example 2 2
[ALSO READ]:

JSON_QUERY Function in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , , , , , , , , , ,

JSON_QUERY is one of the new JSON function introduced in Sql Server 2016 to query the JSON data. JSON_QUERY basically returns the JSON fragment (i.e. JSON object or an array) from the input JSON string from the specified JSON path.

SYNTAX:

JSON_QUERY ( json_string,  json_path )

WHERE:

json_string is the JSON string from which the JSON fragment will be extracted.

json_path is the location of the JSON string in the json_string. Within json_path we can specify the path mode, it can be lax or strict. Lax is the default path mode, if json_path is invalid (i.e. it is not present in the json_string) then it returns null, but if path mode is strict it will raise an error.

This function will return error even in the scenario if the specified json_path is resulting in a scalar value other than the JSON object or array. Where as JSON_VALUE works the other way, it returns an error in case the JSON path is

[ALSO READ]:

Let us understand JSON_QUERY function with extensive list of examples:

Example 1: In this example let us try to get the Hobbies array using the JSON_QUERY function

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'$.Hobbies') Hobbies

RESULT:
JSON_QUERY Sql Example 1 1

Here in the json_path the $ symbol implies the json_string and $. Hobbies means Hobbies property in the json_string at the root level.

Let us try doing the same using the JSON_VALUE function

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_VALUE(@json_str,'$.Hobbies') Hobbies

RESULT:
JSON_QUERY Sql Example 1 2

From the result it is clear that JSON_VALUE function is not for reading the JSON object or array, instead it is for reading the scalar JSON values like string, integer etc.

Example 2: Try to get the complete JSON string from the root

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
--Get customer details
SELECT JSON_QUERY(@json_str,'$') JSON
GO

RESULT:
JSON_QUERY Sql Example 2

Example 3: Try to get a JSON scalar value (i.e. non-JSON object or array)

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'$.Name') Name

RESULT:
JSON_QUERY Sql Example 3
[ALSO READ]:lax and strict JSON Path modes in Sql Server 2016
Example 4: lax and strict JSON path modes in JSON_QUERY

Let us try to re-execute the example 3 by setting the JSON path mode as strict

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'strict$.Name') Name

RESULT:

Msg 13608, Level 16, State 2, Line 4
Property cannot be found in specified path.

Let us try executing the above query by explicitly specifying the default JSON path mode lax

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'lax$.Name') Name

RESULT:
JSON_QUERY Sql Example 4 2
Example 5: In this example try to read on of the array element which in-turn is not a JSON object or an array

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_QUERY(@json_str,'$.Hobbies[1]') Hobby

RESULT:
JSON_QUERY Sql Example 5 1

Try to execute the above query using the JSON_VALUE function instead of JSON_QUERY function

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Id":1,"Name":"Basavaraj",
		"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_VALUE(@json_str,'$.Hobbies[1]') Hobby

RESULT:
JSON_QUERY Sql Example 5 2

From these examples it is clear that we can use the JSON_QUERY function to extract a JSON object or an array only, but not for the scalar values like string, integer etc.

Example 6: Try to extract complete JSON from the JSON string

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Customers":
	[{"Id":1,"Name":"Basavaraj",
	  "Address":{"State":"KA","Country":"India"}},
	 {"Id":2,"Name":"Kalpana",
	  "Address":{"State":"NY","Country":"United State"}}
	]
 }'
 SELECT JSON_QUERY(@json_str,'$') CompleteJSON

RESULT:
JSON_QUERY Sql Example 6

Example 7: In this example try to extract the Customers array

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Customers":
	[{"Id":1,"Name":"Basavaraj",
			"Address":{"State":"KA","Country":"India"}},
	 {"Id":2,"Name":"Kalpana",
			"Address":{"State":"NY","Country":"United State"}}
	]
 }'
 SELECT JSON_QUERY(@json_str,'$.Customers') CustomersArray

RESULT:
JSON_QUERY Sql Example 7

Example 8: In this example extract the first customer JSON object

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Customers":
	[{"Id":1,"Name":"Basavaraj",
			"Address":{"State":"KA","Country":"India"}},
	 {"Id":2,"Name":"Kalpana",
			"Address":{"State":"NY","Country":"United State"}}
	]
 }'
 SELECT JSON_QUERY(@json_str,'$.Customers[0]') CustomerObject

RESULT:
JSON_QUERY Sql Example 8

Example 9: In this example try to extract the first customer’s address object

DECLARE @json_str NVARCHAR(MAX) = 
 '{"Customers":
	[{"Id":1,"Name":"Basavaraj",
			"Address":{"State":"KA","Country":"India"}},
	 {"Id":2,"Name":"Kalpana",
			"Address":{"State":"NY","Country":"United State"}}
	]
 }'
 SELECT JSON_QUERY(@json_str,'$.Customers[0].Address') AddressObject

RESULT:
JSON_QUERY Sql Example 9

[ALSO READ]: