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 functionJSON_UNQUOTE
. - The syntax of
<path>
is as follows:$
represents the json root.k1
represents the element with keyk1
in the json object- If the key value contains ".",
<path>
needs to use double quotes, for exampleSELECT json_extract('{"k1.a":"abc","k2":300}', '$."k1.a"')
;
- If the key value contains ".",
[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.
- To get the last element of json_array, you can use
*
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β
- General parameters
SELECT JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k1');
+-----------------------------------------------+
| JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k1') |
+-----------------------------------------------+
| "v31" |
+-----------------------------------------------+
Note: The returned result is
"v31"
notv31
.
-
NULL parameters
select JSON_EXTRACT(null, '$.k1');
+----------------------------+
| JSON_EXTRACT(null, '$.k1') |
+----------------------------+
| NULL |
+----------------------------+ -
<path>
is NULLSELECT JSON_EXTRACT('{"k1":"v31","k2":300}', NULL);
+---------------------------------------------+
| JSON_EXTRACT('{"k1":"v31","k2":300}', NULL) |
+---------------------------------------------+
| NULL |
+---------------------------------------------+ -
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 |
+------------------------------------------------------------------------+ -
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" |
+----------------------------------------------------------------------+ -
Non-existent path
SELECT JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k3');
+-----------------------------------------------+
| JSON_EXTRACT('{"k1":"v31","k2":300}', '$.k3') |
+-----------------------------------------------+
| NULL |
+-----------------------------------------------+ -
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 |
+------------------------------------------------------------------------------+ -
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"] |
+---------------------------------------------------------------------------------------+ -
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 |
+------+------+