JSON_KEYS

Description

Returns all keys of a JSON object in array form. By default, it returns the keys of the root object, but you can also control which specific path's object keys to return through parameters.

Syntax

JSON_KEYS(<json_object>[, <path>])

Parameters

Required Parameters

• <json_object> JSON type, the JSON object from which keys need to be extracted.

Optional Parameters

• <path> String type, optional JSON path that specifies the JSON subdocument to check. If not provided, defaults to the root document.

Return Value

• Array Returns an array of strings, where the array members are all the keys of the JSON object.

Notes

• Returns NULL when <json_object> or <path> is NULL.
• Returns NULL if it's not a JSON object (e.g., if it's a JSON array).
• Returns NULL if the object pointed to by <path> does not exist.

Examples

1. Example 1

   SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');

   +---------------------------------------+
   | JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
   +---------------------------------------+
   | ["a", "b"]                            |
   +---------------------------------------+

   SELECT JSON_KEYS('{}');

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

2. Specify path

   SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');

   +----------------------------------------------+
   | JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
   +----------------------------------------------+
   | ["c"]                                        |
   +----------------------------------------------+

3. NULL parameters

   SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', NULL);

   +---------------------------------------------+
   | JSON_KEYS('{"a": 1, "b": {"c": 30}}', NULL) |
   +---------------------------------------------+
   | NULL                                        |
   +---------------------------------------------+

   SELECT JSON_KEYS(NULL);

   +-----------------+
   | JSON_KEYS(NULL) |
   +-----------------+
   | NULL            |
   +-----------------+

4. Not a JSON object

   SELECT JSON_KEYS('[1,2]');

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

   SELECT JSON_KEYS('{"k": [1, 2, 3]}', '$.k');

   +--------------------------------------+
   | JSON_KEYS('{"k": [1, 2, 3]}', '$.k') |
   +--------------------------------------+
   | NULL                                 |
   +--------------------------------------+

5. Object specified by path does not exist

   SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.c');

   +----------------------------------------------+
   | JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.c') |
   +----------------------------------------------+
   | NULL                                         |
   +----------------------------------------------+