Skip to main content

JSON_EXTRACT

Description​

Extract the field specified by json_path from JSON type data.

Syntax​

JSON_EXTRACT (<json_object>, <path>[, <path2>, ...])

Parameters​

Required Parameters:​

  • <json_object>: The JSON type expression to extract from.
  • <path>: The JSON path to extract the target element from the target JSON.

Optional/Variable Parameters​

  • <path2> Multiple path values can be extracted from the JSON object.

Return Value​

  • Nullable(JSON): Returns the JSON element pointed to by <path>. If multiple results are matched, they will be returned as a JSON array.

Usage Notes​

  • If <json_object> is NULL, or <path> is NULL, returns NULL.
  • For single <path> parameter cases, if the <path> does not exist, returns NULL.
  • For multiple <path> parameter cases, non-existent paths are ignored, and matched elements are returned as a JSON array. If no matches are found, returns NULL.
  • If <path> is not a valid path, an error is reported.
  • If the value corresponding to <path> is a string, the returned string will be surrounded by double quotes ("). To get results without double quotes, please use the function JSON_UNQUOTE.
  • The syntax of <path> is as follows:
    • $ represents the json root
    • .k1 represents the element with key k1 in the json object
      • If the key value contains ".", <path> needs to use double quotes, for example SELECT json_extract('{"k1.a":"abc","k2":300}', '$."k1.a"');
    • [i] represents the element at index i in the json array
      • To get the last element of json_array, you can use $[last], the second to last element can use $[last-1], and so on.
    • * represents a wildcard, where $.* represents all members of the root object, and $[*] represents all elements of the array.
  • If <path> contains wildcards (*), the matching results will be returned in array form.

Examples​

  1. General parameters
SELECT JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k1');
+-----------------------------------------------+
| JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k1') |
+-----------------------------------------------+
| "v31" |
+-----------------------------------------------+

Note: The returned result is "v31" not v31.

  1. NULL parameters

    select JSON_EXTRACT(null, '$.k1');
    +----------------------------+
    | JSON_EXTRACT(null, '$.k1') |
    +----------------------------+
    | NULL |
    +----------------------------+
  2. <path> is NULL

    SELECT JSON_EXTRACT('{"k1":"v31","k2":300}', NULL);
    +---------------------------------------------+
    | JSON_EXTRACT('{"k1":"v31","k2":300}', NULL) |
    +---------------------------------------------+
    | NULL |
    +---------------------------------------------+
  3. Multi-level path

    SELECT JSON_EXTRACT('{"k1":"v31","k2":{"sub_key": 1234.56}}', '$.k2.sub_key');
    +------------------------------------------------------------------------+
    | JSON_EXTRACT('{"k1":"v31","k2":{"sub_key": 1234.56}}', '$.k2.sub_key') |
    +------------------------------------------------------------------------+
    | 1234.56 |
    +------------------------------------------------------------------------+
  4. Array path

    SELECT JSON_EXTRACT(json_array("abc", 123, cast(now() as string)), '$[2]');
    +----------------------------------------------------------------------+
    | JSON_EXTRACT(json_array("abc", 123, cast(now() as string)), '$.[2]') |
    +----------------------------------------------------------------------+
    | "2025-07-16 18:35:25" |
    +----------------------------------------------------------------------+
  5. Non-existent path

    SELECT JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k3');
    +-----------------------------------------------+
    | JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k3') |
    +-----------------------------------------------+
    | NULL |
    +-----------------------------------------------+
  6. Multiple path parameters

    select JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id', '$.not_exists');
    +--------------------------------------------------------------------------------+
    | JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id', '$.not_exists') |
    +--------------------------------------------------------------------------------+
    | ["doris",123] |
    +--------------------------------------------------------------------------------+

    Even if there is only one match, it will be returned in array form

    select JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id2', '$.not_exists');
    +---------------------------------------------------------------------------------+
    | JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.name', '$.id2', '$.not_exists') |
    +---------------------------------------------------------------------------------+
    | ["doris"] |
    +---------------------------------------------------------------------------------+

    If all paths have no matches, return NULL

    select JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.k1', '$.k2', '$.not_exists');
    +------------------------------------------------------------------------------+
    | JSON_EXTRACT('{"id": 123, "name": "doris"}', '$.k1', '$.k2', '$.not_exists') |
    +------------------------------------------------------------------------------+
    | NULL |
    +------------------------------------------------------------------------------+
  7. Wildcard path

    select json_extract('{"k": [1,2,3,4,5]}', '$.k[*]');
    +----------------------------------------------+
    | json_extract('{"k": [1,2,3,4,5]}', '$.k[*]') |
    +----------------------------------------------+
    | [1,2,3,4,5] |
    +----------------------------------------------+
    select json_extract('{"k": [1,2,3,4,5], "k2": "abc", "k3": {"k4": "v4"}}', '$.*', '$.k3.k4');
    +---------------------------------------------------------------------------------------+
    | json_extract('{"k": [1,2,3,4,5], "k2": "abc", "k3": {"k4": "v4"}}', '$.*', '$.k3.k4') |
    +---------------------------------------------------------------------------------------+
    | [[1,2,3,4,5],"abc",{"k4":"v4"},"v4"] |
    +---------------------------------------------------------------------------------------+
  8. Value is NULL case

    select JSON_EXTRACT('{"id": 123, "name": null}', '$.name') v, JSON_EXTRACT('{"id": 123, "name": null}', '$.name') is null v2;
    +------+------+
    | v | v2 |
    +------+------+
    | null | 0 |
    +------+------+