The JSON_QUERY function in SQL is used to extract an object or an array from a JSON string. It returns a JSON fragment from a JSON string based on a specified JSON path expression. Here is an example to demonstrate the usage of JSON_QUERY:
Suppose we have a table named employees with a column info that stores JSON data. We want to extract the "skills" array from the JSON data using JSON_QUERY.
Example 1:
CREATE TABLE employees (
id INT PRIMARY KEY,
info NVARCHAR(MAX)
);
INSERT INTO employees (id, info)
VALUES (1, '{"name": "Alice", "skills": ["SQL", "Python", "Java"]}');
SELECT JSON_QUERY(info, '$.skills') AS extracted_skills
FROM employees
WHERE id = 1;
In this example:
- We create a table employees with columns id and info.
- Insert a record with ID 1 and JSON data containing a "skills" array.
- Use the JSON_QUERY function to extract the "skills" array from the JSON data based on the JSON path expression '$.skills'.
- The result will be the extracted "skills" array: ["SQL", "Python", "Java"].
Example 2 : Given a JSON string containing a name and a list of skills, the task is to extract the first value of the 'Skills' key.
To extract the first value of the 'Skills' key from a JSON string in SQL, you can use the JSON_VALUE function along with the appropriate path expression.
Here's how you can achieve this:
DECLARE @json NVARCHAR(MAX) = '{"name": "Alice", "skills": ["SQL", "Python", "Java"]}';
SELECT JSON_VALUE(@json, '$.skills[0]') AS FirstSkill;
In the above SQL code snippet:
- We declare a variable @json containing the JSON string.
- We then use the JSON_VALUE function to extract the value at the specified path expression '$.skills[0]'. Here, '[0]' indicates that we want the first element of the 'skills' array.
This approach allows you to efficiently extract specific values from JSON strings in SQL, providing flexibility and ease of access to JSON data elements.