Purpose
This function checks whether the specified JSON document is contained within the target JSON document, or whether the specified element is found at the specified path within the target document (if the path parameter is provided). If the specified JSON document is contained within the target JSON document, the function returns 1; otherwise, it returns 0.
If you only need to check whether data exists in a specified path, use JSON_CONTAINS_PATH() instead.
Syntax
JSON_CONTAINS(target, candidate[, path])
Description
The candidate parameter specifies the source JSON document, and the target parameter specifies the target JSON document.
If any parameter is NULL, or if the path parameter is not identified as part of the target document, NULL is returned.
An error occurs in any of the following scenarios:
The
candidateortargetparameter is not a valid JSON document.The
pathparameter is not a valid path expression.The
pathparameter contains the * or ** wildcard character.
This function follows these rules:
For two single elements to be equal, their JSON_TYPE() must be the same and they must be comparable. For numeric types, this means they can be directly compared.
For arrays, the
candidatearray is contained in thetargetarray if and only if all elements ofcandidateare present intarget.For objects, the
candidateobject is contained in thetargetobject if and only if all keys ofcandidateare present intarget, and the value corresponding to each key incandidateis equal to the value corresponding to that key intarget.
Examples
obclient> SET @jn = '{"a": 1, "b": 2, "c": {"d": 4}}';
Query OK, 0 rows affected
obclient> SET @jn2 = '1';
Query OK, 0 rows affected
obclient> SELECT JSON_CONTAINS(@jn, @jn2, '$.a');
+---------------------------------+
| JSON_CONTAINS(@jn, @jn2, '$.a') |
+---------------------------------+
| 1 |
+---------------------------------+
1 row in set
obclient> SELECT JSON_CONTAINS(@jn, @jn2, '$.b');
+---------------------------------+
| JSON_CONTAINS(@jn, @jn2, '$.b') |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set
obclient> SET @jn2 = '{"d": 4}';
Query OK, 0 rows affected
obclient> SELECT JSON_CONTAINS(@jn, @jn2, '$.a');
+---------------------------------+
| JSON_CONTAINS(@jn, @jn2, '$.a') |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set
obclient> SELECT JSON_CONTAINS(@jn, @jn2, '$.c');
+---------------------------------+
| JSON_CONTAINS(@jn, @jn2, '$.c') |
+---------------------------------+
| 1 |
+---------------------------------+
1 row in set