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
-
Example 1
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"] |
+---------------------------------------+SELECT JSON_KEYS('{}');
+-----------------+
| JSON_KEYS('{}') |
+-----------------+
| [] |
+-----------------+ -
Specify path
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
+----------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
+----------------------------------------------+
| ["c"] |
+----------------------------------------------+ -
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 |
+-----------------+ -
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 |
+--------------------------------------+ -
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 |
+----------------------------------------------+