JSON_CONTAINS

Purpose

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 document, and 0 otherwise.

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

Syntax

JSON_CONTAINS(target, candidate[, path])

Purpose

The candidate parameter specifies the JSON document, 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() of the two scalar values is the same and they can be compared. For numeric types, direct comparison is possible.

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

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

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