Array FILTER/TRANSFORM lambda using index

Question

Snowflake supports higher order functions like FILTER or TRANSFORM.

A lambda expression that defines the filter condition on each array element.

The lambda expression must have only one argument specified in the following syntax:
<arg> [ <datatype> ] -> <expr>

This type of functions may be also known as array_filter/list_filter, array_map, array_transform, apply, etc.

Example:

SELECT FILTER([10,20,30,40], x -> x > 20);
-- [30, 40]

My scenario requires to operate on element's index of the array, like choosing every second element. Pseudocode below:

SELECT FILTER([10,20,30,40], (x, i) -> i % 2 = 0);
-- [10, 30]

SELECT TRANSFORM([10,20,30,40], (x, i) -> CASE WHEN i % 2 = 0 THEN x * 10 ELSE x END);
-- [100, 20, 300, 40]

For sample input:

CREATE OR REPLACE TABLE tab(id INT, arr ARRAY) AS
SELECT 1, ['a','b','b', 'd', 'c','c'] UNION ALL
SELECT 2, [] UNION ALL
SELECT 3, ['a','a','a','a'] UNION ALL
SELECT 4, ['a',null, null] ;

Desired output (filtering every second element/transforming every second element with prefix):

+----+---------------------------+---------------------+------------------------------------------------+
| ID |              ARR          | FILTER_EVERY_SECOND |          TRANSFORM_ARR_PREFIX_EVERY_SECOND     |
+----+---------------------------+---------------------+------------------------------------------------+
|  1 | ["a","b","b","d","c","c"] | [a","b","c"]        | ["prefix_a","b","prefix_b","d","prefix_c","c"] |
|  2 | []                        | []                  | []                                             |
|  3 | ["a","a","a","a"]         | ["a","a"]           | ["prefix_a","a","prefix_a","a"]                |
|  4 | ["a",undefined,undefined] | ["a",undefined]     | ["prefix_a",undefined,undefined]               |
+----+---------------------------+---------------------+------------------------------------------------+

Preferred solution:

I am aware of flattening and and aggregating back with array_agg approach, though I am seeking for other SQL approaches using built-in capabilities, ideally a single expression(to easily plug it into existing queries).

Lukasz Szozda · Accepted Answer · 2026-01-01 09:09:27Z

The idea is to emulate index logic by using higher order function REDUCE and structure {res:array, i:current_index} as an accumulator.

FILTER(arr, (x, i) -> i % 2 = 0) becomes:

SELECT id, arr,
 REDUCE(arr, {'res':[], 'i':0},
  (acc, x)->{'res':CASE WHEN acc:i%2=0 THEN ARRAY_APPEND(acc:res,x) ELSE acc:res END
             ,'i':acc:i+1} 
 ):res::ARRAY AS filter_arr_every_second
FROM tab;

TRANSFORM(arr, (x, i) -> CASE WHEN i%2=0 THEN 'prefix_' || x ELSE x END) becomes:

SELECT id, arr,
 REDUCE(arr, {'res':[], 'i':0},
  (acc, x)->{'res':ARRAY_APPEND(acc:res,CASE WHEN acc:i%2=0 THEN 'prefix_'||x ELSE x END)
             ,'i':acc:i+1} 
 ):res::ARRAY AS transform_arr_prefix_every_second  
FROM tab;

Output:

Collectives™ on Stack Overflow

Array FILTER/TRANSFORM lambda using index

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related