Presto array contains an element that likes some pattern

sql presto
daydayup picture daydayup · Jun 8, 2018 · Viewed 19.6k times · Source

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?

Answer

Bohemian picture Bohemian · Jun 8, 2018

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

Related questions

Presto SQL - Converting a date string to date format

I'm on presto and have a date formatted as varchar that looks like - 7/14/2015 8:22:39 AM I've looked the presto docs and tried various things(cast, date_format, using split_part to parse and then cast) and am not getting this …

Read More
Presto check if NULL and return default (NVL analog)

Is there any analog of NVL in Presto DB? I need to check if a field is NULL and return a default value. I solve this somehow like this: SELECT CASE WHEN my_field is null THEN 0 ELSE my_field …

Read More
Athena greater than condition in date column

I have the following query that I am trying to run on Athena. SELECT observation_date, COUNT(*) AS count FROM db.table_name WHERE observation_date > '2017-12-31' GROUP BY observation_date However it is producing this …

Read More