AWS Athena使用serde格式提取JSON中数组及嵌套数组全量数据的方法

AWS Athena 基于 Trino（原 Presto）引擎，暂不支持 .data.balances[*] 这类通配符直接提取数组全量元素的语法，但可以通过以下两种常用方案实现无需逐个指定索引的全量提取：

方案1：保留单行结构，提取全量字段为数组格式 #

如果不需要把数组元素拆分为多行，只需要把目标字段统一提取为数组存储，可以用 transform 函数直接遍历数组处理：

-- 提取外层balances数组所有元素的内部嵌套balances数组的date字段，返回二维数组
SELECT
  transform(
    customerdata.data.balances,
    outer_balance -> transform(
      outer_balance.data.balances,
      inner_balance -> inner_balance.date
    )
  ) AS all_balance_dates
FROM 你的表名

返回的all_balance_dates是二维数组，第一层对应外层balances数组的索引，第二层对应每个外层元素下嵌套balances数组的date值。如果只需指定外层索引（比如你示例中的第8个外层元素），可以调整为customerdata.data.balances[8].data.balances作为transform的第一个参数，直接返回对应外层元素下所有date的一维数组。

方案2：展开数组为多行，方便后续分析 #

如果需要把数组元素拆分为独立行做过滤、聚合等操作，可以用UNNEST结合侧视图实现：

-- 两层嵌套数组全量展开，保留原数组索引
SELECT
  outer_idx,
  inner_idx,
  inner_balance.date AS balance_date
FROM 你的表名
-- 展开外层balances数组，返回元素和对应索引
CROSS JOIN UNNEST(customerdata.data.balances) WITH ORDINALITY AS t(outer_balance, outer_idx)
-- 展开外层元素下的嵌套balances数组
CROSS JOIN UNNEST(outer_balance.data.balances) WITH ORDINALITY AS t(inner_balance, inner_idx)
-- 可加过滤条件筛选指定外层索引，比如取第8个外层元素的所有内层值
-- WHERE outer_idx = 8

执行后每个嵌套的date值都会单独占一行，完全不需要手动逐个指定索引取值。不需要保留索引的话，去掉WITH ORDINALITY和对应的索引字段即可。

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