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