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.