Sign inTry Free

JSON_KEYS

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_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 if json_doc is either NULL 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('{}') |
+-----------------+
| []              |
+-----------------+

Last modified: March 7, 2023

Was this article helpful?

On this page

Was this article helpful?