JSON_QUERY() vs JSON_VALUE() in SQL Server: What’s the Difference?

Two of the many T-SQL functions available in SQL Server are JSON_QUERY() and JSON_VALUE(). These functions can be used to extract data from JSON documents.

Their general syntax is similar, and at first glance, you might think they do exactly the same thing, but they don’t. There’s definitely a place for both functions when working with JSON and SQL Server.

This article looks at the difference between JSON_QUERY() and JSON_VALUE().

The Difference

These two functions have slightly different definitions, a slightly different syntax, and their return values are slightly different.

Definitions

Here’s how the two functions are defined:

JSON_QUERY(): Extracts an object or an array from a JSON string.
JSON_VALUE(): Extracts a scalar value from a JSON string.

So the difference between these two functions is what they extract. One extracts an object or an array, the other extracts a scalar value.

Syntax Differences

Another difference is in the syntax:

JSON_QUERY ( expression [ , path ] )
JSON_VALUE ( expression , path )

Look at the JSON_QUERY() syntax. Those square brackets around the path argument mean that it’s an optional argument. That’s because this function can return a whole JSON document if required.

However, the path argument is a required argument when using the JSON_VALUE() function. So you must provide both arguments when using this function.

Return Values

And one more difference is in their return values.

JSON_QUERY() returns a JSON fragment of type nvarchar(max)
JSON_VALUE() returns a single text value of type nvarchar(4000)

Example 1 – Extract a Scalar Value

Here’s an example to demonstrate the difference between these functions when trying to extract a scalar value.

SELECT 
  JSON_VALUE('{"Name": "Homer"}', '$.Name') AS 'JSON_VALUE',
  JSON_QUERY('{"Name": "Homer"}', '$.Name') AS 'JSON_QUERY';

Result:

+--------------+--------------+
| JSON_VALUE   | JSON_QUERY   |
|--------------+--------------|
| Homer        | NULL         |
+--------------+--------------+

So both functions are trying to extract the same value from the JSON document, but only one succeeds: JSON_VALUE(). This is because the value they’re trying to extract is a scalar value. Basically, a scalar value is one unit of data. It could be a string of text or a number. But it can’t be an object or an array.

Example 2 – Extract an Array

In this example, both functions try to extract a whole array.

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

Result:

+--------------+----------------------------------------+
| JSON_VALUE   | JSON_QUERY                             |
|--------------+----------------------------------------|
| NULL         | ["Eating", "Sleeping", "Base Jumping"] |
+--------------+----------------------------------------+

In this case, only the JSON_QUERY() function succeeds.

Example 3 – Extract an Array Item

This example is similar to the previous one, except that instead of trying to extract the whole array, we only want a single item from the array.

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

Result:

+--------------+--------------+
| JSON_VALUE   | JSON_QUERY   |
|--------------+--------------|
| Base Jumping | NULL         |
+--------------+--------------+

So this time JSON_VALUE() is the winner.

Example 4 – Extract an Object

Let’s try for a whole object.

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

Result:

+--------------+--------------+
| JSON_VALUE   | JSON_QUERY   |
|--------------+--------------|
| NULL         | {    
       "Name": "Homer Simpson",
       "Hobbies": ["Eating", "Sleeping", "Base Jumping"]  
    }              |
+--------------+--------------+

And JSON_QUERY() wins.

(Excuse the formatting, this is how my MSSQL command line tool returns the results).

Example 5 – Extract the Whole JSON Document

Let’s try for the whole JSON document.

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_VALUE(@data, '$') AS 'JSON_VALUE', 
  JSON_QUERY(@data, '$') AS 'JSON_QUERY';

Result:

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

So JSON_QUERY() is the only one that can return the whole document.

Example 6 – Omit the Path

Another difference between these two functions is that, the path argument is optional when using JSON_QUERY(). If you omit this, the whole JSON document is returned.

You can’t omit this argument when using JSON_VALUE(), as it is a required argument. This is probably due to the fact that the function can only return a scalar value. If the first argument only consisted of a scalar value, it wouldn’t be valid JSON.

Anyway, here’s an example of omitting the path argument from JSON_QUERY():

SELECT JSON_QUERY('{"Name": "Homer"}') AS 'Result';

Result:

+-------------------+
| Result            |
|-------------------|
| {"Name": "Homer"} |
+-------------------+

And here’s what happens if we try that trick with JSON_VALUE():

SELECT JSON_VALUE('{"Name": "Homer"}') AS 'Result';

Result:

Msg 174, Level 15, State 1, Line 1
The json_value function requires 2 argument(s).

Example 7 – Path Mode

In the earlier examples, when a function couldn’t handle the supplied path, it returned NULL. This is because all of those examples were run in lax mode (the default mode).

If we’d run them in strict mode, we would’ve received an error instead. To explicitly specify the path mode, simply add it before the dollar sign (and leave a space between them).

Here’s an example of what happens when you provide an invalid path while in strict mode:

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

Result:

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