如何在Redshift Spectrum中无需WHERE子句访问MAP类型值？

1. Redshift支持的类似Athena的直接访问语法 #

Redshift确实支持直接通过键名访问MAP类型元素，无需展开或添加WHERE子句，有两种简洁写法：

写法一：直接键索引 #

如果events字段是map<string, struct<string_value:string,int_value:bigint,float_value:double,double_value:double>>类型，可直接用以下语法提取指定键的struct字段：

SELECT
  a.events['event_id'].int_value AS event_id
FROM table a

若之前尝试点符号报错，大概率是写法疏漏（比如未指定表别名、键名拼写错误）；如果键不存在，该写法会返回NULL而非报错，可配合COALESCE处理空值：

SELECT
  COALESCE(a.events['event_id'].int_value, 0) AS event_id
FROM table a

写法二：使用map_get函数 #

更稳妥的方式是用Redshift内置的map_get函数，专门用于提取MAP中指定键的值，兼容性更强：

SELECT
  map_get(a.events, 'event_id').int_value AS event_id
FROM table a

当键不存在时，该函数会返回NULL，不会抛出异常，适合处理数据中可能存在的键缺失场景。

2. 标量子查询的效率问题 #

你提到的标量子查询写法：

SELECT
  (SELECT max(ep.value.int_value) from a.events as ep where ep.key = 'event_id')
FROM table a

虽然能得到结果，但在数百万行的场景下效率极低、成本很高：

• 每行都会单独执行一次子查询，相当于对每条记录的events字段重复扫描、过滤，占用大量Redshift计算资源
• Redshift Spectrum按扫描数据量收费，这种写法会导致重复扫描同一批S3数据，大幅增加成本
• 相比直接访问MAP的方式，查询时间会呈数量级增长

3. 各方案效率对比 #

• 最优方案：map_get函数或直接键索引写法，只需扫描一次数据，直接定位目标键值，计算和扫描成本最低
• 次优方案：你最初使用的JOIN展开写法（FROM table a, a.events ep WHERE ep.key = 'event_id'），虽会展开MAP为多行，但过滤逻辑一次性执行，效率远高于标量子查询
• 不推荐方案：标量子查询写法，仅适合小数据量测试，绝不能用于数百万/十亿级别的数据分析

内容的提问来源于stack exchange，提问作者Nick Edwards