Skip to main content

JSON_KEYS

Description

Returns all keys of a JSON object in array form. By default, it returns the keys of the root object, but you can also control which specific path's object keys to return through parameters.

Syntax

JSON_KEYS(<json_object>[, <path>])

Parameters

Required Parameters

  • <json_object> JSON type, the JSON object from which keys need to be extracted.

Optional Parameters

  • <path> String type, optional JSON path that specifies the JSON subdocument to check. If not provided, defaults to the root document.

Return Value

  • Array Returns an array of strings, where the array members are all the keys of the JSON object.

Notes

  • Returns NULL when <json_object> or <path> is NULL.
  • Returns NULL if it's not a JSON object (e.g., if it's a JSON array).
  • Returns NULL if the object pointed to by <path> does not exist.

Examples

  1. Example 1

    SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
    +---------------------------------------+
    | JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
    +---------------------------------------+
    | ["a", "b"] |
    +---------------------------------------+
    SELECT JSON_KEYS('{}');
    +-----------------+
    | JSON_KEYS('{}') |
    +-----------------+
    | [] |
    +-----------------+
  2. Specify path

    SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
    +----------------------------------------------+
    | JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
    +----------------------------------------------+
    | ["c"] |
    +----------------------------------------------+
  3. NULL parameters

    SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', NULL);
    +---------------------------------------------+
    | JSON_KEYS('{"a": 1, "b": {"c": 30}}', NULL) |
    +---------------------------------------------+
    | NULL |
    +---------------------------------------------+
    SELECT JSON_KEYS(NULL);
    +-----------------+
    | JSON_KEYS(NULL) |
    +-----------------+
    | NULL |
    +-----------------+
  4. Not a JSON object

    SELECT JSON_KEYS('[1,2]');
    +--------------------+
    | JSON_KEYS('[1,2]') |
    +--------------------+
    | NULL |
    +--------------------+
    SELECT JSON_KEYS('{"k": [1, 2, 3]}', '$.k');
    +--------------------------------------+
    | JSON_KEYS('{"k": [1, 2, 3]}', '$.k') |
    +--------------------------------------+
    | NULL |
    +--------------------------------------+
  5. Object specified by path does not exist

    SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.c');
    +----------------------------------------------+
    | JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.c') |
    +----------------------------------------------+
    | NULL |
    +----------------------------------------------+