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

Time:10-29

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.

enter image description here

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

CodePudding user response:

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]
  • Related