Rajendra Gupta
JSON_VALUE function output

The JSON_QUERY() function to extract objects from JSON Data

May 19, 2020 by

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)

  • 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.

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.

Overview of built-in JSON support

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

It retrieves the first JSON object and gives you the following output.

JSON_QUERY 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.

You can see the whole document as part of the JSON_QUERY() function.

JSON_QUERY() function for JSON data

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().

It returns a NULL value in the output, as shown below.

NULL values in JSON_QUERY() function

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.

It also gives you NULL value in the output If we try to get a scalar value from the JSON_QUERY() function.

Scalar value

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.

Strict mode

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.

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.

Retrieve JSON by the $ symbol

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.

In the output, we get the employees array without the key. We can note that the array starts and end with a square bracket.

JSON Array without root key

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].

We can further filter the JSON and get the customer’s address JSON object. Here, we can specify further argument as $.Employees[1].Address.

View output

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

    Download database

  • Restore this database in your SQL instance in RECOVERY mode

    Restore database

  • 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

    Download scripts

At this step, we can use the JSON function to query data in the AdventureWorks2016_EXT sample database.

View sample data

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.

SQL functions

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.

FOR JSON PATH argument in JSON Data

You can click on the JSON hyperlink and it gives you JSON as shown below.

View JSON hyperlink

You can copy the JSON and paste it in the JSON formatter. It gives you the following formatted JSON.

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.

JSON_VALUE function output

It returns a NULL value in the output if we try to retrieve a scalar value.

JSON_Modify function output

We cannot retrieve a JSON object using the JSON_VALUE() function. It returns a NULL value in this case.

JSON_Value() function

We cannot retrieve an array element in using this function. We get the NULL value for the array element.

JSON_Modify() function

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.

Rajendra Gupta
21,434 Views