top of page
CerebroSQL

MySQL: 

JSON_CONTAINS

Syntax:
JSON_CONTAINS(target, candidate[, path])

Indicates by returning 1 or 0 whether a given candidate JSON document
is contained within a target JSON document, or---if a path argument was
supplied---whether the candidate is found at a specific path within the
target. Returns NULL if any argument is NULL, or if the path argument
does not identify a section of the target document. An error occurs if
target or candidate is not a valid JSON document, or if the path
argument is not a valid path expression or contains a * or ** wildcard.

To check only whether any data exists at the path, use
JSON_CONTAINS_PATH() instead.

The following rules define containment:

o A candidate scalar is contained in a target scalar if and only if
they are comparable and are equal. Two scalar values are comparable
if they have the same JSON_TYPE() types, with the exception that
values of types INTEGER and DECIMAL are also comparable to each
other.

o A candidate array is contained in a target array if and only if every
element in the candidate is contained in some element of the target.

o A candidate nonarray is contained in a target array if and only if
the candidate is contained in some element of the target.

o A candidate object is contained in a target object if and only if for
each key in the candidate there is a key with the same name in the
target and the value associated with the candidate key is contained
in the value associated with the target key.

Otherwise, the candidate value is not contained in the target document.

Starting with MySQL 8.0.17, queries using JSON_CONTAINS() on InnoDB
tables can be optimized using multi-valued indexes; see
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-
multi-valued, for more information.

URL: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

Example

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SET @j2 = '1';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
| 1 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.b') |
+-------------------------------+
| 0 |
+-------------------------------+

mysql> SET @j2 = '{"d": 4}';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
| 0 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.c') |
+-------------------------------+
| 1 |
+-------------------------------+

bottom of page