JSON_EXTRACT_STRING
Descriptionβ
JSON_EXTRACT_STRING
extracts the field specified by <json_path>
from a JSON object and converts it to STRING
type.
Syntaxβ
JSON_EXTRACT_STRING(<json_object>, <json_path>)
Parametersβ
<json_object>
: JSON type, the target parameter to extract from.<json_path>
: String type, the JSON path to extract the target element from the target JSON.
Return Valueβ
Nullable(STRING)
Returns the extracted STRING value, returns NULL in some cases
Usage Notesβ
- If
<json_object>
or<json_path>
is NULL, returns NULL. - If the element specified by
<json_path>
does not exist, returns NULL. - If the element specified by
<json_path>
cannot be converted to STRING, returns NULL. - Its behavior is consistent with "cast + json_extract", which is equivalent to:
So even if the object pointed to by
CAST(JSON_EXTRACT(<json_object>, <json_path>) as STRING)
<json_path>
is not of STRING type, as long as it supports conversion to STRING type, you can get the converted value. - The STRING returned here does not contain double quotes (").
- For null values in JSON objects, the result is not NULL but the string "null".
If you want to check whether an element is null, please use the function
JSON_EXTRACT_ISNULL
γ
Examplesβ
- Normal parameters
SELECT json_extract_string('{"id": 123, "name": "doris"}', '$.name');
+---------------------------------------------------------------+
| json_extract_string('{"id": 123, "name": "doris"}', '$.name') |
+---------------------------------------------------------------+
| doris |
+---------------------------------------------------------------+ - Case where path does not exist
SELECT json_extract_string('{"id": 123, "name": "doris"}', '$.name2');
+----------------------------------------------------------------+
| json_extract_string('{"id": 123, "name": "doris"}', '$.name2') |
+----------------------------------------------------------------+
| NULL |
+----------------------------------------------------------------+ - NULL parameters
SELECT json_extract_string('{"id": 123, "name": "doris"}', NULl);
+-----------------------------------------------------------+
| json_extract_string('{"id": 123, "name": "doris"}', NULl) |
+-----------------------------------------------------------+
| NULL |
+-----------------------------------------------------------+SELECT json_extract_string(NULL, '$.id2');
+------------------------------------+
| json_extract_string(NULL, '$.id2') |
+------------------------------------+
| NULL |
+------------------------------------+ - Case where other types are converted to STRING
SELECT json_extract_string('{"id": 123, "name": "doris"}','$.id');
+------------------------------------------------------------+
| json_extract_string('{"id": 123, "name": "doris"}','$.id') |
+------------------------------------------------------------+
| 123 |
+------------------------------------------------------------+ - Null values will be converted to string "null" instead of NULL
SELECT json_extract_string('{"id": null, "name": "doris"}','$.id');
+-------------------------------------------------------------+
| json_extract_string('{"id": null, "name": "doris"}','$.id') |
+-------------------------------------------------------------+
| null |
+-------------------------------------------------------------+