Wildcard search for array<string> in Athena

user_13465436 0
Viewed 282

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%');
presto amazon-athena aws-glue hive sql
1 Answers

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

user_913360
Related