MariaDB JSON_KEYS() Function

In MariaDB, JSON_KEYS() is a built-in function that returns an array containing the topmost member (key) of the specified JSON object.

MariaDB JSON_KEYS() Syntax

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

JSON_KEYS(json)
JSON_KEYS(json, path)

Parameters

json

Required. A JSON object document.

path

Optional. A path expression whose keys you want.

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_KEYS'.

Return value

The MariaDB JSON_KEYS() function returns an array containing the topmost member (key) of the specified JSON object. If a path expression is specified, returns an array of the topmost members of the JSON object matched by the path expression.

The JSON_KEYS() function will NULL return if:

  • No path was specified and the JSON document is not a JSON object.
  • A path was specified and the JSON value matched by the path was not a JSON object.
  • Any parameters are NULL.

MariaDB JSON_KEYS() Examples

Here are some common examples to show the usages of the Mariadb JSON_KEYS() function.

Basic example

The following example returns an array of all top-level members of a JSON object.

SELECT JSON_KEYS('{"x": 1, "y": 2, "z": 3}');

Output:

+---------------------------------------+
| JSON_KEYS('{"x": 1, "y": 2, "z": 3}') |
+---------------------------------------+
| ["x", "y", "z"]                       |
+---------------------------------------+

Specified path

We can also return keys of JSON objects matching the gaven path expressions.

SELECT JSON_KEYS('[0, {"x": 1, "y": 2, "z": 3}]', '$[1]');

Output:

+----------------------------------------------------+
| JSON_KEYS('[0, {"x": 1, "y": 2, "z": 3}]', '$[1]') |
+----------------------------------------------------+
| ["x", "y", "z"]                                    |
+----------------------------------------------------+

non-JSON object

The JSON_KEYS() returns NULL if the matching JSON document is not a JSON object. This example illustrates the point:

SELECT
    JSON_KEYS('1') as `keys of 1`,
    JSON_KEYS('true') as `keys of true`,
    JSON_KEYS('"hello"') as `keys of "hello"`,
    JSON_KEYS('[1, 2]') as `keys of [1, 2]`;

Output:

+-----------+--------------+-----------------+----------------+
| keys of 1 | keys of true | keys of "hello" | keys of [1, 2] |
+-----------+--------------+-----------------+----------------+
| NULL      | NULL         | NULL            | NULL           |
+-----------+--------------+-----------------+----------------+

Empty object

If the given object is empty, MariaDB JSON_KEYS() will return NULL:

SELECT JSON_KEYS('{}');

Output:

+-----------------+
| JSON_KEYS('{}') |
+-----------------+
| []              |
+-----------------+

NULL parameters

MariaDB JSON_KEYS() will return NULL if any argument is NULL:

SELECT
    JSON_KEYS(NULL),
    JSON_KEYS(NULL, '$'),
    JSON_KEYS('{}', NULL);

Output:

+-----------------+----------------------+-----------------------+
| JSON_KEYS(NULL) | JSON_KEYS(NULL, '$') | JSON_KEYS('{}', NULL) |
+-----------------+----------------------+-----------------------+
| NULL            | NULL                 | NULL                  |
+-----------------+----------------------+-----------------------+

Conclusion

In MariaDB, JSON_KEYS() is a built-in function that returns an array containing the topmost member (key) of the specified JSON object.