SQL JSON_QUERY is a JSON function which extracts an object or an array from a JSON string.
JSON_QUERY function introduced in SQL Server 2016.
You can not extract a scalar value using JSON_QUERY function. It always extract JSON object or JSON Array. To extract scalar value you can use JSON_VALUE function.
SYNTAX
JSON_QUERY ( json_string ,[path mode] json_path )
json_string is the string that contains the JSON string.
json_path is the path of the JSON string which specifies the path of JSON Object or the Array to extract in the json_string.
[path mode]: It is an optional, It can be specified with json_path, it can be either lax or strict.
Lax is the default path_mode, if specified json_path is not present in the json_string then it returns null, but if path_mode is strict it will always raise an error.
Lets look at an example of JSON_QUERY function in SQL Server.
Extracting the JSON Object and JSON Array from JSON String
Lets extracts the color array using the JSON_QUERY function.
DECLARE @string_Json NVARCHAR(150)
= '{"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]}'
SELECT JSON_QUERY(@string_Json,'$.Color') AS JSON_Color
Lets extract the second JSON object from the ItemList key.
DECLARE @string_Json NVARCHAR(4000);
SET @string_Json = N'{
"ItemList":
[ {
"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]
},
{
"Item":"Iphone 7",
"Price":25000,
"Color":["Black", "Grey"]
}
]
}';
SELECT JSON_QUERY(@string_Json,'$.ItemList[1]') AS JSON_Color
Using JSON_QUERY function to read a scalar value from JSON String
JSON_QUERY function always returns a JSON object or JSON Array, so you can not use JSON_QUERY function to read the scalar value.
Lets try to extract the scalar value that is first element of color array using JSON_QUERY function.
DECLARE @string_Json NVARCHAR(MAX)
= '{"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]}'
SELECT JSON_QUERY(@string_Json,'$.Color[0]') AS JSON_Color
As you can see, it returns null means JSON_QUERY does not read scalar value.
JSON_QUERY VS JSON_VALUE
As you have seen in above example, JSON_QUERY does not extract a scalar value from JSON string, so to get the scalar value from JSON string you can use JSON_VALUE function as shown below.
DECLARE @string_Json NVARCHAR(MAX)
= '{"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]}'
SELECT JSON_VALUE(@string_Json,'$.Color[0]') AS JSON_Color
As you can see, it returns the value of first element of color array and this is one of the difference between JSON_QUERY and JSON_VALUE function that is JSON_QUERY returns the JSON object and JSON array while JSON_VALUE function returns only scalar value from JSON string.
Extracting the complete JSON from JSON String
Extracting the complete JSON from JSON string you can use the ‘$’ argument as shown below.
DECLARE @string_Json NVARCHAR(MAX);
SET @string_Json = N'{
"ItemList":
[ {
"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]
},
{
"Item":"Iphone 7",
"Price":25000,
"Color":["Black", "Grey"]
}
]
}';
SELECT JSON_QUERY(@string_Json,'$') AS Complete_JSONString
As you can see, It returns complete JSON string.
Using Path Mode lax and strict with JSON_QUERY function
JSON_QUERY function uses a default path mode lax, you can also specified it explicitly with Json path.
In following statement use lax path mode with JSON path and try to read scalar value from JSON string. Obviously the scalar is value is not an object or an array so, JSON_QUERY function returns null instead of raising an error as the path mode lax is used which returns null when value is not JSON object or JSON array.
DECLARE @string_Json NVARCHAR(MAX)
= '{"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]}'
SELECT JSON_QUERY(@string_Json,'lax$.Color[0]') AS JSON_Color
Lets use a strict path mode, if you try to read a scalar value using strict path mode then you will get an error as shown below.
DECLARE @string_Json NVARCHAR(MAX)
= '{"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]}'
SELECT JSON_QUERY(@string_Json,'strict$.Color[0]') AS JSON_Color
As you can see, using a strict path mode returns an error as result is not a JSON object or JSON array.
Also Read..
