Presto - How to Transform the Second Appearance in an Array

Viewed 16

Input: Array['a', 'b', 'a']

Expected output: Array['a', 'b', 'a*2']

I've tried this following code, but I couldn't figure out how to represent the current x's index.

select transform(array['a', 'b', 'a'], x -> cast(ARRAY_POSITION(array['a', 'b', 'a'], x) as varchar))

UPD A little bit complicated, but it did the work.

with cte as (
    select ctr, ZIP_WITH(ctr, SEQUENCE(1, CARDINALITY(ctr), 1), (x, y) -> x || ':' || cast(y as varchar)) as col
    from (values array['a', 'b', 'a', 'b', 'c']) as t(ctr)
)
select 
    array_duplicates(ctr),
    col,
    SUBSTR('a:3', strpos('a:3', ':') + 1, 1),
    transform(col, x -> case when contains(array_duplicates(ctr), SUBSTR(x, 1, strpos(x, ':') - 1)) and SUBSTR(x, strpos(x, ':') + 1, 2) = cast(ARRAY_POSITION(ctr, SUBSTR(x, 1, strpos(x, ':') - 1), 2) as varchar) 
        then SUBSTR(x, 1, strpos(x, ':') - 1) || '*2' else SUBSTR(x, 1, strpos(x, ':') - 1) end)
from cte

0 Answers
Related