MariaDB JSON_QUERY() Explained

In MariaDB, JSON_QUERY() is a built-in function that returns an object or array from a JSON document, based on the path provided.

It’s similar to the JSON_VALUE() function, except that it returns an object or array instead of a scalar (JSON_VALUE() returns a scalar).

Syntax

The syntax goes like this:

JSON_QUERY(json_doc, path)

Where json_doc is the JSON document, and path is a path within the document.

Example

Here’s an example to demonstrate.

SET @json_document = '
    { 
        "_id" : 1, 
        "name" : "Wag", 
        "details" : {
            "type" : "Dog", 
            "weight" : 20,
            "awards" : { 
                "Florida Dog Awards" : "Top Dog", 
                "New York Marathon" : "Fastest Dog", 
                "Sumo 2020" : "Biggest Dog"
            }
        }
    }
';
SELECT JSON_QUERY(@json_document, '$.details');

Result:

{
    "type" : "Dog", 
    "weight" : 20,
    "awards" : { 
        "Florida Dog Awards" : "Top Dog", 
        "New York Marathon" : "Fastest Dog", 
        "Sumo 2020" : "Biggest Dog"
    }
}

We can use dot notation to go down to the next nested object:

SET @json_document = '
    { 
        "_id" : 1, 
        "name" : "Wag", 
        "details" : {
            "type" : "Dog", 
            "weight" : 20,
            "awards" : { 
                "Florida Dog Awards" : "Top Dog", 
                "New York Marathon" : "Fastest Dog", 
                "Sumo 2020" : "Biggest Dog"
            }
        }
    }
';
SELECT JSON_QUERY(@json_document, '$.details.awards');

Result:

{ 
    "Florida Dog Awards" : "Top Dog", 
    "New York Marathon" : "Fastest Dog", 
    "Sumo 2020" : "Biggest Dog"
}

Arrays

Here’s an example of returning an array:

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

Result:

+------------------------------------------+
| JSON_QUERY(@json_document, '$.awards')   |
+------------------------------------------+
| [ "Top Dog", "Best Dog", "Biggest Dog" ] |
+------------------------------------------+

If you want to return an actual array element, try the JSON_VALUE() function.

Non-Existent Path

Passing a path that doesn’t exist in the JSON document results in NULL.

Example:

SET @json_document = '
    { 
        "_id" : 1, 
        "awards" : [ "Top Dog", "Best Dog", "Biggest Dog" ] 
    }
';
SELECT JSON_QUERY(@json_document, '$.type');

Result:

+--------------------------------------+
| JSON_QUERY(@json_document, '$.type') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+

Scalar Values

Attempting to return a scalar value returns NULL.

Example:

SELECT JSON_QUERY('{ "weight": 10 }', '$.weight');

Result:

+--------------------------------------------+
| JSON_QUERY('{ "weight": 10 }', '$.weight') |
+--------------------------------------------+
| NULL                                       |
+--------------------------------------------+

To return a scalar value, use the JSON_VALUE() function.

Null Arguments

If any argument is NULL, the result is NULL:

SELECT 
    JSON_QUERY(null, '$.type'),
    JSON_QUERY('{"a":1}', null);

Result:

+----------------------------+-----------------------------+
| JSON_QUERY(null, '$.type') | JSON_QUERY('{"a":1}', null) |
+----------------------------+-----------------------------+
| NULL                       | NULL                        |
+----------------------------+-----------------------------+

Incorrect Parameter Count

Providing no arguments results in an error:

SELECT JSON_QUERY();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_QUERY'

It’s the same when you provide too few or too many arguments:

SELECT JSON_QUERY('{ "a": 1}');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_QUERY'