Wildcard search for array<string> in Athena

I have a table in Athena where one of the columns is of type array.
I tried the below query to get output containing earth but doesn’t work.
How do I perform a wildcard search in this column?

Example array column:

Expected output after wildcard search:

enter image description here

select * from mytable
where contains(myarr,'eart%');

>Solution :

This is from memory, so it might need a bit of tweaking, but you can use a filter on the array elements

where cardinality(filter(myarr, q -> q like 'eart%')) > 0

filter creates an array of matches and cardinality tests for one or more elements in the array

Leave a Reply