MaxCompute:JSON_CONTAINS

Syntax

BOOLEAN JSON_CONTAINS(JSON <json>, JSON <candidate> [, STRING <json_path>])

-- Standard example.
-- Returns true.
SELECT JSON_CONTAINS(JSON '[1,2,3,4,5,6,7,8]', JSON '4');

Parameters

• json: Required. The JSON data to check. The data type is JSON.

• candidate: Required. The JSON element to check for. The data type is JSON.

• json_path: Optional. The JSON path to check. The data type is STRING. If you do not specify this parameter, the function checks the entire JSON object by default.

Return value

The function returns a BOOLEAN value based on the following rules:

• If the specified element exists in the json data, the function returns true. Otherwise, it returns false.

• If json_path does not exist or is invalid, the function returns false.

• If json or candidate is NULL, the function returns NULL.

Examples

-- Returns true.
SELECT JSON_CONTAINS(JSON '[1,2,3,4,5,6,7,8]', JSON '4');

-- Returns true.
SELECT JSON_CONTAINS(JSON '{"a": 1, "b": 2, "c": {"d": 4}}', JSON '1', '$.a');

-- Returns false.
SELECT JSON_CONTAINS(JSON '{"a": 1, "b": 2, "c": {"d": 4}}', JSON '2', '$.a');

-- Returns false. The json_path does not exist.
SELECT JSON_CONTAINS(JSON '{"a": 1}',JSON '2', '$.b');

-- Returns false. The json_path is invalid.
SELECT JSON_CONTAINS(JSON '{"a": 1}',JSON '2', 'b');

-- Returns NULL.
SELECT JSON_CONTAINS(JSON '{"a": 1}',NULL);

-- Returns NULL.
SELECT JSON_CONTAINS(NULL,JSON '1'); 

Related functions

`JSON_CONTAINS` is a JSON function. For more information, see JSON functions.