如何在Snowflake中使用带索引的FILTER/TRANSFORM高阶函数

Snowflake的FILTER、TRANSFORM等高阶函数，其Lambda表达式仅支持单个参数，但实际场景中常需要基于数组元素的索引进行操作——比如筛选索引为偶数的元素，或是给偶数索引的元素添加前缀。以下是仅使用Snowflake内置能力的单表达式实现方案，无需借助FLATTEN结合ARRAY_AGG的写法，方便嵌入现有查询。

示例表准备 #

先创建测试用表：

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] ;

1. 筛选数组中索引为偶数的元素 #

利用ARRAY_TRANSFORM的双参数特性（支持同时获取元素值和索引），先构建包含元素与对应索引的结构化数组，再通过FILTER筛选索引为偶数的元素，最后提取原元素：

FILTER(
    ARRAY_TRANSFORM(arr, (val, idx) => OBJECT_CONSTRUCT('val', val, 'idx', idx)),
    x => MOD(x:idx::INT, 2) = 0
):val::ARRAY

说明：

• ARRAY_TRANSFORM的双参数形式可直接获取数组元素值和其索引，先将二者封装为对象
• FILTER遍历对象数组，筛选出索引为偶数的对象
• 通过:val::ARRAY提取所有符合条件的元素，还原为目标数组

2. 为索引为偶数的元素添加前缀 #

同样借助ARRAY_TRANSFORM的双参数能力，判断当前元素的索引是否为偶数，按需添加前缀：

ARRAY_TRANSFORM(
    arr,
    (val, idx) => IFF(MOD(idx::INT, 2) = 0, CONCAT('prefix_', val), val)
)

说明：

• ARRAY_TRANSFORM的双参数形式直接获取元素值和索引
• 通过IFF判断索引是否为偶数，是则添加指定前缀，否则保留原元素

完整查询示例 #

将上述逻辑整合到查询中，输出原数组、筛选后数组、转换后数组：

SELECT
    id,
    arr AS 原数组,
    -- 筛选偶数索引元素
    FILTER(
        ARRAY_TRANSFORM(arr, (val, idx) => OBJECT_CONSTRUCT('val', val, 'idx', idx)),
        x => MOD(x:idx::INT, 2) = 0
    ):val::ARRAY AS 偶数索引筛选数组,
    -- 为偶数索引元素加前缀
    ARRAY_TRANSFORM(
        arr,
        (val, idx) => IFF(MOD(idx::INT, 2) = 0, CONCAT('prefix_', val), val)
    ) AS 偶数索引加前缀数组
FROM tab;

执行后得到的结果：

内容的提问来源于stack exchange，提问作者Lukasz Szozda