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]:
- Native JSON Support in Sql Server 2016
- FOR JSON Clause in Sql Server 2016
- OPENJSON Function in Sql Server 2016
- ISJSON Function in Sql Server 2016
- JSON_VALUE Function in Sql Server 2016
- JSON_QUERY Function in Sql Server 2016
- Indexing Strategy for JSON Value in Sql Server 2016
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'
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'
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'
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
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
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')
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')
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')
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')
- Native JSON Support in Sql Server 2016
- DROP IF EXISTS Statement in Sql Server 2016
- Compare Execution Plans in Sql Server 2016
- Live Query Statistics in Sql Server 2016
- DATEDIFF_BIG Function in Sql Server 2016
- Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server
- SESSION_CONTEXT in Sql Server 2016