JSON_CONTAINS(target, candiate[, path])
This function indicates with a 1 or 0 if a candidate document is contained in the target document. The optional path argument lets you seek information in embedded documents. And please note the 'haystack' is before the 'needle' for this function.
mysql> SELECT JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Moe": 1}');
+------------------------------------------------------+
| JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Moe": 1}') |
+------------------------------------------------------+
| 1 |
+------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Shemp": 1}');
+--------------------------------------------------------+
| JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Shemp": 1}') |
+--------------------------------------------------------+
| 0 |
+--------------------------------------------------------+
1 row in set (0.00 sec)
Objects as must match both key and value. Be careful as an array is considered to be contained in a target array only if every element in the candidate is contained in some element of the target. So JSON_CONTAINS("[1,2,3]","[2,3]") will return a '1' while JSON_CONTAINS("[1,2,3]","[3,4]") will return a '0'.
You can always use JSON_CONTAINS_PATH() to test if any matches exist on the entire path and JSON_CONTAINS() for a simple match.
JSON_OVERLAPS(document1, document2)
This functions compares two JSON documents and returns 1 if it has any key/value pairs or array elements in common.
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[2,3,4,5]");
+----------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[2,3,4,5]") |
+----------------------------------------+
| 1 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[2,4,6]");
+--------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[2,4,6]") |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (0.00 sec)
So what is the difference between these two new functions? JSON_CONTAINS() requires ALL elements of the array searched for to be present while JSON_OVERLAPS() looks for any matches. So think JSON_CONTAINS() as the AND operation on KEYS while JSON_OVERLAP is the OR operator.
mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[1,3,5,9]");
+----------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[1,3,5,9]") |
+----------------------------------------+
| 1 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS("[1,3,5,7]","[1,3,5,9]");
+----------------------------------------+
| JSON_CONTAINS("[1,3,5,7]","[1,3,5,9]") |
+----------------------------------------+
| 0 |
+----------------------------------------+
1 row in set (0.00 sec)
value MEMBER OF(json_array)
This function returns a 1 if the value is an element of the json_array.
mysql> SELECT 3 MEMBER OF('[1, 3, 5, 7, "Moe"]');
+------------------------------------+
| 3 MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+------------------------------------+
| 1 |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT 2 MEMBER OF('[1, 3, 5, 7, "Moe"]');
+------------------------------------+
| 2 MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+------------------------------------+
| 0 |
+------------------------------------+
1 row in set (0.00 sec)
This function does not convert to and from strings for you so do not try something like this.
mysql> SELECT "3" MEMBER OF('[1, 3, 5, 7, "Moe"]');
+--------------------------------------+
| "3" MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+--------------------------------------+
| 0 |
+--------------------------------------+
So "3" is not equal to 3. And you may have to explicitly cast the value as an array or use JSON_ARRAY().
mysql> SELECT CAST('[3,4]' AS JSON) MEMBER OF ('[[1,2],[3,4]]');
+---------------------------------------------------+
| CAST('[3,4]' AS JSON) MEMBER OF ('[[1,2],[3,4]]') |
+---------------------------------------------------+
| 1 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_ARRAY(3,4) MEMBER OF ('[[1,2],[3,4]]');
+---------------------------------------------+
| JSON_ARRAY(3,4) MEMBER OF ('[[1,2],[3,4]]') |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)
