SQL Server JSON_QUERY() Function

Summary: in this tutorial, you will learn how to use the SQL Server JSON_QUERY() function to extract an object or an array from a JSON string.

Introduction to the SQL Server JSON_QUERY() function

The JSON_QUERY() function allows you to extract a JSON object or a JSON array from a JSON string.

Here’s the basic syntax of the JSON_QUERY() function:

JSON_QUERY ( expression , path )Code language: SQL (Structured Query Language) (sql)

In this syntax:

• expression: This is a JSON string, a variable that holds a JSON string, or a column that stores JSON data from which to extract the JSON array or JSON object.
• path: This is the path to the JSON object or array that you want to extract. The path is optional, which defaults to $.

The JSON_QUERY() returns a JSON object or array as a character string. If the path is invalid, the JSON_QUERY() function returns an error.

To extract a scalar value from a JSON string, you can use the JSON_VALUE() function.

SQL Server JSON_QUERY() function examples

Let’s take some examples of using the JSON_QUERY() function.

1) Basic JSON_QUERY() function examples

The following example uses the JSON_QUERY() function to extract the JSON object spec from a JSON string:

SELECT 
  JSON_QUERY(
    '{"name": "Tablet", "spec": {"weight": "1.07 pounds", "display": "10.2-inch", "bands": [, 2, 3, 4, 5, 7, 8, 11, 12, 13, 14, 17, 18, 19, 20, 21, 25, 26, 29, 30, 34, 38, 39, 40, 41, 66, 71]}}', 
    '$.spec'
  ) AS spec;Code language: SQL (Structured Query Language) (sql)

Output:

spec

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
{"weight": "1.07 pounds", "display": "10.2-inch", "bands": [1, 2, 3, 4, 5, 7, 8, 11, 12, 13, 14, 17, 18, 19, 20, 21, 25, 26, 29, 30, 34, 38, 39, 40, 41, 66, 71]}Code language: SQL (Structured Query Language) (sql)

The following example uses the JSON_QUERY() function to extract the JSON array bands from the product JSON string:

SELECT 
  JSON_QUERY(
    '{"name": "Tablet", "spec": {"weight": "1.07 pounds", "display": "10.2-inch", "bands": [1, 2, 3, 4, 5, 7, 8, 11, 12, 13, 14, 17, 18, 19, 20, 21, 25, 26, 29, 30, 34, 38, 39, 40, 41, 66, 71]}}', 
    '$.spec.bands'
  ) AS bands;Code language: SQL (Structured Query Language) (sql)

Output:

bands
-----------------------------------------------------------------------------------------------------
[1, 2, 3, 4, 5, 7, 8, 11, 12, 13, 14, 17, 18, 19, 20, 21, 25, 26, 29, 30, 34, 38, 39, 40, 41, 66, 71]Code language: SQL (Structured Query Language) (sql)

2) Using SQL Server JSON_QUERY() function with table data example

First, create a new table called Products:

CREATE TABLE Products (
    ProductID INT IDENTITY PRIMARY KEY,
    ProductDetails NVARCHAR(MAX)
);Code language: SQL (Structured Query Language) (sql)

The Products table has two columns ProductID and ProductDetails. The ProductDetails column will store JSON data.

Second, insert three rows into the Products table:

INSERT INTO Products 
VALUES 
('{"name": "Laptop", "price": 999.99, "specs": {"processor": "Intel i7", "RAM": "16GB", "storage": "512GB SSD"}}'),
('{"name": "Smartphone", "price": 699.99, "colors": ["Black", "White", "Blue"]}'),
('{"name": "Headphones", "price": 129.99, "features": ["Noise-cancellation", "Wireless", "Long battery life"]}');Code language: SQL (Structured Query Language) (sql)

Third, extract the specs object from the ProductDetails JSON using the JSON_QUERY() function:

SELECT ProductID, 
       JSON_QUERY(ProductDetails, '$.specs') AS Specs
FROM Products;Code language: SQL (Structured Query Language) (sql)

Output:

ProductID | Specs
--------------------------------------------------------------------------
1         | {"processor": "Intel i7", "RAM": "16GB", "storage": "512GB SSD"}
2         | NULL
3         | NULL
(3 rows)Code language: SQL (Structured Query Language) (sql)

In this example, we use the JSON path '$.specs' that locates the specs key in the JSON string. The function returns a JSON object.

Finally, extract the colors array from the ProductDetails JSON:

SELECT ProductID, 
       JSON_QUERY(ProductDetails, '$.colors') AS Colors
FROM Products;Code language: SQL (Structured Query Language) (sql)

Output:

ProductID | Colors
------------------------------------
1         | NULL
2         | ["Black", "White", "Blue"]
3         | NULL
(3 rows)Code language: SQL (Structured Query Language) (sql)

In this example, we use the JSON path '$.colors' to reference the colors array of the ProductDetails JSON object.

Summary

• Use the SQL Server JSON_QUERY() function to extract an object or an array from a JSON string.