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