如何在Athena（Trino）中将嵌套数组拆分为带索引的列？

原始表结构 #

id, authors
123,[{institutions=[<more_content>], author={name="james"}}, {institutions=[<more content>], author={name="john"}}]
245,[]
765,[{institutions=[<more_content>], author={name="mike"}}, {institutions=[<more content>], author={name="peter"}}]

期望输出 #

id, author_row_number,name
123,1,james
123,2,john
765,1,mike
765,2,peter

实现SQL #

利用Trino（Athena基于Trino）的UNNEST配合WITH ORDINALITY语法即可实现需求，具体SQL如下：

SELECT
  t.id,
  u.author_row_number,
  u.author_info.author.name AS name
FROM
  your_table_name t
CROSS JOIN UNNEST(t.authors) WITH ORDINALITY AS u(author_info, author_row_number)
WHERE
  cardinality(t.authors) > 0;

关键语法说明 #

• UNNEST(t.authors)：将authors数组中的每个元素拆分为单独的行
• WITH ORDINALITY：为拆分后的每一行生成从1开始的递增索引，对应需求中的author_row_number
• CROSS JOIN：自动排除原始表中authors为空数组的行（如id=245的记录），若需保留空数组行可改用LEFT JOIN，此时空数组对应的author_row_number和name会为NULL
• cardinality(t.authors) > 0：过滤空数组行，和CROSS JOIN效果一致，可根据习惯选择是否保留

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