Checking array in Athena

Question

I have a table in Athena where one of the columns is of type array<string>. However, when I run

select * from mytable
where array_contains(myarr,'foobar')
limit 10

it seems Athena doesn't have the array_contains function:

SYNTAX_ERROR: line 2:7: Function array_contains not registered

Is there an alternative way to check if the array contains a particular string?

Thanks @DuduMarkovitz ! I was looking into Hive and this seemed to be the suggested function to use for checking an array. I didn't see or try `contains`, but that works. — dunstantom, Apr 14 '17 at 17:48
Please bear with me. I'm trying to figure out something. Why did you think that Hive is relevant here? — David דודו Markovitz, Apr 14 '17 at 17:52
In retrospect, I realized that Athena just uses the Hive metastore, but before I was thinking Hive functions were available also. Now I realize I should reference Presto's docs. — dunstantom, Apr 14 '17 at 17:59

score 34 · Accepted Answer · answered Apr 13 '17 at 16:47

34

select * 
from   mytable
where  contains(myarr,'foobar')
limit  10

answered Apr 13 '17 at 16:47

1 Answers1