如何在AWS Athena中从JSON对象数组提取指定字段？

假设你的表名为your_table，存储JSON数组的列名为json_column，JSON结构如下：

[
  {
    "event_type": "application_state_transition",
    "data": {
      "event_id": "-3368023833341021830"
    }
  },
  {
    "event_type": "application_state_transition",
    "data": {
      "event_id": "5692882176024811076"
    }
  }
]

需求是提取所有event_id的值，无需严格限定格式，只要能获取到目标ID即可。之前尝试用JSON_EXTRACT配合jq风格的.[].data.event_id语法失败，因为Athena的JSON函数语法与jq不兼容，以下是几种可行方案：

方案1：展开数组并提取单个event_id（拆分行） #

使用JSON_EXTRACT_ARRAY_ELEMENTS将数组拆分为单独的JSON对象，再用JSON_EXTRACT_SCALAR提取event_id值，适合需要将每个ID作为独立行输出的场景：

SELECT 
  JSON_EXTRACT_SCALAR(event_obj, '$.data.event_id') AS event_id
FROM your_table,
UNNEST(JSON_EXTRACT_ARRAY_ELEMENTS(json_column)) AS t(event_obj)

方案2：生成包含所有event_id的数组（保留列表形式） #

使用TRANSFORM函数遍历数组，将每个元素映射为对应的event_id，直接返回数组格式的结果：

SELECT 
  TRANSFORM(
    JSON_EXTRACT_ARRAY_ELEMENTS(json_column),
    x -> JSON_EXTRACT_SCALAR(x, '$.data.event_id')
  ) AS event_ids
FROM your_table

方案3：使用JSONPath数组查询（Athena版本≥3支持） #

如果你的Athena使用的是引擎版本3及以上，可以用JSON_PATH_QUERY_ARRAY函数，支持更接近jq的JSONPath语法直接提取数组内的所有目标字段：

SELECT 
  JSON_PATH_QUERY_ARRAY(json_column, '$[*].data.event_id') AS event_ids
FROM your_table

补充说明 #

• JSON_EXTRACT_SCALAR用于提取字符串类型的标量值，避免返回带引号的JSON字符串；
• UNNEST配合JSON_EXTRACT_ARRAY_ELEMENTS是处理JSON数组的常用方式，能将数组元素横向展开为多行；
• TRANSFORM适合需要保留数组结构的场景，输出结果就是包含所有event_id的数组。

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