MySQL JSON_KEYS() Function

In MySQL, the JSON_KEYS() function returns an array containing all top-level keys of the specified JSON object.

JSON_KEYS() Syntax

Here is the syntax of the MySQL JSON_KEYS() function:

JSON_KEYS(json)
JSON_KEYS(json, path)

Parameters

json
Required. The JSON object document whose keys will be returned.
path
Optional. The path expression that indicates whose keys will be returned in the JSON document.

Return value

In MySQL, the JSON_KEYS() function returns an array containing all top-level keys of the specified JSON object. If a path expression is specified, returns an array of top-level members in the JSON object matched by the path expression.

The JSON_KEYS() function will return NULL in the following cases:

  • No path was specified, and the JSON document is not a JSON object.
  • A path was specified, and the JSON value matching the path is not a JSON object.
  • Any parameter is NULL.

There will happen an error in the following cases:

  • MySQL will give an error if the parameter json is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.
  • If the parameter path is not a valid path expression, MySQL will give an error.

JSON_KEYS() Examples

Here are some examples of JSON_KEYS().

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

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

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

We can also return the keys of JSON objects that matchs the path expression.

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

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

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

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]`;

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