ARRAY_TRANSFORM

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Applies a lambda expression to each element (or corresponding elements from multiple arrays) and returns a new array with the transformed values.

1ARRAY_TRANSFORM(<array1>[, <array2>, ...], <lambda>)

• <array1>: The first input array.
• <lambda>: A lambda expression that defines the transformation. The number of parameters in the lambda must match the number of input arrays.

• <array2>, ...: Additional input arrays. When multiple arrays are provided, the lambda receives corresponding elements from each array. All arrays must have the same length.

Returns an array with the same length as the input arrays, where each element is the result of applying the lambda expression to the corresponding input element(s). Returns NULL if any input array is NULL.

• All input arrays must have the same length. Mismatched lengths result in an error.
• NULL elements in the input arrays propagate through the lambda. If any input element is NULL, the corresponding output element is typically NULL (depending on the lambda expression).
• The lambda’s return type determines the output array’s element type. If the lambda returns only NULL, you must explicitly cast it to a specific type.
• Empty arrays return an empty array.

Double each element in an array.

1SELECT ARRAY_TRANSFORM(ARRAY[1, 2, 3], lambda x: x * 2) AS doubled;

Returns [2, 4, 6].

Add corresponding elements from two arrays.

1SELECT ARRAY_TRANSFORM(ARRAY[1, 2, 3], ARRAY[10, 20, 30], lambda x, y: x + y) AS sums;

Returns [11, 22, 33].

Concatenate corresponding strings from two arrays.

1SELECT ARRAY_TRANSFORM(
2  ARRAY['hello', 'good'], 
3  ARRAY[' world', ' bye'], 
4  lambda x, y: x || y
5) AS greetings;

Returns ["hello world", "good bye"].

Use CASE to apply conditional logic.

1SELECT ARRAY_TRANSFORM(
2  ARRAY[1, 5, 3], 
3  ARRAY[4, 2, 6], 
4  lambda x, y: CASE WHEN x > y THEN x ELSE y END
5) AS maximums;

Returns [4, 5, 6].

Compare corresponding elements and return boolean results.

1SELECT ARRAY_TRANSFORM(ARRAY[1, 5, 3], ARRAY[4, 2, 6], lambda x, y: x > y) AS comparisons;

Returns [false, true, false].

Combine elements from three arrays.

1SELECT ARRAY_TRANSFORM(
2  ARRAY[1, 2], 
3  ARRAY[3, 4], 
4  ARRAY[5, 6], 
5  lambda x, y, z: x + y + z
6) AS totals;

Returns [9, 12].

NULL elements propagate through the transformation.

1SELECT ARRAY_TRANSFORM(ARRAY[1, NULL, 3], ARRAY[4, 5, 6], lambda x, y: x + y) AS result;

Returns [5, NULL, 9].

Use nested calls to chain transformations.

1SELECT ARRAY_TRANSFORM(
2  ARRAY_TRANSFORM(ARRAY[1, 2], ARRAY[3, 4], lambda x, y: x + y),
3  ARRAY[10, 20],
4  lambda x, y: x * y
5) AS result;

Returns [40, 120].

• Lambda Expressions
• ARRAY_FILTER
• Array Data Type