JSON_QUERY() Examples in SQL Server (T-SQL)

Posted on by Ian

When using JSON with SQL Server, you can use the JSON_QUERY() function to extract an object or an array from a JSON string.

To use this function, you provide the JSON expression as an argument. You can also provide a second (optional) argument to specify the object or array to extract.

Syntax

The syntax goes like this:

JSON_QUERY ( expression [ , path ] )

Where expression is the JSON string expression, and path is the object or array that you want to extract from that expression. The path argument is optional (if you don’t provide it, the whole JSON document is returned).

The path argument (if supplied) can include an optional path mode component. This optional path mode can be a value of either lax or strict. This value determines what happens in the event the supplied path is invalid. The path mode (if supplied) comes before the dollar sign.

Example 1 – Basic Usage

Here’s an example to demonstrate basic usage of the JSON_QUERY() function.

DECLARE @data NVARCHAR(4000)
SET @data=N'{
    "Cities": [
        {
            "Name": "Kabul",
            "CountryCode": "AFG",
            "District": "Kabol",
            "Population": 1780000
        },
        {
            "Name": "Qandahar",
            "CountryCode": "AFG",
            "District": "Qandahar",
            "Population": 237500
        }
    ]
}'
SELECT JSON_QUERY(@data, '$.Cities[0]') AS 'Result';

Result:

+----------+
| Result   |
|----------|
| {
            "Name": "Kabul",
            "CountryCode": "AFG",
            "District": "Kabol",
            "Population": 1780000
        }          |
+----------+

In this example, I first declare and set a variable called @data. I then assign an array to this variable. Once I’ve done this, I run a query against that array.

In this case I use Cities[0] to reference the first item in the array (JSON arrays use zero-based numbering).

I could access the second item by using Cities[1]. Like this:

DECLARE @data NVARCHAR(4000)
SET @data=N'{
    "Cities": [
        {
            "Name": "Kabul",
            "CountryCode": "AFG",
            "District": "Kabol",
            "Population": 1780000
        },
        {
            "Name": "Qandahar",
            "CountryCode": "AFG",
            "District": "Qandahar",
            "Population": 237500
        }
    ]
}'
SELECT JSON_QUERY(@data, '$.Cities[1]') AS 'Result';

Result:

+----------+
| Result   |
|----------|
| {
            "Name": "Qandahar",
            "CountryCode": "AFG",
            "District": "Qandahar",
            "Population": 237500
        }          |
+----------+

Example 2 – Return the Whole JSON Expression

The second argument is optional, so if you omit it, the whole JSON document is returned. Here’s what happens when we do that using the same data from the previous examples:

DECLARE @data NVARCHAR(4000)
SET @data=N'{
    "Cities": [
        {
            "Name": "Kabul",
            "CountryCode": "AFG",
            "District": "Kabol",
            "Population": 1780000
        },
        {
            "Name": "Qandahar",
            "CountryCode": "AFG",
            "District": "Qandahar",
            "Population": 237500
        }
    ]
}'
SELECT JSON_QUERY(@data) AS 'Result';

Result:

+----------+
| Result   |
|----------|
| {
    "Cities": [
        {
            "Name": "Kabul",
            "CountryCode": "AFG",
            "District": "Kabol",
            "Population": 1780000
        },
        {
            "Name": "Qandahar",
            "CountryCode": "AFG",
            "District": "Qandahar",
            "Population": 237500
        }
    ]
}          |
+----------+

Example 3 – A Database Example

If we were to put the data from the previous example into a database, we could rewrite the query as follows:

SELECT 
  JSON_QUERY(Document,'$.Cities[0]') AS 'City 1'
FROM Json_Documents

Result:

+----------+
| City 1   |
|----------|
| {
            "ID": 1,
            "Name": "Kabul",
            "CountryCode": "AFG",
            "District": "Kabol",
            "Population": 1780000
        }          |
+----------+

This assumes that the JSON document is stored in a column called Document, which is in a table called Json_Documents.

Example 4 – Scalar Values

The JSON_QUERY() function is not designed to return scalar values. If you want to return a scalar value, use the JSON_VALUE() function instead.

However, there’s nothing to stop you combining both functions within a query to return data at various levels of granularity.

Here’s an example:

DECLARE @data NVARCHAR(4000)
SET @data=N'{  
    "Suspect": {    
       "Name": "Homer Simpson",
       "Hobbies": ["Eating", "Sleeping", "Base Jumping"]  
    }
 }'
 SELECT 
   JSON_VALUE(@data,'$.Suspect.Name') AS 'Name',
   JSON_QUERY(@data,'$.Suspect.Hobbies') AS 'Hobbies',
   JSON_VALUE(@data,'$.Suspect.Hobbies[2]') AS 'Last Hobby';

Result:

+---------------+----------------------------------------+--------------+
| Name          | Hobbies                                | Last Hobby   |
|---------------+----------------------------------------+--------------|
| Homer Simpson | ["Eating", "Sleeping", "Base Jumping"] | Base Jumping |
+---------------+----------------------------------------+--------------+

In this example I used JSON_VALUE() to extract various scalar values, but I also used JSON_QUERY() to return a whole array (which JSON_VALUE() can’t do).

Example 5 – Path Mode

As mentioned, you also have the option of specifying the path mode. This can be either lax or strict.

The value of the path mode determines what happens when the path expression contains an error.  Specifically:

  • In lax mode, the function returns empty values if the path expression contains an error. For example, if you request the value $.name, and the JSON text doesn’t contain a name key, the function returns null, but does not raise an error.

  • In strict mode, the function raises an error if the path expression contains an error.

The default value is lax.

Here’s an example to demonstrate the difference between these two modes.

Error in lax mode

Here’s what happens when the path expression contains an error while in lax mode.

SELECT JSON_QUERY('{"Name": "Bruce"}', 'lax $.Name') AS 'Result';

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

In this example we’re trying to return a scalar value, but JSON_QUERY() doesn’t do scalar values. As mentioned, it only returns objects and arrays. In this case we get a null value (because we’re using lax mode).

Error in strict mode

Here’s what happens when we run the same code in strict mode.

SELECT JSON_QUERY('{"Name": "Bruce"}', 'strict $.Name') AS 'Result';

Result:

Msg 13624, Level 16, State 2, Line 1
Object or array cannot be found in the specified JSON path.

As expected, strict mode results in an error message explaining the error.