Resolved: Wildcard search for array in Athena

Question:

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?

Expected output after wildcard search:

Answer:

This is from memory, so it might need a bit of tweaking, but you can use a filter on the array elements
filter creates an array of matches and cardinality tests for one or more elements in the array

If you have better answer, please add a comment about this, thank you!