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?

Please add sample data and expected outcome – mtr.web Jun 08 '18 at 20:04 — mtr.web, Jun 08 '18 at 20:04

score 32 · Answer 1 · answered Jun 08 '18 at 20:48

32

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

answered Jun 08 '18 at 20:48

Bohemian

412,405
93
575
722

8

You could `reduce(myArray, false, (a, x -> a OR x like '%denied%'))` to avoid copying an array for every row. – Piotr Findeisen Jun 09 '18 at 09:03

score 14 · Answer 2 · edited Sep 16 '21 at 08:10

14

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

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

edited Sep 16 '21 at 08:10

user5305519

3,008
4
26
44

answered Apr 30 '21 at 16:47

Martin Traverso

4,731
15
24

score 2 · Answer 3 · answered Jan 20 '20 at 20:57

2

See array operator docs here

contains(array_column,'denied')

answered Jan 20 '20 at 20:57

twoy

53
7

2

this does not answer the question. According to the doc, the `CONTAINS` operator only looks for exact matches. – bmarcov Apr 29 '20 at 10:58

AGVG · Answer 4 · 2022-08-29T09:00:59.033

0

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

where strpos(array_column,'denied')>0

edited Aug 29 '22 at 09:00

answered Aug 29 '22 at 07:46

AGVG

36
4

Presto array contains an element that likes some pattern

4 Answers4

Linked