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 :
--Valid 1, Invalid 0

Using JSON_QUERY, we can hold chunk / fragment of 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.

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


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:

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

No comments:
Post a Comment