JSON_VALUE is a JSON function which is used to extracts scalar value from JSON string.
It returns a single text value of type nvarchar.
It can not be used to extract JSON object or JSON array from JSON string, for this you can use JSON_QUERY function.
SYNTAX
JSON_VALUE ( json_string , [path mode] json_path )
Json_string is the JSON string from which the scalar value will be extracted.
json_path is the path of the JSON string which specifies the path of scalar value 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 using JSON_VALUE function in SQL.
Extracting a value of property using JSON_VALUE function
Lets extract the value of Item property from following JSON string.
DECLARE @string_Json NVARCHAR(MAX)
= '{
"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]
}'
SELECT JSON_VALUE(@string_Json,'$.Item') AS JSON_Color
Extracting the value from JSON Object and Array
Lets take a look on few set of examples on extracting a scalar value from JSON object and Array using JSON_VALUE function.
Example 1:
Lets extract the third value of JSON array Color from JSON String.
DECLARE @string_Json NVARCHAR(MAX)
= '{
"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]
}'
SELECT JSON_VALUE(@string_Json,'$.Color[2]') AS JSON_Color
Example 2:
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_VALUE(@string_Json,'$.ItemList[1].Item') AS Item,
JSON_VALUE(@string_Json,'$.ItemList[1].Color[0]') AS Color
Example 3:
DECLARE @string_Json NVARCHAR(MAX);
SET @string_Json =
N'{
"ItemList": [
{
"Item":"Iphone 5S",
"Price":25000,
"Varient":[{"Color":"Grey", "Price":25000},
{"Color":"Silver", "Price":15000},
{"Color":"Black", "Price":20000}]
},
{ "Item":"Iphone 7",
"Price":25000,
"Varient":[{"Color":"Grey", "Price":45000},
{"Color":"Silver", "Price":35000},
{"Color":"Black", "Price":25000}]
}
]
}';
SELECT JSON_VALUE(@string_Json,'$.ItemList[1].Varient[2].Price') AS Price
Extracting JSON object or JSON Array using JSON_VALUE function
When you try to extract a JSON object or JSON array using JSON_VALUE function it returns an error as it always extract a scalar value.
Lets try to extract JSON array Color from below JSON string.
DECLARE @string_Json NVARCHAR(MAX)
= '{
"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]
}'
SELECT JSON_VALUE(@string_Json,'$.Color') AS JSON_Color
As you can see, it returns null because you are trying to extract a JSON array from JSON string and JSON_VALUE function extract only scalar value.
JSON_VALUE VS JSON_QUERY
As you have seen in above example, JSON_VALUE function does not extract JSON object or JSON array from JSON string. If you want to extract JSON object or JSON array from JSON string then can use JSON_QUERY function as shown below.
DECLARE @string_Json NVARCHAR(MAX)
= '{
"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]
}'
SELECT JSON_QUERY(@string_Json,'$.Color') AS JSON_Color
As you can JSON_QUERY returns JSON array from JSON string, this is the difference between JSON_VALUE and JSON_QUERY. JSON_VALUE extract only scalar value from JSON string while JSON_QUERY extracts a JSON object or JSON array from JSON string.
Also Read..
