Skip to main content
Returns an array by applying <function> on each element of <array>. The Lambda function <function> is a mandatory parameter. Alias: TRANSFORM

​
Syntax

ARRAY_TRANSFORM(<function>, <array>)

​
Parameters

ParameterDescriptionSupported input type
<function>A Lambda function used to check elements in the array.Any Lambda function
<array>The array to be transformed by the function.Any array

​
Return Type

ARRAY having the return type of <function> as its element type

​
Examples

SELECT ARRAY_TRANSFORM(x -> x * 2, [ 1, 2, 3, 9 ] ) AS levels;
levels array(int)
[2, 4, 6, 18]

Rows: 1Execution time: 4.89ms

In the example below, the TRANSFORM function is used to CAST each element from a string to a date type. With each element now as a date type, the INTERVAL function is then used to add 5 years to each. 
SELECT
    ARRAY_TRANSFORM(x -> CAST(x as DATE) + INTERVAL '5 year',
        [ '1979-01-01', '1986-02-26', '1975-04-04' ] )
    AS registeredon;
registeredon array(timestamp)
[‘1984-01-01 00:00:00’, ‘1991-02-26 00:00:00’, ‘1980-04-04 00:00:00’]

Rows: 1Execution time: 5.62ms

In the example below, ARRAY_TRANSFORM is used with CASE to modify specific elements based on a condition. 
SELECT
    ARRAY_TRANSFORM(x, y -> CASE
        WHEN y = 'esimpson' THEN x
        ELSE 0
        END,
        [ 1, 2, 3 ],
        [ 'kennethpark', 'esimpson', 'sabrina21' ] )
    AS levels;
levels array(int)
[0, 2, 0]

Rows: 1Execution time: 5.21ms

This example again uses ARRAY_TRANSFORM with CASE. Elements that don’t meet the condition are left unchanged. 
SELECT
    ARRAY_TRANSFORM(x, y -> CASE
        WHEN y % 2 == 0
        THEN UPPER(x)
        ELSE x END,
        [ 'esimpson', 'sabrina21', 'kennethpark' ],
        [ 1, 2, 3 ] )
    AS players;
players array(text)
[‘esimpson’, ‘SABRINA21’, ‘kennethpark’]

Rows: 1Execution time: 5.32ms

This is another example using CASE that changes elements only if they meet the condition. 
SELECT
    ARRAY_TRANSFORM(x, y -> CASE
        WHEN x < y
        THEN y
        ELSE x END,
        [ 100, 700, 800 ],
        [ 300, 500, 200 ] )
    AS res;
res array(int)
[300, 700, 800]

Rows: 1Execution time: 5.87ms