SQL Server JSON_QUERY function is a built-in function in SQL Server that is used to extract data from a JSON object. The function is used to retrieve a scalar (single) value or a complete JSON object from a JSON string or column in a table.

Syntax

The syntax of the JSON_QUERY function is as follows:

JSON_QUERY ( expression , path )

Parameters:
expression: A JSON string or column in a table that contains the JSON data.
path: A string that specifies the JSON path expression to extract the data from the JSON object.
The JSON_QUERY function returns the JSON value as a VARCHAR or NVARCHAR value, depending on the input expression.

Example

Here's an example that demonstrates the use of the JSON_QUERY function:

We start the example by creating the "Employees" table with the EmployeeID column of type INT and EmployeeData of type NVARCHAR(MAX).

CREATE TABLE Employees
(EmployeeID INT, EmployeeData NVARCHAR(MAX));

INSERT INTO Employees(EmployeeID, EmployeeData)
VALUES (1, '{"Name": ["John"], "Age": [35], "Department": ["IT"]}');
INSERT INTO Employees(EmployeeID, EmployeeData)
VALUES (2, '{"Name": ["Sarah"], "Age": [28], "Department": ["HR"]}');

We can use the JSON_QUERY function to extract the Department value for the employee with EmployeeID = 1 as follows:

SELECT JSON_QUERY(EmployeeData, '$.Department') AS Department
FROM Employees
WHERE EmployeeID = 1;

In this example, the JSON_QUERY function is used to extract the "Department" value from the "EmployeeData" column where the EmployeeID is 1. The '$.Department' argument in the function specifies the JSON path expression to retrieve the "Department" value from the JSON object.

Example

The following example shows how to extract the first employee from the json variable.

DECLARE @json NVARCHAR(4000);
SET @json = N'{"employees":
[
{"Name": "John", "Age": 35, "Department": "IT"},
{"Name": "Sarah", "Age": 28, "Department": "HR"}
]
}';
SELECT JSON_QUERY(@json, '$.employees[0]');

Overall, the SQL Server JSON_QUERY function is a useful tool for working with JSON data in SQL Server. It allows you to easily extract specific values from JSON objects stored in columns, making it easier to work with and analyze this data.