ARRAY_FILTER

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns a new array containing only the elements for which the provided lambda expression returns true.

1ARRAY_FILTER(<array>, <lambda>)

• <array>: The input array to filter.
• <lambda>: A lambda expression that takes one parameter and returns a boolean value. Elements for which the lambda returns true are included in the result.

Returns an array containing only the elements where the lambda expression evaluated to true. Returns NULL if the input array is NULL. Returns an empty array if no elements match the condition.

• The lambda expression must return a boolean value. Non-boolean return types result in an error.
• The lambda can’t reference columns or variables from the outer query scope.
• The order of elements in the result matches their order in the input array.

Keep only even numbers from an array.

1SELECT ARRAY_FILTER(ARRAY[1, 2, 3, 4, 5, 6], lambda x: x % 2 = 0) AS evens;

Returns [2, 4, 6].

Keep strings that don’t contain uppercase letters.

1SELECT ARRAY_FILTER(
2  ARRAY['abc', 'very long string', 'UPPER case', 'lower case'],
3  lambda x: NOT REGEXP_LIKE(x, '[A-Z]')
4) AS lowercase_only;

Returns ["abc", "very long string", "lower case"].

Filter nested arrays based on their contents.

1SELECT ARRAY_FILTER(
2  ARRAY[ARRAY[1, 2, 3], ARRAY[4, 5, 6], ARRAY[2, 3, 4]],
3  lambda x: ARRAY_CONTAINS(x, 3)
4) AS contains_three;

Returns [[1, 2, 3], [2, 3, 4]].

Keep elements greater than a threshold.

1SELECT ARRAY_FILTER(ARRAY[10, 25, 5, 30, 15], lambda x: x > 20) AS large_values;

Returns [25, 30].

Chain ARRAY_FILTER with other array operations.

1SELECT ARRAY_LENGTH(
2  ARRAY_FILTER(ARRAY[1, 2, 3, 4, 5], lambda x: x > 2)
3) AS count_above_two;

Returns 3.

• Lambda Expressions
• ARRAY_TRANSFORM
• ARRAY_CONTAINS
• Array Data Type