JSON_VALUE is one of the new JSON scalar function introduced in Sql Server 2016. This function returns the scalar value from the input JSON text from the specified JSON path location.
SYNTAX:
JSON_VALUE ( json_string, json_path )
WHERE:
json_string is the JSON string from which the scalar value will be extracted.
json_path is the location of the scalar value in the json_string. Within json_path we can specify the path mode, it can be lax or strict. Lax is the default path mode, if json_path is invalid (i.e. it is not present in the json_string) then it returns null, but if path mode is strict it will raise an error.
[ALSO READ]:
- Native JSON Support in Sql Server 2016
- FOR JSON Clause in Sql Server 2016
- OPENJSON Function in Sql Server 2016
- ISJSON Function in Sql Server 2016
- JSON_QUERY Function in Sql Server 2016
- lax and strict JSON Path modes in Sql Server 2016
- Indexing Strategy for JSON Value in Sql Server 2016
Let us understand JSON_VALUE function with extensive list of examples:
Basic JSON_VALUE function examples
Example 1: Basic example where we are trying to get FirstName from a JSON string
SELECT
JSON_VALUE('{"FirstName":"Basavaraj","Last Name":"Biradar"}',
'$.FirstName') AS 'First Name'
RESULT:
Here in the json_path the $ symbol implies the json_string and $.FirstName means FirstName property in the json_string at the root level.
Example 2: Getting FirstName JSON property value which is not at the root level
SELECT JSON_VALUE(
'{"Name":{"FirstName":"Basavaraj","Last Name":"Biradar"}}',
'$.Name.FirstName') AS 'First Name'
RESULT:
Here in this example FirstName property is not at the root level instead it is in the Name object, because of that we need to specify the path as ‘$.Name.FirstName’ instead of just ‘$.FirstName’ like the example 1.
Example 3: Repeat the example 2, but specify the path as in example one. Basically, the idea of this example is to check whether specifying the path like $.PropertyName searches the PropertyName anywhere in the JSON string or just at the root level
SELECT JSON_VALUE(
'{"Name":{"FirstName":"Basavaraj","Last Name":"Biradar"}}',
'$.FirstName') AS 'First Name'
RESULT:
From the result it is clear that, specifying the JSON path like $.PropertyName, just tries to locate the PropertName just at the root level, in otherwords JSON path is the exact location of the string.
Example 4: This example explains that the JSON path expression is case sensitive
SELECT JSON_VALUE('{"name":"Basavaraj"}','$.NAME') AS Name
RESULT:
From the result it is clear that the JSON path is case sensitive. Even though the JSON sting has the property name but the response returned is NULL because the property mentioned in the JSON path is in upper case where as in the JSON string it is in lower case
Let us try the above example by specifying the JSON path in the same case as that in the JSON string
SELECT JSON_VALUE('{"name":"Basavaraj"}','$.name') AS Name
RESULT:
Example 5: This example explains how to specify a JSON String property name with spaces in it in the JSON path expression
Let us try to fetch the JSON property Last Name value, where this property name has an empty space in it:
SELECT JSON_VALUE(
'{"Last Name":"Basavaraj"}','$.Last Name') AS 'Last Name'
RESULT:
Msg 13607, Level 16, State 4, Line 1
JSON path is not properly formatted. Unexpected character found at position 7.
In case if the JSON property name has a space in it, then while specifying it in the JSON path we need to specify property name by enclosing it in the double quotes.
Let us re-write the query and try now
SELECT JSON_VALUE(
'{"Last Name":"Basavaraj"}','$."Last Name"') AS 'Last Name'
RESULT:
Advanced JSON_VALUE function examples
Example 1: Reading values from JSON object where one of its property is an Array (i.e. [ , ])
DECLARE @json_str NVARCHAR(MAX) =
'{"Id":1,"Name":"Basavaraj",
"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_VALUE(@json_str,'$.Name') Name,
JSON_VALUE(@json_str,'$.Hobbies[0]') Hobby1,
JSON_VALUE(@json_str,'$.Hobbies[1]') Hobby2
RESULT:
Example 2: Trying to get a non-scalar value. In the below example trying to get the value of a property which is an array
DECLARE @json_str NVARCHAR(MAX) =
'{"Id":1,"Name":"Basavaraj",
"Hobbies":["Blogging","Cricket"]}'
SELECT JSON_VALUE(@json_str,'$.Hobbies') Hobbies
RESULT:
From the result it is clear that if the JSON Path is pointing to a non-scalar value like an array, sub JSON object etc will return a NULL value or an error if PATH mode is strict. In this example the JSON path is pointing to an array [“Blogging”,”Cricket”] , because of this JSON_VALUE function returning a NULL value
Example 3: Reading JSON property value from a JSON object where Customers property is an array of JSON object (i.e. [{},{}])
DECLARE @json_str NVARCHAR(MAX) =
'{"Customers":
[{"Id":1,"Name":"Basavaraj",
"Address":{"State":"KA","Country":"India"}},
{"Id":2,"Name":"Kalpana",
"Address":{"State":"NY","Country":"United State"}}
]
}'
--Get first customer details
SELECT
JSON_VALUE(@json_str,'$.Customers[0].Name') Name,
JSON_VALUE(@json_str,'$.Customers[0].Address.Country') Country
--Get second customer details
SELECT
JSON_VALUE(@json_str,'$.Customers[1].Name') Name,
JSON_VALUE(@json_str,'$.Customers[1].Address.Country') Country
RESULT:
Example 4: A modified version of the example 3
DECLARE @json_str NVARCHAR(MAX) =
'{"Customers":
[{"Customer":{"Id":1,"Name":"Basavaraj",
"Address":{"State":"KA","Country":"India"}}},
{"Customer":{"Id":2,"Name":"Kalpana",
"Address":{"State":"NY","Country":"United State"}}}
]
}'
--Get first customer details
SELECT
JSON_VALUE(@json_str,'$.Customers[0].Customer.Name') Name,
JSON_VALUE(@json_str,
'$.Customers[0].Customer.Address.Country') Country
--Get second customer details
SELECT
JSON_VALUE(@json_str,'$.Customers[1].Customer.Name') Name,
JSON_VALUE(@json_str,
'$.Customers[1].Customer.Address.Country') Country
RESULT:
[ALSO READ]:
- Native JSON Support in Sql Server 2016
- DROP IF EXISTS Statement in Sql Server 2016
- Compare Execution Plans in Sql Server 2016
- Live Query Statistics in Sql Server 2016
- DATEDIFF_BIG Function in Sql Server 2016
- Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server
- SESSION_CONTEXT in Sql Server 2016
