Athena/Presto中如何拆分嵌套数组至两列并保持行聚合

针对你描述的嵌套数组结构，不需要用UNNEST拆分多行，直接用数组函数提取并聚合元素即可，每个id保留一行。

假设你的表名为target_table，包含id列和嵌套数组列data_array（结构为[{org=[..],auth={..}}, ...]），以下是实现SQL：

基础实现（保留原层级） #

SELECT
  id,
  -- 提取所有org数组，组成二维数组；若原数组为null则返回空数组
  COALESCE(transform(data_array, elem -> elem.org), ARRAY[]) AS org_col,
  -- 提取所有auth对象，组成对象数组；若原数组为null则返回空数组
  COALESCE(transform(data_array, elem -> elem.auth), ARRAY[]) AS auth_col
FROM target_table;

进阶：扁平化org数组 #

如果需要把所有org子数组的元素合并成一个一维数组，用flatten函数：

SELECT
  id,
  COALESCE(flatten(transform(data_array, elem -> elem.org)), ARRAY[]) AS flattened_org_col,
  COALESCE(transform(data_array, elem -> elem.auth), ARRAY[]) AS auth_col
FROM target_table;

过滤空元素 #

如果嵌套数组中存在org或auth为null的元素，可先过滤再提取：

SELECT
  id,
  COALESCE(
    transform(
      filter(data_array, elem -> elem.org IS NOT NULL),
      elem -> elem.org
    ),
    ARRAY[]
  ) AS org_col,
  COALESCE(
    transform(
      filter(data_array, elem -> elem.auth IS NOT NULL),
      elem -> elem.auth
    ),
    ARRAY[]
  ) AS auth_col
FROM target_table;

关键函数说明 #

• transform：遍历原数组的每个元素，提取指定字段生成新数组，不改变原表行数。
• COALESCE：处理原数组为null的场景，返回空数组避免结果出现null。
• flatten：将二维数组转为一维数组，适用于需要合并子数组元素的场景。
• filter：过滤掉数组中指定字段为null的元素，保证结果数组的纯净性。

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