JSON_KEYS
Returns the top-level keys of a JSON object in the form of a JSON array.
Optionally, if a keypath is defined, returns the top-level keys from the keypath.
Syntax
JSON_KEYS(json_doc[, keypath_1, keypath_2, ..., keypath_n])
Arguments
json_doca JSON object which contains the keys to be extracted.keypath: an optional sequence of arguments that defines the path of a nested JSON object to extract the keys from. The arguments are comma-separated list of keys or zero-indexed array positions.
Remarks
There is no guarantee as to the order in which the keys are returned.
Return Value
A JSON array containing the top level keys of the JSON object. If the optional path argument(s) are provided, the function will return the top level-keys from the JSON sub-object at the path.
Returns an empty array if
json_docis empty.NULLifjson_docis eitherNULLor not a valid JSON object, or if the keypath does not locate a JSON object.
Examples
Example 1
Returns the top-level keys from a JSON object.
SELECT JSON_KEYS('{"letter": "beta", "lang": {"greek": 2}}');
****
+-------------------------------------------------------+
| JSON_KEYS('{"letter": "beta", "lang": {"greek": 2}}') |
+-------------------------------------------------------+
| ["lang","letter"] |
+-------------------------------------------------------+
Example 2
Returns the top-level keys from a JSON sub object "lang", which is specified in the keypath.
SELECT JSON_KEYS('{"letter": "beta", "lang": {"Greek": 2 , "Coptic" : 4}}', 'lang');
****
+------------------------------------------------------------------------------+
| JSON_KEYS('{"letter": "beta", "lang": {"Greek": 2 , "Coptic" : 4}}', 'lang') |
+------------------------------------------------------------------------------+
| ["Coptic","Greek"] |
+------------------------------------------------------------------------------+
Example 3
Returns NULL since there is a single value instead of a JSON object at the keypath 'lang.''Greek':
SELECT JSON_KEYS('{"Name": "beta", "lang": {"Greek": 2}}', 'lang','Greek');
****
+---------------------------------------------------------------------+
| JSON_KEYS('{"Name": "beta", "lang": {"Greek": 2}}', 'lang','Greek') |
+---------------------------------------------------------------------+
| NULL |
+---------------------------------------------------------------------+
Example 4
Returns the top-level key of the JSON object at the keypath 'lang.''Greek':
SELECT JSON_KEYS('{"letter": "beta", "lang": {"Greek": {"year" : 800}}}', 'lang','Greek');
****
+-------------------------------------------------------------------------------------+
| JSON_KEYS('{"letter": "beta", "lang": {"Greek": {"year" : 800}}}', 'lang','Greek') |
+-------------------------------------------------------------------------------------+
| ["year"] |
+-------------------------------------------------------------------------------------+
Example 5
Returns the top-level key of the second element of the JSON array 'letter'. 'beta'.
Note
Keypath array positions are zero-indexed; 1 is used in the last argument to indicate that the second element be returned.
SELECT JSON_KEYS('{"letter": {"beta": [{"year" : 800}, {"origin": "Greece"}]}}', 'letter', 'beta', 1);
****
+------------------------------------------------------------------------------------------------+
| JSON_KEYS('{"letter": {"beta": [{"year" : 800}, {"origin": "Greece"}]}}', 'letter', 'beta', 1) |
+------------------------------------------------------------------------------------------------+
| ["origin"] |
+------------------------------------------------------------------------------------------------+
Example 6
Returns an empty array when the JSON object is empty.
SELECT JSON_KEYS('{}');
****
+-----------------+
| JSON_KEYS('{}') |
+-----------------+
| [] |
+-----------------+