MariaDB JSON_QUERY() Function

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

Use JSON_VALUE() if you need to query scalars from JSON documents.

MariaDB JSON_QUERY() Syntax

Here is the syntax for the MariaDB JSON_QUERY() function:

JSON_QUERY(json_doc, path)

Parameters

json_doc

Required. A JSON document.

path

Required. You should specify at least one path expression.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_QUERY'.

Return value

The MariaDB JSON_QUERY() function returns an object or array from JSON documents based on the path provided.

If the path given in the JSON document is not an object or an array, JSON_QUERY() will return NULL.

The MariaDB JSON_QUERY() function will return NULL if the given JSON is invalid.

The MariaDB JSON_QUERY() function will return NULL if any argument is NULL.

MariaDB JSON_QUERY() Examples

The following examples show the usage of the MariaDB JSON_QUERY() function.

Basic example

SET @json_doc = '[1, 2, {"x": 3}]';
SELECT
  @json_doc AS 'Json',
  JSON_QUERY(@json_doc, '$') AS `$`,
  JSON_QUERY(@json_doc, '$[2]') AS `$[2]`;

Output:

+------------------+------------------+----------+
| Json             | $                | $[2]     |
+------------------+------------------+----------+
| [1, 2, {"x": 3}] | [1, 2, {"x": 3}] | {"x": 3} |
+------------------+------------------+----------+

If the path you give is not an object or array, MariaDB JSON_QUERY() will return NULL.

SET @json_doc = '[1, 2, {"x": 3}]';
SELECT
  @json_doc AS 'Json',
  JSON_QUERY(@json_doc, '$[0]') AS `$[0]`;

Output:

+------------------+------+
| Json             | $[0] |
+------------------+------+
| [1, 2, {"x": 3}] | NULL |
+------------------+------+

In this example, since the value of $[0] is 1, not an object or array, so it returns NULL.

Invalid JSON

The MariaDB JSON_QUERY() function will return NULL if the given JSON is invalid.

SELECT JSON_QUERY('a', '$[0]');

Output:

+-------------------------+
| JSON_QUERY('a', '$[0]') |
+-------------------------+
| NULL                    |
+-------------------------+

NULL parameters

The MariaDB JSON_QUERY() function will return NULL if any argument is NULL.

SELECT
    JSON_QUERY(NULL, '$'),
    JSON_QUERY('[1,2]', NULL);

Output:

+-----------------------+---------------------------+
| JSON_QUERY(NULL, '$') | JSON_QUERY('[1,2]', NULL) |
+-----------------------+---------------------------+
| NULL                  | NULL                      |
+-----------------------+---------------------------+

Conclusion

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