How to parse data that corresponds to a json_path in a JSON expression - MaxCompute

Extracts a string or JSON data from a JSON-formatted string or a value of the JSON data type based on the specified json_path.

Syntax

The JSON_EXTRACT function has the following two signatures.

Signature 1: The input parameter is of the JSON data type, and the return value is of the JSON type.
```
JSON JSON_EXTRACT(JSON <json_expr>, STRING <json_path>)
```
Signature 2: When BigQuery-compatible mode is enabled by running the SET odps.sql.bigquery.compatible=true; command, the function accepts a JSON-formatted string as input and returns a value of the STRING type.
Note
In this case, the function behaves the same as GET_JSON_OBJECT when the input parameter is of the STRING type.
```
STRING JSON_EXTRACT(STRING <json_string_expr>, STRING <json_path>)
```

Parameters

Parameter	Description
json_expr	Required. A value of the JSON type. This parameter specifies the JSON data to be processed.
json_string_expr	Required. A value of the STRING type. This parameter specifies a standard string in the JSON format.
json_path	Required. A value of the STRING type. This parameter specifies the JSON path of the value to be returned.

Return value

Returns a value of the JSON or STRING type. The following rules apply:

Input parameter type

Return value

The input parameter is of the JSON type

Returns a value of the JSON type.

If the json_path is invalid, the function returns NULL.
If the json_path is malformed, the function returns an error.

The input parameter is a JSON-formatted string

Returns a value of the STRING type.

If the json_path is invalid or malformed, the function returns NULL.

Note

In this case, the function behaves the same as GET_JSON_OBJECT when the input parameter is of the STRING type.

Examples

The input parameter is of the JSON type

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
```

The input parameter is of the STRING type

To use a STRING type input parameter, you must enable BigQuery-compatible mode by running the SET odps.sql.bigquery.compatible=true; command.

Example 1: In BigQuery-compatible mode, use a JSON-formatted string as input. The function returns a value of the STRING type.

SET odps.sql.bigquery.compatible=true;
SELECT JSON_EXTRACT(
  '{"class": {"students": [{"name": "Jane"}]}}',
  '$') AS json_text_string;

The following result is returned:

+------------------------------------------+
|          json_text_string                |
+------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}} |
+------------------------------------------+

Example 2: In BigQuery-compatible mode, if the input is a JSON-formatted string and the json_path is invalid or malformed, the function returns NULL.
```
SET odps.sql.bigquery.compatible=true;
SELECT JSON_EXTRACT('{"a": null}', "$.b");
```
The following result is returned:
```
+------------+
| _c0        | 
+------------+
| NULL       | 
+------------+
```

Related functions

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