In MySQL, the JSON_KEYS() function returns keys from the top level value of a JSON object. The keys are returned as a JSON array, or, if a path argument is given, the top-level keys from the selected path.

You provide the JSON document as an argument to the function.

You can also (optionally) provide a second argument to specify where the “top-level” path starts from within the JSON document.

Syntax

The syntax goes like this:

JSON_KEYS(json_doc[, path])

Where json_doc is the JSON document and path is an optional argument to determine where the “top-level” path starts within the JSON document.

The result array is empty if the selected object is empty. If the top-level value has nested subobjects, the return value does not include keys from those subobjects.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT JSON_KEYS('{"a": 1, "b": 2, "c": 3}') AS 'Result';

Result:

+-----------------+
| Result          |
+-----------------+
| ["a", "b", "c"] |
+-----------------+

Here’s another example:

SELECT 
  JSON_KEYS('{"Name": "Bart", "Age": 10}') AS 'Result';

Result:

+-----------------+
| Result          |
+-----------------+
| ["Age", "Name"] |
+-----------------+

Example 2 – Specify a Path

Here’s an example of specifying a path. This allows us to get the keys from a nested object (instead of being limited to just the top-level object of the whole JSON document).

SELECT 
  JSON_KEYS('{"a": 1, "b": 2, "c": {"d": 3, "e": 4, "f": 5}}', '$.c') AS 'Result';

Result:

+-----------------+
| Result          |
+-----------------+
| ["d", "e", "f"] |
+-----------------+

Example 3 – Non-Existent Path

If you specify a path that doesn’t exist a NULL value is returned.

SELECT 
  JSON_KEYS('{"a": 1, "b": 2, "c": {"d": 3, "e": 4, "f": 5}}', '$.z') AS 'Result';

Result:

+--------+
| Result |
+--------+
| NULL   |
+--------+

You’ll also get a NULL value if any of the arguments are NULL or if the first argument is not a JSON object.

Example 4 – Arrays

Arrays are not objects, and they don’t contain key/value pairs. Therefore, you’ll get a NULL value if you try to return keys from an array.

SELECT JSON_KEYS('[1, 2, 3]') AS 'Result';

Result:

+--------+
| Result |
+--------+
| NULL   |
+--------+

However, if any of the array’s elements contains an object, you can still use the path argument to get the keys from that object.

SELECT JSON_KEYS('[1, 2, {"a": 1, "b": 2}]', '$[2]') AS 'Result';

Result:

+------------+
| Result     |
+------------+
| ["a", "b"] |
+------------+

Example 5 – Empty Objects

If the selected object is empty, you’ll get an empty array.

SELECT JSON_KEYS('{}') AS 'Result';

Result:

+--------+
| Result |
+--------+
| []     |
+--------+