TRANSFORM
Returns an array by applying <func>
on each element of <arr>
.
The Lambda function <func>
is mandatory.
Syntax
TRANSFORM(<func>, <arr>)
Parameter | Description |
---|---|
<func> | A Lambda function used to check elements in the array. |
<arr> | The array to be transformed by the function. |
Examples
SELECT
TRANSFORM(x -> x * 2, [ 1, 2, 3, 9 ] ) AS res;
Returns: 2,4,6,18
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
TRANSFORM(x -> CAST(x as DATE) + INTERVAL '5 year',
[ '1979-01-01', '1986-02-26', '1975-04-04' ] )
AS res;
Returns: ["1984-01-01 05:06:00","1991-02-26 05:06:00","1980-04-03 05:06:00"]
In the example below, TRANSFORM
is used with CASE
to modify specific elements based on a condition.
SELECT
TRANSFORM(x, y -> CASE
WHEN y = 'green' THEN x
ELSE 0
END,
[ 1, 2, 3 ],
[ 'red', 'green', 'blue' ] )
AS res;
Returns: [0,2,0]
This example again uses TRANSFORM
with CASE
. Elements that don’t meet the condition are left unchanged.
SELECT
TRANSFORM(x, y -> CASE
WHEN y % 2 == 0
THEN UPPER(x)
ELSE x END,
[ 'red', 'green', 'blue' ],
[ 1, 2, 3 ] )
AS res;
Returns: ["red","GREEN","blue"]
This is another example using CASE
that changes elements only if they meet the condition.
SELECT
TRANSFORM(x, y -> CASE
WHEN x < y THEN y
ELSE x END,
[ 100, 700, 800 ],
[ 300, 500, 200 ] )
AS res;
Returns: [300,700,800]