SingleStore Managed Service

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 if json_doc is either NULL or not a valid JSON object, or if the keypath does not locate a JSON object.

Examples
Example: Return the top-level keys from a JSON object
SELECT JSON_KEYS('{"Name": "John", "Car": {"Benz": 2}}');
****
+---------------------------------------------------+
| JSON_KEYS('{"Name": "John", "Car": {"Benz": 2}}') |
+---------------------------------------------------+
| ["Car","Name"]                                    |
+---------------------------------------------------+
Example: Return the top-level keys from a JSON sub object "Cars", which is specified in the keypath
SELECT JSON_KEYS('{"Name": "John", "Cars": {"Benz": 2 , "Mercedes" : 4}}', 'Cars');
****
+-----------------------------------------------------------------------------+
| JSON_KEYS('{"Name": "John", "Cars": {"Benz": 2 , "Mercedes" : 4}}', 'Cars') |
+-----------------------------------------------------------------------------+
| ["Benz","Mercedes"]                                                         |
+-----------------------------------------------------------------------------+
Example: Return NULL since there is a single value instead of a JSON object at the keypath 'Cars'.'Benz':
SELECT JSON_KEYS('{"Name": "John", "Cars": {"Benz": 2}}', 'Cars','Benz');
****
+-------------------------------------------------------------------+
| JSON_KEYS('{"Name": "John", "Cars": {"Benz": 2}}', 'Cars','Benz') |
+-------------------------------------------------------------------+
| NULL                                                              |
+-------------------------------------------------------------------+
Example: Return the top-level key of the JSON object at the keypath 'Cars'.'Benz':
SELECT JSON_KEYS('{"Name": "John", "Cars": {"Benz": {"Model" : 201}}}', 'Cars','Benz');
****
+---------------------------------------------------------------------------------+
| JSON_KEYS('{"Name": "John", "Cars": {"Benz": {"Model" : 201}}}', 'Cars','Benz') |
+---------------------------------------------------------------------------------+
| ["Model"]                                                                       |
+---------------------------------------------------------------------------------+
Example: Return the top-level key of the second element of the JSON array 'Name'. 'John':
SELECT JSON_KEYS('{"Name": {"John": [{"Age" : 30}, {"Address": "Berlin"}]}}', 'Name', 'John', 1);
****
+-------------------------------------------------------------------------------------------+
| JSON_KEYS('{"Name": {"John": [{"Age" : 30}, {"Address": "Berlin"}]}}', 'Name', 'John', 1) |
+-------------------------------------------------------------------------------------------+
| ["Address"]                                                                               |
+-------------------------------------------------------------------------------------------+

Note that keypath array positions are zero-indexed; 1 is used in the last argument to indicate that the second element be returned.

Example: Return an empty array when the JSON object is empty
SELECT JSON_KEYS('{}');
****
+-----------------+
| JSON_KEYS('{}') |
+-----------------+
| []              |
+-----------------+