Syntax of JSON_VALUE Function:
The syntax of the JSON_VALUE function is as follows:
JSON_VALUE (expression, path)
expression: The JSON string from which to extract the value.
path: The JSON path that specifies the location of the value to be extracted.
Example Scenario:
Suppose we have a table named Employee in our SQL Server database that stores employee information in JSON format. Each row contains a JSON object representing details such as name, age, and department. Here is a sample JSON structure stored in the Employee table:
{
"name": "John Doe",
"age": 30,
"department": "IT"
}
Example 1- Query Using JSON_VALUE:
Now, let's say we want to extract the value of the name field from the JSON data stored in the Employee table. We can achieve this using the JSON_VALUE function as shown below:
SELECT JSON_VALUE(Employee.JsonColumn, '$.name') AS EmployeeName
FROM Employee
In this query:
Employee.JsonColumn represents the column in the Employee table that stores the JSON data.
'$ .name' is the JSON path that specifies the name field within the JSON object.
Output:
Upon executing the query, the result will be a single column named EmployeeName containing the extracted value of the name field from the JSON data.
Example 2- Query Using JSON_VALUE:
DECLARE @json NVARCHAR(MAX) = '{"name": "John", "age": 30, "city": "New York"}';
SELECT JSON_VALUE(@json, '$.name') AS PersonName;
In this example:
- We declare a variable @json containing a JSON string with key-value pairs.
- We use the JSON_VALUE function to extract the value associated with the key 'name' from the JSON string.
- The JSON path '$' specifies the root of the JSON object, and 'name' specifies the key whose value we want to extract.
- The result of this query will be the value 'John', which is the value associated with the key 'name' in the JSON string.
- Remember, when using the JSON_VALUE function, ensure that the JSON path provided is correct and points to the desired value within the JSON string.