In this article, we will explore JSON_QUERY() functions in SQL Server to extract JSON objects and array from the JSON Data.
Overview of JSON
JavaScript Object Notation (JSON) is an accessible format for representing data in a structured way. It consists of lightweight data for data exchange. If you are familiar with Amazon Web Service, DynamoDB, MongoDB, Couchbase databases, you should be familiar with the JSON documents. These NoSQL databases primarily uses JSON structured data. It becomes necessary for SQL database administrators as well to understand JSON and use SQL Server function in conjunction with JSON.
- It consists of key-value pairs.
- It is a bridge between No-SQL and relational databases. You can refer to article SQL Server JSON functions: a bridge between NoSQL and relational worlds for more details
- Each key should be enclosed in a double-quote
- We can have a string, object, array, Boolean or number data format in a value
- Each key should use colon (:) to segregate from the values. For example “Name”:”Rajendra”
Before we go further, I will give a small overview of JSON object and array.
JSON Object:
In JSON, each object is enclosed by curly brackets({}). In the below example, we have a key (Name), and its value is a JSON object (nested JSON)
123"Name" : {"FirstName" : "Rajendra"}JSON Array
It is an ordered list of values to store multiple values. We use square brackets to represent an array in the JSON Data. In the following example, we have an employee array that has employees’ records.
12345678910111213141516{ "employees":[ {"name":"Raj","email":"raj@gmail.com","age":32},{"name":"Mohan","email":"Mohan@yahoo.com","age":21}]}
SQL Server provides the following JSON functions to work with JSON Data:
- ISJSON(): we can check valid JSON using this function
- JSON_VALUE(): It extracts a scalar value from the JSON data
- JSON_MODIFY(): It modifies values in the JSON Data. You should go through Modifying JSON data using JSON_MODIFY() in SQL Server for this function
- JSON_QUERY: It extracts an array or string from JSON in SQL Server
We can view these functions in the following image of Microsoft docs.
We explored JSON_VALUE() and JSON_MODIFY() functions in my previous articles. You can refer to JSON for these articles. In this article, we are exploring JSON_QUERY() function using various examples.
Syntax of JSON_QUERY()
JSON_QUERY (expression ,[Path Mode] JSON_path)
- Expression: It is a JSON string or a variable holding JSON data
- JSON_Path: It is the path of the object or an array from where we want to retrieve values
- Path mode: It controls the output of a JSON_QUERY() function in case of an invalid JSON string using the LAX and Strict arguments
Example 1: Get the JSON object from a JSON string
In this example, we require to retrieve the first JSON object from the [employees] key.
- A variable @data contains an array for the “employees” key
- We can note the array is enclosed in a square bracket
- JSON array follows zero-based indexing. To retrieve the first record, we use employees[0] argument
- Similarly, we can access the second record using the employees[1] argument
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @data NVARCHAR(4000); SET @data = N'{ "employees": [ { "name":"Raj", "email":"raj@gmail.com", "age":32 }, { "name":"Mohan", "email":"Mohan@yahoo.com", "age":21 } ] }'; SELECT JSON_QUERY(@data, '$.employees[0]') AS 'Result'; |
It retrieves the first JSON object and gives you the following output.
We can retrieve the whole JSON document by removing the second argument. It is an optional argument, so we do not get any error. We can also use the ‘$’ argument to get the whole JSON string.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @data NVARCHAR(4000); SET @data = N'{ "employees": [ { "name":"Raj", "email":"raj@gmail.com", "age":32 }, { "name":"Mohan", "email":"Mohan@yahoo.com", "age":21 } ] }'; SELECT JSON_QUERY(@data) AS 'Result'; |
You can see the whole document as part of the JSON_QUERY() function.
As you know, we cannot use the JSON_VALUE() function to retrieve an object or array. It retrieves a scalar value from a JSON string. You get NULL value in the output if we replace the JSON_MODIFY() function with JSON_VALUE().
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @data NVARCHAR(4000); SET @data = N'{ "employees": [ { "name":"Raj", "email":"raj@gmail.com", "age":32 }, { "name":"Mohan", "email":"Mohan@yahoo.com", "age":21 } ] }'; SELECT JSON_VALUE(@data,'$.employees') AS 'Result'; |
It returns a NULL value in the output, as shown below.
Example 2: Retrieve a scalar value using the JSON_QUERY() function
As you know, we use JSON_VALUE() function to retrieve a scalar value. If we try to retrieve the scalar value using JSON_QUERY() function, let’s see the output.
|
1 2 3 4 |
DECLARE @json_data NVARCHAR(4000) = '{"Id":1,"Brand":"HP", "City":["Laptop","Mobile"]}' SELECT JSON_QUERY(@json_data,'$.Name') as Name |
It also gives you NULL value in the output If we try to get a scalar value from the JSON_QUERY() function.
By default, JSON_QUERY() function uses a default path mode lax. In this mode, SQL Server does not raise an error in case of any invalid key or value. We might want to raise an error message instead of getting a NULL value in the output. We can use strict mode for getting the error message.
Example 3: Retrieve JSON by the $ symbol using the JSON_QUERY() function
In the following example, we have a JSON string that contains key-value pairs, a JSON array and JSON object.
|
1 2 3 4 5 6 7 8 9 |
DECLARE @data NVARCHAR(4000) = '{"Employees": [{"EmpId":1,"Name":"Raj", "Address":{"City":"Gurgaon","Country":"India"}}, {"EmpId":2,"Name":"Sohan", "Address":{"Village":"Sohna","City":"Jaipur","Country":"India"}} ] }' SELECT JSON_QUERY(@data,'$') Employees_String |
First, we use the $ argument in the second parameter, and we get the complete JSON string, as shown below.
In the output, we can also notice the message – 1 row affected. It treats the entire JSON string as a single row in SQL Server.
Now, we want to retrieve the Employees array. In this case, we can specify the array that we want to retrieve with the $ symbol. In the below, we specify it as $.Employees.
|
1 2 3 4 5 6 7 8 9 |
DECLARE @data NVARCHAR(4000) = '{"Employees": [{"EmpId":1,"Name":"Raj", "Address":{"City":"Gurgaon","Country":"India"}}, {"EmpId":2,"Name":"Sohan", "Address":{"Village":"Sohna","City":"Jaipur","Country":"India"}} ] }' SELECT JSON_QUERY(@data,'$.Employees') Employees_Array |
In the output, we get the employees array without the key. We can note that the array starts and end with a square bracket.
Further to this example, we need to retrieve the second row (JSON object]. As we already know, JSON uses a zero-based indexing process, and we can specify the second argument $.Employees[1].
|
1 2 3 4 5 6 7 8 9 |
DECLARE @data NVARCHAR(4000) = '{"Employees": [{"EmpId":1,"Name":"Raj", "Address":{"City":"Gurgaon","Country":"India"}}, {"EmpId":2,"Name":"Sohan", "Address":{"Village":"Sohna","City":"Jaipur","Country":"India"}} ] }' SELECT JSON_QUERY(@data,'$.Employees[1]') Employees_Object |
We can further filter the JSON and get the customer’s address JSON object. Here, we can specify further argument as $.Employees[1].Address.
|
1 2 3 4 5 6 7 8 9 |
DECLARE @data NVARCHAR(4000) = '{"Employees": [{"EmpId":1,"Name":"Raj", "Address":{"City":"Gurgaon","Country":"India"}}, {"EmpId":2,"Name":"Sohan", "Address":{"Village":"Sohna","City":"Jaipur","Country":"India"}} ] }' SELECT JSON_QUERY(@data,'$.Employees[1].Address') Employees_Object |
JSON query in AdventureWorks sample database
We can use the JSON_Query() function in the AdventureWorks sample database. For this demo, you can use the below steps to prepare the same database.
-
Download the backup file of AdventureWorks2016_EXT from the GitHub
Restore this database in your SQL instance in RECOVERY mode
- Download [sql-server-2016-samples.zip] from GitHub
Extract the folder and execute all scripts(except cleanup.sql) from the JSON folder in the AdventureWorks2016_EXT. You also need a full-text search service for json.indexes.sql, but it is ok for you to ignore the errors related to full text for this article
At this step, we can use the JSON function to query data in the AdventureWorks2016_EXT sample database.
|
1 2 3 |
SELECT TOP 10 JSON_QUERY(OrderItems, '$') OrderItems, JSON_QUERY(info, '$') Info FROM Sales.SalesOrder_json; |
SQL Functions in combination with JSON_QUERY() function
We can use SQL functions such as SQL CONCAT in combination with JSON_QUERY functions. You can download WideWorldImporters database.
|
1 2 3 |
SELECT TOP 1 JSON_QUERY(Tags) AS Tags, JSON_QUERY(CONCAT('["ValidFrom","', ValidFrom, '","', "ValidTo", ValidTo, '"]')) ValidityPeriod FROM Warehouse.StockItems; |
We can use FOR JSON PATH argument to format the output in the JSON format. It also allows you to provide a root element in the JSON string. In the below query, we use the root as Items.
|
1 2 3 4 |
SELECT TOP 1 JSON_QUERY(Tags) AS Tags, JSON_QUERY(CONCAT('["ValidFrom","', ValidFrom, '","', "ValidTo", ValidTo, '"]')) ValidityPeriod FROM Warehouse.StockItems FOR json PATH,Root('Items') |
You can click on the JSON hyperlink and it gives you JSON as shown below.
You can copy the JSON and paste it in the JSON formatter. It gives you the following formatted JSON.
Difference between JSON_VALUE and JSON_Modify functions
|
JSON_VALUE function |
JSON_MODIFY function |
||||
|
It returns a scalar value from JSON. |
We get an object or an array from the JSON. |
||||
|
Output data type – Nvarhcar(4000) |
Output data type – Nvarchar(max) |
||||
|
It returns a NULL Value if we try to return an array or object.
|
It returns a NULL value in the output if we try to retrieve a scalar value.
|
||||
|
We cannot retrieve a JSON object using the JSON_VALUE() function. It returns a NULL value in this case.
|
We cannot retrieve an array element in using this function. We get the NULL value for the array element.
|
Conclusion
In this article, we explored JSON_QUERY() function to retrieve the JSON object and array from JSON data. It is a useful function to work with SQL Server JSON data. You should explore these functions and be familiar with the Javascript object Notation.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023