如何在Redshift Spectrum中高效过滤含嵌套数组结构体的列？

1. 用 REDUCE 函数实现类 Athena any_match 的逻辑 #

Redshift 的 REDUCE 函数可遍历数组元素并累积判断结果，语法比关联 UNNEST 更简洁，适配顶层数组结构体的过滤场景，完美替代 Athena 的 any_match 逻辑。

针对你的 category 列（数组结构体类型），查询语句如下：

SELECT * 
FROM "events-data-lake-1791"
WHERE REDUCE(
    category,
    FALSE,
    (acc, val) -> acc OR (val.type = 'shopping'),
    acc -> acc
);

• 逻辑说明：
  • 初始累积值设为 FALSE
  • 遍历每个 category 元素 val，只要有一个元素的 type 匹配 shopping，就将累积值更新为 TRUE
  • 最终返回累积结果为 TRUE 的行，即符合过滤条件的完整数据

2. 嵌套数组结构体的处理方案 #

如果数据存在「结构体数组内嵌数组」的复杂场景（比如 category 中的结构体包含子数组字段），可以嵌套使用 REDUCE 实现多层条件判断。

假设数据结构为 [{type: 'shopping', id:34, sub_types: ['online', 'offline']}, {type:'cloths',id:54}]，要过滤出 category 中存在 type='shopping' 且 sub_types 包含 'online' 的行，语句如下：

SELECT * 
FROM "events-data-lake-1791"
WHERE REDUCE(
    category,
    FALSE,
    (acc, val) -> acc OR (
        val.type = 'shopping' 
        AND REDUCE(
            val.sub_types,
            FALSE,
            (sub_acc, sub_val) -> sub_acc OR (sub_val = 'online'),
            sub_acc -> sub_acc
        )
    ),
    acc -> acc
);

3. 备选：UNNEST 关联查询（适合需展开元素分析的场景） #

如果除了过滤，还需要对匹配的结构体元素做进一步分析，可使用 UNNEST 结合子查询，注意需加 DISTINCT 避免原表行被重复返回：

SELECT DISTINCT t.*
FROM "events-data-lake-1791" t,
     UNNEST(t.category) AS cat
WHERE cat.type = 'shopping';

内容的提问来源于stack exchange，提问作者Nishant Dixit