如何在Redshift Spectrum中正确Unnest两列嵌套数组

你原来的查询产生重复数据的原因是两次LEFT JOIN生成了笛卡尔积：每个student都会和所有grade配对，比如C1的3个学生对应3个成绩，会得到9条冗余记录，完全不符合需求。

要实现同位置数组元素一一对应的Unnest，需要借助数组元素的位置索引来关联两个数组，Redshift提供了两种常用方式：

方法1：使用posexplode函数 #

posexplode会同时返回数组元素和它的位置索引（从1开始），通过匹配索引来关联两个数组：

SELECT 
    r.class,
    s.student,
    g.grade
FROM spectrum.results r
JOIN posexplode(r.students) AS s(student_pos, student)
JOIN posexplode(r.grades) AS g(grade_pos, grade)
ON s.student_pos = g.grade_pos;

方法2：使用UNNEST ... WITH ORDINALITY #

WITH ORDINALITY会给Unnest后的元素添加位置序号，同样通过序号匹配来关联：

SELECT 
    r.class,
    s.student,
    g.grade
FROM spectrum.results r
JOIN UNNEST(r.students) WITH ORDINALITY AS s(student, pos)
JOIN UNNEST(r.grades) WITH ORDINALITY AS g(grade, pos)
ON s.pos = g.pos;

注意事项 #

• 如果存在students和grades数组长度不一致的行，需要将JOIN改为LEFT JOIN，避免丢失数据（但需确认业务逻辑是否允许这种情况）。
• 两种方法的本质都是通过位置索引绑定对应元素，彻底避免笛卡尔积问题，返回你需要的一对一关联结果。

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