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.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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');