How to apply operations on each array item in a column in Presto?

Question

We want to check if the array items in text_array column start with "a" in the input table, and store the results into the third column, so we get the following output table.

My first question is: Is there any way to get output table from input table using presto?

In python we can define a function for it, like:

def myFunc(text):
    out = []
    for word in text:
        out.append(word.startswith("a"))
    return out

My second question is: Is there any way to use the python function with presto? I was looking for something like this:

SELECT
  id,
  text_array,
  myFunc(text_array) AS starts_with_a
FROM t1

Answer 1

You can use transform from array functions:

- sample data
with dataset(id, text_array) AS (
 values (1, array['ax', 'by']),
    (2, array['ax', 'ay', 'cz'])
)

-- query
select *,
    transform(text_array, el -> el like 'a%') starts_with_a
from dataset;

Output:

id	text_array	starts_with_a
1	[ax, by]	[true, false]
2	[ax, ay, cz]	[true, true, false]