JSON_
On this page
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_a JSON object which contains the keys to be extracted.doc -
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_is empty.doc -
NULLifjson_is eitherdoc NULLor 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.
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.
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'.
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('{}') |
+-----------------+
| [] |
+-----------------+Last modified: March 7, 2023