Presto array contains an element that likes some pattern

Question

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?

Suez

Please add sample data and expected outcome – Oviduct

Answer 1

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

Hydrophyte

You could reduce(myArray, false, (a, x -> a OR x like '%denied%')) to avoid copying an array for every row. – Tribble

Answer 2

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

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

Shoop

Answer 3

See array operator docs here

contains(array_column,'denied')

Bethanybethe

this does not answer the question. According to the doc, the CONTAINS operator only looks for exact matches. – Cigar

Answer 4

We can use strpos(returns starting position of substring and 0 if not found) here. (documentation)

where strpos(array_column,'denied')>0

Clackmannan