MaxCompute:JSON_EXTRACT

Syntax

JSON JSON_EXTRACT(JSON <json_expr>, STRING <json_path>)

Standard example:

-- Returns 1.
SELECT JSON_EXTRACT(JSON '{"a":1, "b":2}', '$.a');

Parameters

Return value

Returns a JSON value.

• If json_path points to a key that does not exist in the JSON object, the function returns NULL.

• If json_path is malformed (invalid format), the function returns an error.

Examples

Extract the value of key `a` from a JSON object:

SELECT JSON_EXTRACT(JSON '{"a":1, "b":2}', '$.a');

Result:

+-----+
| _c0 |
+-----+
| 1   |
+-----+

Key does not exist — returns NULL:

SELECT JSON_EXTRACT(JSON '{"a":1, "b":2}', '$.c');

Result:

+------+
| _c0  |
+------+
| NULL |
+------+

Malformed path — returns an error:

SELECT JSON_EXTRACT(JSON '{"a":1, "b":2}', '$a');

Result:

Invalid argument - Param json path $invalid_json_path is invalid

Related functions

JSON_EXTRACT is a JSON function. For more information about other JSON functions, see JSON functions.