Use JSON_EXISTS to check if a JSON value exists at a specific path - MaxCompute

Checks whether a value exists at the specified JSON path.

Syntax

BOOLEAN JSON_EXISTS(<json>, <json_path>)

Parameters

Parameter	Required	Description
`json`	Yes	The JSON expression to evaluate.
`json_path`	Yes	The JSON path to check for an existing value.

Return value

Type: BOOLEAN

true — the specified JSON path exists.
false — the specified JSON path does not exist.

Usage notes

Condition	Behavior
`json_path` is invalid	An error is reported.
Strict mode: path structure is inconsistent with the actual JSON data structure	No error is reported. Returns `false`.

Examples

Example 1: Check whether the key a exists in a JSON object.

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

Result:

+------+
| _c0  |
+------+
| true |
+------+

Example 2: Check whether the key c exists in a JSON object.

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

Result:

+-------+
| _c0   |
+-------+
| false |
+-------+

Example 3: Check whether a value exists at a specific array index.

SELECT JSON_EXISTS(JSON '[1,2, {"a":34}]', '$[2].a');

Result:

+------+
| _c0  |
+------+
| true |
+------+

Related functions

JSON_EXISTS is a complex type function. For more information about functions used to process complex data types such as ARRAY, MAP, STRUCT, and JSON, see Complex type functions.