JSON_CONTAINS

Description

This function checks whether a specified JSON document is contained in a target JSON document, or whether a specified element is found at a specified path in the target document (if the path parameter is provided). It returns 1 if the specified JSON document is contained in the target JSON document, and 0 otherwise.

If you only need to check whether data exists at a specified path, use JSON_CONTAINS_PATH() instead.

Syntax

JSON_CONTAINS(target, candidate[, path])

Description

The candidate parameter specifies the JSON document to check, and the target parameter specifies the target JSON document.

If any parameter is NULL or the path parameter is not recognized as part of the target document, the function returns NULL.

The following situations will result in an error:

• The candidate or target parameter is not a valid JSON document.

• The path parameter is not a valid path expression.

• The path parameter contains the * or ** wildcard.

This function follows these rules:

• A single element is considered equal if the JSON_TYPE() values of the two scalar values are the same and can be compared. For numeric types, the values can be directly compared.

• For arrays, an array is considered contained in another array if all elements of the candidate array are contained in the target array.

• For objects, an object is considered contained in another object if all key values of the candidate object are present in the target object, and the corresponding value for each key is the same as the corresponding key in the target object.

Examples

SET @jn = '{"a": 1, "b": 2, "c": {"d": 4}}';
Query OK, 0 rows affected (0.000 sec)

SET @jn2 = '1';
Query OK, 0 rows affected (0.000 sec)

SELECT JSON_CONTAINS(@jn, @jn2, '$.a');
+---------------------------------+
| JSON_CONTAINS(@jn, @jn2, '$.a') |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.001 sec)

SELECT JSON_CONTAINS(@jn, @jn2, '$.b');
+---------------------------------+
| JSON_CONTAINS(@jn, @jn2, '$.b') |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set (0.001 sec)

SET @jn2 = '{"d": 4}';
Query OK, 0 rows affected (0.000 sec)

SELECT JSON_CONTAINS(@jn, @jn2, '$.a');
+---------------------------------+
| JSON_CONTAINS(@jn, @jn2, '$.a') |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set (0.001 sec)

SELECT JSON_CONTAINS(@jn, @jn2, '$.c');
+---------------------------------+
| JSON_CONTAINS(@jn, @jn2, '$.c') |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.001 sec)