如何基于Athena表中JSON数组字段创建指定结构视图

针对你的需求，因为数组元素顺序不固定，不能通过索引访问，需要将数组拆分行后通过条件聚合或PIVOT转换为固定列，以下是两种可行的实现方式：

方法一：条件聚合（兼容性更好） #

假设你的原始表名为raw_table，需保留原始表的唯一标识字段（例如id），SQL语句如下：

CREATE VIEW fixed_asset_view AS
SELECT
  -- 保留原始表的其他字段，按需添加
  id,
  -- 提取key为id的value
  MAX(CASE WHEN item.key = 'id' THEN item.value END) AS asset_id,
  -- 提取key为author的value
  MAX(CASE WHEN item.key = 'author' THEN item.value END) AS asset_author,
  -- 提取key为version的value
  MAX(CASE WHEN item.key = 'version' THEN item.value END) AS asset_version
FROM
  raw_table,
  -- 将JSON字符串解析为JSON类型，再拆分数组为多行
  UNNEST(json_parse(cmsmessage).asset.metadata.item) AS t(item)
-- 按原始表的唯一字段分组，确保同一原始行的聚合结果正确
GROUP BY
  id;

方法二：使用PIVOT语法（更简洁） #

如果你的Athena版本支持PIVOT（多数新版环境都支持），可以用更简洁的写法：

CREATE VIEW fixed_asset_view AS
SELECT
  id,
  asset_id,
  asset_author,
  asset_version
FROM (
  SELECT
    id,
    item.key,
    item.value
  FROM
    raw_table,
    UNNEST(json_parse(cmsmessage).asset.metadata.item) AS t(item)
  -- 过滤目标key，减少不必要的数据处理
  WHERE item.key IN ('id', 'author', 'version')
)
-- 将key列转换为固定列，聚合取对应value
PIVOT (
  MAX(value)
  FOR key IN ('id' AS asset_id, 'author' AS asset_author, 'version' AS asset_version)
) AS pivot_result;

说明 #

1. json_parse(cmsmessage)：将存储JSON字符串的cmsmessage列转换为Athena可操作的JSON类型。
2. UNNEST(...)：将数组拆分为多行，每个数组元素对应一行数据。
3. 两种方法都通过匹配key值提取对应value，完全不受数组元素顺序影响。
4. 如果某个key在数组中不存在，对应的列会返回NULL，可根据需求用COALESCE设置默认值。

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