Can I use json array field with IN clause

Question

I have this query running:

select * 
from housedata 
where JSON_EXTRACT(properties->"$.Type", '$[0]') in ('House', 'Flat');

However, in some rows, there are multiple items in the Type json array and I don't want to use only the first item like the example above.

I want to match the query assuming it is ["House", "Flat"], but also when it is ["House", "Tent"] as one of the items in the "In" list. < /p>

Can this be implemented in a query? I've tried to find it, but I've been looking for examples of finding something in the JSON array itself, but that's not what I'm looking for.

Answer 1

Yes, this works (tested in MySQL 8.0.32):

select json_extract(properties->'$.Type', '$[0]') IN ('House', 'Flat') as result 
from housedata;
+--------+
| result |
+--------+
|      1 |
+--------+

If you want to test for any value in the JSON array that matches one of the value lists, use JSON_OVERLAPS():

select true from housedata
where json_overlaps(properties->'$.Type',
  cast('["House","Flat"]' as json));
+------+
| true |
+------+
|    1 |
+------+

Of course, this would be much simpler if instead of using JSON for the multi-valued attributes, you use a dependent table and store one value per row.

select ...
from housedata join housedata_type using (house_id)
where housedata_type.type in ('House', 'Flat');