Tag Archives: JSON in Sql

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

JSON_VALUE Function in Sql Server 2016

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

JSON_VALUE is one of the new JSON scalar function introduced in Sql Server 2016. This function returns the scalar value from the input JSON text from the specified JSON path location.

SYNTAX: 

JSON_VALUE ( json_string,  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. 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.

[ALSO READ]:

Let us understand JSON_VALUE function with extensive list of examples:

Basic JSON_VALUE function examples

Example 1: Basic example where we are trying to get FirstName from a JSON string

SELECT 
 JSON_VALUE('{"FirstName":"Basavaraj","Last Name":"Biradar"}',
					'$.FirstName') AS 'First Name'

RESULT:
JSON_VALUE Sql Example 1

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

Example 2: Getting FirstName JSON property value which is not at the root level

SELECT JSON_VALUE(
 '{"Name":{"FirstName":"Basavaraj","Last Name":"Biradar"}}',
					'$.Name.FirstName') AS 'First Name'

RESULT:
JSON_VALUE Sql Example 2

Here in this example FirstName property is not at the root level instead it is in the Name object, because of that we need to specify the path as ‘$.Name.FirstName’ instead of just ‘$.FirstName’ like the example 1.

Example 3: Repeat the example 2, but specify the path as in example one. Basically, the idea of this example is to check whether specifying the path like $.PropertyName searches the PropertyName anywhere in the JSON string or just at the root level

SELECT JSON_VALUE(
  '{"Name":{"FirstName":"Basavaraj","Last Name":"Biradar"}}',
					'$.FirstName') AS 'First Name'

RESULT:
JSON_VALUE Sql Example 3

From the result it is clear that, specifying the JSON path like $.PropertyName, just tries to locate the PropertName just at the root level, in otherwords JSON path is the exact location of the string.

Example 4: This example explains that the JSON path expression is case sensitive

SELECT JSON_VALUE('{"name":"Basavaraj"}','$.NAME') AS Name

RESULT:
JSON_VALUE Sql Example 4 1

From the result it is clear that the JSON path is case sensitive. Even though the JSON sting has the property name but the response returned is NULL because the property mentioned in the JSON path is in upper case where as in the JSON string it is in lower case

Let us try the above example by specifying the JSON path in the same case as that in the JSON string

SELECT JSON_VALUE('{"name":"Basavaraj"}','$.name') AS Name

RESULT:
JSON_VALUE Sql Example 4 2

Example 5: This example explains how to specify a JSON String property name with spaces in it in the JSON path expression

Let us try to fetch the JSON property Last Name value, where this property name has an empty space in it:

SELECT JSON_VALUE(
	'{"Last Name":"Basavaraj"}','$.Last Name') AS 'Last Name'

RESULT:

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

In case if the JSON property name has a space in it, then while specifying it in the JSON path we need to specify property name by enclosing it in the double quotes.

Let us re-write the query and try now

SELECT JSON_VALUE(
  '{"Last Name":"Basavaraj"}','$."Last Name"') AS 'Last Name'

RESULT:
JSON_VALUE Sql Example 5 2

Advanced JSON_VALUE function examples

Example 1: Reading values from JSON object where one of its property is an Array (i.e. [ , ])

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

RESULT:
JSON_VALUE Sql Advance Example 1

Example 2: Trying to get a non-scalar value. In the below example trying to get the value of a property which is an array

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

RESULT:
JSON_VALUE Sql Advance Example 2

From the result it is clear that if the JSON Path is pointing to a non-scalar value like an array, sub JSON object etc will return a NULL value or an error if PATH mode is strict. In this example the JSON path is pointing to an array [“Blogging”,”Cricket”] , because of this JSON_VALUE function returning a NULL value

Example 3: Reading JSON property value from a JSON object where Customers property is an array of JSON object (i.e. [{},{}])

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"}}
	]
 }'
--Get first customer details
SELECT 
 JSON_VALUE(@json_str,'$.Customers[0].Name') Name,
 JSON_VALUE(@json_str,'$.Customers[0].Address.Country') Country
 --Get second customer details
SELECT 
 JSON_VALUE(@json_str,'$.Customers[1].Name') Name,
 JSON_VALUE(@json_str,'$.Customers[1].Address.Country') Country

RESULT:
JSON_VALUE Sql Advance Example 3

Example 4: A modified version of the example 3

DECLARE @json_str NVARCHAR(MAX) = 
	'{"Customers":
		[{"Customer":{"Id":1,"Name":"Basavaraj",
			"Address":{"State":"KA","Country":"India"}}},
		 {"Customer":{"Id":2,"Name":"Kalpana",
			"Address":{"State":"NY","Country":"United State"}}}
		]
	}'
--Get first customer details
SELECT 
 JSON_VALUE(@json_str,'$.Customers[0].Customer.Name') Name,
 JSON_VALUE(@json_str,
   '$.Customers[0].Customer.Address.Country') Country
 --Get second customer details
SELECT 
 JSON_VALUE(@json_str,'$.Customers[1].Customer.Name') Name,
 JSON_VALUE(@json_str,
   '$.Customers[1].Customer.Address.Country') Country

RESULT:
JSON_VALUE Sql Advance Example 4

[ALSO READ]: