SQL Server support 2 modes of JSON parsing
- lax mode
- strict mode
A JSON path that specifies the property to extract. If the format of the path isn’t valid, JSON_VALUE returns an error.
JSON_VALUE returns a single text value of type nvarchar(4000). The collation of the returned value is the same as the collation of the input expression.
If the value is greater than 4000 characters:
- In lax mode, JSON_VALUE returns null.
- In strict mode, JSON_VALUE returns an error.
the lax mode is the default mode and returns null if the property has no value. Here is an example, If you see the category property under Post1 object has no value
DECLARE @jsonInfo NVARCHAR(MAX) SET @jsonInfo=N' { "CoName": "DBPROX.COM", "Release": "David Willington", "Post": { "Category": "CTO", "Tags": ["IT-INFRA", "Solution Engineering & Delivery"] }, "Post1": { "Category1": "", "Tags1": ["IT-INFRA", "Solution Engineering & Delivery"] } } '
On querying the above document with the default mode, the query will return null for the property with no value
DECLARE @jsonInfo NVARCHAR(MAX) SET @jsonInfo=N' { "CoName": "DBPROX.COM", "Release": "David Willington", "Post": { "Category": "CTO", "Tags": ["IT-INFRA", "Solution Engineering & Delivery"] }, "Post1": { "Category1": "", "Tags1": ["IT-INFRA", "Solution Engineering & Delivery"] } } ' --JSON_VALUE(Post,'$.Category') SELECT CoName, Release, JSON_VALUE(Post,'lax $.Category') Category, JSON_VALUE(Post,'lax $.Tags') Tags, JSON_QUERY(Post1,'lax $.Category') Category, JSON_QUERY(Post1,'lax $.Tags1') Tags FROM OPENJSON(@jsonInfo) WITH ( CoName varchar(max) '$.CoName', Release varchar(max) '$.Release', Post Nvarchar(max) AS JSON, Post1 Nvarchar(max) AS JSON )
In strict mode, the function raises an error if the path expression contains an error.
DECLARE @jsonInfo NVARCHAR(MAX) SET @jsonInfo=N' { "CoName": "DBPROX.COM", "Release": "David Willington", "Post": { "Category": "CTO", "Tags": ["IT-INFRA", "Solution Engineering & Delivery"] }, "Post1": { "Category1": "", "Tags1": ["IT-INFRA", "Solution Engineering & Delivery"] } } ' SELECT CoName, Release, JSON_VALUE(Post,'lax $.Category') Category, JSON_VALUE(Post,'lax $.Tags') Tags, JSON_QUERY(Post1,'lax $.Category') Category, JSON_QUERY(Post1,'lax $.Tags1') Tags FROM OPENJSON(@jsonInfo) WITH ( CoName varchar(max) '$.CoName', Release varchar(max) '$.Release', Post Nvarchar(max) AS JSON, Post1 Nvarchar(max) AS JSON ) SELECT CoName, Release, JSON_VALUE(Post,'strict $.Category') Category, JSON_VALUE(Post,'strict $.Tags') Tags, JSON_QUERY(Post1,'strict $.Category') Category, JSON_QUERY(Post1,'strict $.Tags1') Tags FROM OPENJSON(@jsonInfo) WITH ( CoName varchar(max) '$.CoName', Release varchar(max) '$.Release', Post Nvarchar(max) AS JSON, Post1 Nvarchar(max) AS JSON )
Error: Msg 13623, Level 16, State 1, Line 33 Scalar value cannot be found in the specified JSON path.