How to do MySQL "IN" condition query with JSON_EXTRACT?

Question

I have a table similar to the following:

CREATE TABLE `foo` (
  `foo_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `data` json DEFAULT NULL,
  `data_type` int(11) NOT NULL,
  `data_status` int(11) NOT NULL,
  PRIMARY KEY (`foo_id`)
);

insert some data

insert into foo(data, data_type, data_status) values ('{\"id\": \"FOO\", \"code\": \"FOO\"}', 1, 1);
insert into foo(data, data_type, data_status) values ('{\"id\": \"BAR\", \"code\": \"BAR\"}', 1, 1);
insert into foo(data, data_type, data_status) values ('{\"id\": \"BAZ\", \"code\": \"BAZ\"}', 1, 1);

show table contents:

mysql> select * from foo;
+--------+------------------------------+-----------+-------------+
| foo_id | data                         | data_type | data_status |
+--------+------------------------------+-----------+-------------+
|      1 | {"id": "FOO", "code": "FOO"} |         1 |           1 |
|      2 | {"id": "BAR", "code": "BAR"} |         1 |           1 |
|      3 | {"id": "BAZ", "code": "BAZ"} |         1 |           1 |
+--------+------------------------------+-----------+-------------+
3 rows in set (0.01 sec)

This query works:

select f.data, f.data_type, f.data_status
from foo f
where (JSON_EXTRACT(f.data, '$.code') in ('FOO'))
   and (f.data_type in (1)) 
   and (f.data_status in (1));

+------------------------------+-----------+-------------+
| data                         | data_type | data_status |
+------------------------------+-----------+-------------+
| {"id": "FOO", "code": "FOO"} |         1 |           1 |
+------------------------------+-----------+-------------+
1 row in set (0.00 sec)

This one too:

select f.data, f.data_type, f.data_status
from foo f
where (JSON_EXTRACT(f.data, '$.code') in ('BAR'))
   and (f.data_type in (1)) 
   and (f.data_status in (1));

+------------------------------+-----------+-------------+
| data                         | data_type | data_status |
+------------------------------+-----------+-------------+
| {"id": "BAR", "code": "BAR"} |         1 |           1 |
+------------------------------+-----------+-------------+

This one does not however...

select f.data, f.data_type, f.data_status
from foo f
where (JSON_EXTRACT(f.data, '$.code') in ('FOO','BAR'))
   and (f.data_type in (1)) 
   and (f.data_status in (1));

Empty set, 1 warning (0.00 sec)

How can I make the third query work?

Answer 1

I noticed there was a warning on the last query

Empty set, 1 warning (0.00 sec)

...

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                           |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 1235 | This version of MySQL doesn't yet support 'comparison of JSON in the IN operator' |
+---------+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

However, that warning led me to this post

https://bugs.mysql.com/bug.php?id=93800

So, by modifying the query to surround the IN arguments with quotes, it works

select f.data, f.data_type, f.data_status 
from foo f 
where (JSON_EXTRACT(f.data, '$.code') in ('"FOO"','"BAR"'))    
and (f.data_type in (1))     
and (f.data_status in (1));
+------------------------------+-----------+-------------+
| data                         | data_type | data_status |
+------------------------------+-----------+-------------+
| {"id": "FOO", "code": "FOO"} |         1 |           1 |
| {"id": "BAR", "code": "BAR"} |         1 |           1 |
+------------------------------+-----------+-------------+
2 rows in set, 1 warning (0.00 sec)

still got the same warning though...

The solution only works for more than one IN parameter, it breaks if you try it on the first two queries that initially succeeded

mysql> select f.data, f.data_type, f.data_status 
from foo f 
where (JSON_EXTRACT(f.data, '$.code') in ('"BAR"'))    
and (f.data_type in (1))     
and (f.data_status in (1));
Empty set (0.00 sec)