In MariaDB, the JSON_VALUE() function and JSON_QUERY() function do similar things – they return data from a JSON document.

So what’s the difference?

The main difference is that JSON_VALUE() returns scalar values, whereas JSON_QUERY() returns arrays and objects.

Definitions

Given a JSON document, each function does the following:

• JSON_VALUE() returns the scalar specified by the path.
• JSON_QUERY() returns an object or array specified by the path.

My understanding is that it works this way due to the SQL standard.

If this causes problems for you, you might find the JSON_EXTRACT() function more useful.

Scalars

Here’s an example to demonstrate what happens when we attempt to use both functions to extract a scalar from a JSON document.

SET @json_document = '
    { 
        "name" : "Rufus",
        "awards" : [ "Top Dog", "Best Dog", "Biggest Dog" ] 
    }
';
SELECT 
    JSON_VALUE(@json_document, '$.name') AS JSON_VALUE,
    JSON_QUERY(@json_document, '$.name') AS JSON_QUERY;

Result:

+------------+------------+
| JSON_VALUE | JSON_QUERY |
+------------+------------+
| Rufus      | NULL       |
+------------+------------+

JSON_VALUE() returned the scalar as expected, but JSON_QUERY() returned NULL. This is expected, because JSON_QUERY() only returns arrays and objects.

It’s the same result if we try to return scalar data from the array:

SET @json_document = '
    { 
        "name" : "Rufus",
        "awards" : [ "Top Dog", "Best Dog", "Biggest Dog" ] 
    }
';
SELECT 
    JSON_VALUE(@json_document, '$.awards[0]') AS JSON_VALUE,
    JSON_QUERY(@json_document, '$.awards[0]') AS JSON_QUERY;

Result:

+------------+------------+
| JSON_VALUE | JSON_QUERY |
+------------+------------+
| Top Dog    | NULL       |
+------------+------------+

Objects

Here’s what happens when we attempt to use both functions to return a whole object:

SET @json_document = '{ "name" : "Rufus" }';

SELECT 
    JSON_VALUE(@json_document, '$') AS JSON_VALUE,
    JSON_QUERY(@json_document, '$') AS JSON_QUERY;

Result:

+------------+----------------------+
| JSON_VALUE | JSON_QUERY           |
+------------+----------------------+
| NULL       | { "name" : "Rufus" } |
+------------+----------------------+

This time it’s the JSON_QUERY() function that succeeds.

Arrays

Here’s what happens when we attempt to use both functions to return a whole array:

SET @json_document = '
    { 
        "name" : "Rufus",
        "awards" : [ "Top Dog", "Best Dog", "Biggest Dog" ] 
    }
';
SELECT 
    JSON_VALUE(@json_document, '$.awards') AS JSON_VALUE,
    JSON_QUERY(@json_document, '$.awards') AS JSON_QUERY;

Result:

+------------+------------------------------------------+
| JSON_VALUE | JSON_QUERY                               |
+------------+------------------------------------------+
| NULL       | [ "Top Dog", "Best Dog", "Biggest Dog" ] |
+------------+------------------------------------------+

Again, the JSON_QUERY() function succeeds.

However, neither function succeeds when we use the array wildcard operator to select all scalar elements from the array. In this case, the JSON_EXTRACT() comes to the rescue:

SET @json_document = '
    { 
        "name" : "Rufus",
        "awards" : [ "Top Dog", "Best Dog", "Biggest Dog" ] 
    }
';
SELECT 
    JSON_VALUE(@json_document, '$.awards[*]') AS JSON_VALUE,
    JSON_QUERY(@json_document, '$.awards[*]') AS JSON_QUERY,
    JSON_EXTRACT(@json_document, '$.awards[*]') AS JSON_EXTRACT;

Result:

+------------+------------+----------------------------------------+
| JSON_VALUE | JSON_QUERY | JSON_EXTRACT                           |
+------------+------------+----------------------------------------+
| Top Dog    | NULL       | ["Top Dog", "Best Dog", "Biggest Dog"] |
+------------+------------+----------------------------------------+

However, if the array’s elements are arrays or objects, then JSON_QUERY() returns them fine:

SET @json_document = '
    { 
        "name" : "Rufus",
        "scores" : [ [1, 2, 3], [8, 9], { "a" : 1 } ] 
    }
';
SELECT 
    JSON_VALUE(@json_document, '$.scores') AS JSON_VALUE,
    JSON_QUERY(@json_document, '$.scores') AS JSON_QUERY;

Result:

+------------+------------------------------------+
| JSON_VALUE | JSON_QUERY                         |
+------------+------------------------------------+
| NULL       | [ [1, 2, 3], [8, 9], { "a" : 1 } ] |
+------------+------------------------------------+