Presto array contains an element that likes some pattern

For example, one column in my table is an array, I want to check if that column contains an element that contains substring "denied" (so elements like "denied at 12:00 pm", "denied by admin" will all count, I believe I will have to use "like" to identify the pattern). How to write sql for this?

3 Answers

Use presto's array functions:

• filter(), which returns elements that satisfy the given condition
• cardinality(), which returns the size of an array:

Like this:

where cardinality(filter(myArray, x -> x like '%denied%')) > 0 

In newer versions of PrestoSQL (now known as Trino), you can use the any_match function:

WHERE any_match(column, e -> e like '%denied%') 

See array operator docs here

contains(array_column,'denied')