The JSON_VALUE function is used in SQL Server and other SQL databases to extract a scalar value from a JSON string. Here's how you can use it:
Syntax
The syntax for the JSON_VALUE function is as follows:
JSON_VALUE ( expression , path )
- expression: This is the JSON string from which you want to extract the value.
- path: This is a string that defines the path to the value you want to extract from the JSON object.
Example
Assume you have a JSON string that represents a person and contains various attributes:
{
"name": "John Doe",
"age": 30,
"address": {
"street": "123 Main St",
"city": "New York"
}
}
To extract the name, age, and city values from this JSON, you could use the following SQL queries:
DECLARE @json NVARCHAR(MAX) = '{
"name": "John Doe",
"age": 30,
"address": {
"street": "123 Main St",
"city": "New York"
}
}';
-- Extracting the name
SELECT JSON_VALUE(@json, '$.name') AS Name;
-- Extracting the age
SELECT JSON_VALUE(@json, '$.age') AS Age;
-- Extracting the city
SELECT JSON_VALUE(@json, '$.address.city') AS City;
Explanation of the Path
- $.name selects the value of the name attribute.
- $.age selects the value of the age attribute.
- $.address.city navigates into the nested address object to extract the value associated with city.
Notes
- The path uses a dollar sign ($) to represent the root of the JSON object.
- You can navigate through nested objects by using a dot (.) to separate the keys.
- JSON_VALUE only extracts scalar values (strings, numbers, booleans); it does not work with arrays or objects.
Performance
Using JSON_VALUE is generally efficient for reading data from JSON structures, but consider indexing strategies for large datasets if you frequently query JSON data.