how to parse data from a json expression based on a json_path - MaxCompute

Extracts data from a JSON value based on the specified json_path.

Syntax

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

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

json_expr: Required. The JSON value to process.

json_path: Required. A STRING value that specifies the path to the value that you want to return.

Returns a JSON value. The following rules apply:

Example 1: Extract the value of the key `a` from a JSON object.
```
SELECT JSON_EXTRACT(JSON '{"a":1, "b":2}', '$.a');
```
The following result is returned:
```
+-----+
| _c0 |
+-----+
| 1   |
+-----+
```
Example 2: If the specified JSON path is invalid or the destination key does not exist, the function returns NULL.
```
SELECT JSON_EXTRACT(JSON '{"a":1, "b":2}', '$.c');
```
The following result is returned:
```
+-----+
| _c0 |
+-----+
| NULL |
+-----+
```
Example 3: If the format of the JSON path is malformed, the function returns an error message.
```
SELECT JSON_EXTRACT(JSON '{"a":1, "b":2}', '$a');
```
The following result is returned:
```
Invalid argument - Param json path $invalid_json_path is invalid
```

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