JSON_KEYS

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Syntax

SQL

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.

SQL

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.

SQL

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':

SQL

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':

SQL

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.

SQL

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.

SQL

SELECT JSON_KEYS('{}');

+-----------------+
| JSON_KEYS('{}') |
+-----------------+
| []              |
+-----------------+