JSON_CONTAINS checks whether a JSON value contains a specified JSON element, optionally at a specific path.
BOOLEAN JSON_CONTAINS(JSON <json>, JSON <candidate> [, STRING <json_path>])Example:
-- Returns true. The scalar 4 is contained in the array.
SELECT JSON_CONTAINS(JSON '[1,2,3,4,5,6,7,8]', JSON '4');Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
json | JSON | Yes | The JSON value to check. |
candidate | JSON | Yes | The JSON element to search for. |
json_path | STRING | No | The JSON path to check within json. If omitted, the function checks the entire JSON value. |
Return value
Returns BOOLEAN.
| Condition | Return value |
|---|---|
The candidate element exists in json (or at json_path) | true |
| The candidate element does not exist | false |
json_path does not exist or is invalid | false |
json or candidate is NULL | NULL |
Examples
-- Returns true. The scalar 4 is contained in the array.
SELECT JSON_CONTAINS(JSON '[1,2,3,4,5,6,7,8]', JSON '4');
-- Returns true. The value at $.a is 1, which matches the candidate.
SELECT JSON_CONTAINS(JSON '{"a": 1, "b": 2, "c": {"d": 4}}', JSON '1', '$.a');
-- Returns false. The value at $.a is 1, not 2.
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. The candidate is SQL NULL.
SELECT JSON_CONTAINS(JSON '{"a": 1}', NULL);
-- Returns NULL. The json input is SQL NULL.
SELECT JSON_CONTAINS(NULL, JSON '1');Related functions
For all JSON functions in MaxCompute, see JSON functions.