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.
Syntax
array_transform(<array1>[, <array2>, ...], <lambda>)
Arguments
Required
<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.
Optional
<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
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.
Considerations
- 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.
Examples
Transform a Single Array
Double each element in an array.
SELECT array_transform(ARRAY[1, 2, 3], lambda x: x * 2) AS doubled;
Returns [2, 4, 6].
Combine Two Arrays
Add corresponding elements from two arrays.
SELECT array_transform(ARRAY[1, 2, 3], ARRAY[10, 20, 30], lambda x, y: x + y) AS sums;
Returns [11, 22, 33].
String Concatenation
Concatenate corresponding strings from two arrays.
SELECT array_transform(
ARRAY['hello', 'good'],
ARRAY[' world', ' bye'],
lambda x, y: x || y
) AS greetings;
Returns ["hello world", "good bye"].
Conditional Transformation
Use CASE to apply conditional logic.
SELECT array_transform(
ARRAY[1, 5, 3],
ARRAY[4, 2, 6],
lambda x, y: CASE WHEN x > y THEN x ELSE y END
) AS maximums;
Returns [4, 5, 6].
Comparison Operations
Compare corresponding elements and return boolean results.
SELECT array_transform(ARRAY[1, 5, 3], ARRAY[4, 2, 6], lambda x, y: x > y) AS comparisons;
Returns [false, true, false].
Three or More Arrays
Combine elements from three arrays.
SELECT array_transform(
ARRAY[1, 2],
ARRAY[3, 4],
ARRAY[5, 6],
lambda x, y, z: x + y + z
) AS totals;
Returns [9, 12].
NULL Handling
NULL elements propagate through the transformation.
SELECT array_transform(ARRAY[1, NULL, 3], ARRAY[4, 5, 6], lambda x, y: x + y) AS result;
Returns [5, NULL, 9].
Nested Array Transform
Use nested calls to chain transformations.
SELECT array_transform(
array_transform(ARRAY[1, 2], ARRAY[3, 4], lambda x, y: x + y),
ARRAY[10, 20],
lambda x, y: x * y
) AS result;
Returns [40, 120].