如何在AWS Athena/Presto中将JSON数组解析映射为表列

你遇到的是典型的JSON键值对数组行列转换场景，在Athena/Presto中推荐优先使用map_from_entries函数实现，写法简洁性能最优。

前置假设 #

假设你的原始表名为email_raw，存储JSON数据的headers字段为两种类型之一：

• 类型1：已经被解析为ARRAY<STRUCT(name VARCHAR, value VARCHAR>>数组类型
• 类型2：未解析的JSON字符串类型

最优实现方案 #

先将整个headers数组转换为name到value的映射字典，再直接按键提取对应值作为独立列：

SELECT
  header_map['Date'] AS `Date`,
  header_map['From'] AS `From`,
  header_map['To'] AS `To`,
  -- 需要其他头字段直接按格式新增即可
  header_map['Subject'] AS `Subject`,
  header_map['Content-Type'] AS `Content_Type`
FROM (
  SELECT
    -- 如果headers是已解析的数组类型，直接用下面这行
    map_from_entries(headers) AS header_map
    -- 如果headers是JSON字符串，替换上面一行为下面的代码：
    -- map_from_entries(CAST(json_parse(headers) AS ARRAY(ROW(name VARCHAR, value VARCHAR)))) AS header_map
  FROM email_raw
) t

运行后输出的结果完全匹配你需要的表格格式，不存在的头字段会自动返回NULL不会触发报错。

兼容老版本备选方案 #

如果你的Athena引擎版本较低不支持map_from_entries函数，可以用UNNEST打散数组后聚合的方式实现：

SELECT
  MAX(IF(h.name = 'Date', h.value, NULL)) AS `Date`,
  MAX(IF(h.name = 'From', h.value, NULL)) AS `From`,
  MAX(IF(h.name = 'To', h.value, NULL)) AS `To`
FROM email_raw
-- 如果你的表有主键/行唯一标识字段，需要在GROUP BY中携带该字段
-- 示例：GROUP BY email_id, send_time
CROSS JOIN UNNEST(headers) AS h(name, value)

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