Menu
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?
804
To access the elements of an array at a given position (known as the index position), use the element_at() function and specify the array name and the index position: If the index is greater than 0, element_at() returns the element that you specify, counting from the beginning to the end of the array.
To query if the array field contains at least one element with the specified value, use the filter { <field>: <value> } where <value> is the element value. To specify conditions on the elements in the array field, use query operators in the query filter document: { <array field>: { <operator1>: <value1>, ... } }
To build an array literal in Athena, use the ARRAY keyword, followed by brackets [ ] , and include the array elements separated by commas.
select *
from mytable
where contains(myarr,'foobar')
limit 10
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
