content_image/759566.jpg content_image/759569.jpg content_image/759573.jpg content_image/759574.jpg
• JSON_OVERLAPS() 

We can demonstrate this by creating and populating the customers  table using the following CREATE TABLE  and INSERT  statements: 

mysql> CREATE TABLE customers ( -> id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> custinfo JSON -> ); Query OK, 0 rows affected (0.51 sec) mysql> INSERT INTO customers VALUES -> (NULL, NOW(), ’{"user":"Jack","user_id":37,"zipcode":[94582,94536]}’), -> (NULL, NOW(), ’{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}’), -> (NULL, NOW(), ’{"user":"Bob","user_id":31,"zipcode":[94477,94507]}’), -> (NULL, NOW(), ’{"user":"Mary","user_id":72,"zipcode":[94536]}’), -> (NULL, NOW(), ’{"user":"Ted","user_id":56,"zipcode":[94507,94582]}’); Query OK, 5 rows affected (0.07 sec) Records: 5 Duplicates: 0 Warnings: 0 

First we execute three queries on the customers  table, one each using MEMBER OF() , JSON_CONTAINS() , and JSON_OVERLAPS() , with the result from each query shown here: 

mysql> SELECT * FROM customers -> WHERE 94507 MEMBER OF(custinfo->’$.zipcode’); 

<img src='content_image/759566.jpg'>

3 rows in set (0.00 sec) 

mysql> SELECT * FROM customers -> WHERE JSON_CONTAINS(custinfo->’$.zipcode’, CAST(’[94507,94582]’ AS JSON)); 

<img src='content_image/759569.jpg'>

2 rows in set (0.00 sec) 

mysql> SELECT * FROM customers -> WHERE JSON_OVERLAPS(custinfo->’$.zipcode’, CAST(’[94507,94582]’ AS JSON)); 

<img src='content_image/759573.jpg'>

+----+---------------------+-------------------------------------------------------------------+ 4 rows in set (0.00 sec) 

Next, we run EXPLAIN  on each of the previous three queries: 

mysql> EXPLAIN SELECT * FROM customers -> WHERE 94507 MEMBER OF(custinfo->’$.zipcode’); 

<img src='content_image/759574.jpg'>

