SQL Server JSON - Examples - JSON_QUERY, JSON_VALUE

In this post, let us see some examples on handling JSON data in SQL Server using JSON_QUERY, JSON_VALUE

Consider below sample JSON data, let us check whether it is valid :

{"MSSQL":
      {  
    "SQL Server":{  
   "Version":"6.0",
   "CompatibilityLevel":60,
   "CodeName":"SQL95" },
   "YearofRelease":"1995"   
      }
   

}

DECLARE @Json_data NVARCHAR(MAX) = '
{"MSSQL":
      {  
    "SQL Server":{  
   "Version":"6.0",
   "CompatibilityLevel":60,
   "CodeName":"SQL95" },
   "YearofRelease":"1995"   
      }
   
}'

SELECT ISJSON(@Json_data) ValidJsonText

 --Valid 1, Invalid 0

--Inserting valid JSON data into table
DROP TABLE IF EXISTS JSON_Data
CREATE TABLE JSON_Data (Id INT IDENTITY(1,1), Json_Col NVARCHAR(MAX))
INSERT JSON_Data SELECT '{"MSSQL":
      {  
    "SQL Server":{  
   "Version":"6.0",
   "CompatibilityLevel":60,
   "CodeName":"SQL95" },
   "YearofRelease":"1995"   
      }
   

}'

Using JSON_QUERY, we can hold chunk / fragment of JSON data

SELECT JSON_QUERY(Json_Col,'$.MSSQL.SQL Server') As JSON_Fragment
FROM JSON_Data

Above query will throw error, as path name SQL Server has space and it needs to be enclosed within double quotes as shown in below image :






JSON_VALUE can be used to hold a single value from JSON text.

SELECT JSON_VALUE(Json_Col,'$.MSSQL.YearofRelease') As JSON_Val
FROM JSON_Data

What is lax and strict ?

lax - returns NULL if the path expression is incorrect / path name is invalid
strict - returns error if the path expression is incorrect / path name is invalid

In below example, I have changed path name YearofRelease to YearsofRelease,
let us see how lax and strict behaves

--Returns NULL
SELECT JSON_VALUE(Json_Col,'lax $.MSSQL.YearsofRelease') As JSON_Val
FROM JSON_Data

--Returns Error
SELECT JSON_VALUE(Json_Col,'strict $.MSSQL.YearsofRelease') As JSON_Val

FROM JSON_Data

If JSON text has duplicate path names, we can handle that using key value. Key value starts from 0.

Below example shows on handling duplicate path names based on position:

DECLARE @Json_datas NVARCHAR(MAX) = '
{"MSSQL":
      { "SQLServer":[
    {"Version":"6.0","CodeName":"SQL95" },
    {"Version":"2014","CodeName":"Hekaton" }
      ]}}'
SELECT ISJSON(@Json_datas) ValidJsonText
SELECT JSON_VALUE(@Json_datas,'$.MSSQL.SQLServer[0].Version') As JSON_Val
SELECT JSON_VALUE(@Json_datas,'$.MSSQL.SQLServer[1].Version') As JSON_Val

See Also:

• SQL Server - Handling JSON data