Transforming Arrays

Transforming arrays

Explore this snippet with some demo data here.

Description

To transform elements of an array (often known as mapping over an array), it is possible to gain access to array elements using the FLATTEN function

with data as (select array_construct(1,2,3) nums, array_construct('a', 'b', 'c') strs)

select
   array_agg(x.value * 2 + 1) nums_transformed, -- Transformation function
   array_agg(y.value ||'_2') strs_transformed -- Another transformation function
from DATA
cross join table(flatten(input => nums)) as x
left join table(flatten(input => strs)) as y on x.index = y.index
NUMS_TRANSFORMED STRS_TRANSFORMED
[3,5,7] [“a_2”,“b_2”,“c_2” ]