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_doc
a 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_doc
is empty.NULL
ifjson_doc
is eitherNULL
or 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('{}') | +-----------------+ | [] | +-----------------+