AWS Athena 提取JSON内容转为多行数据的技术问题咨询

json_extract仅适合提取固定位置的元素，要动态展开整个JSON数组的元素为多行，无需手动指定下标，可使用Athena内置的UNNEST运算符搭配JSON解析函数实现，以下是具体用法：

标准JSON数组展开 #

如果你的待处理字段为JSON数组格式的字符串，例如json_arr字段存储内容为[{"id":1,"name":"张三"},{"id":2,"name":"李四"}]，使用以下查询语句：

SELECT
  -- 保留原表其他需要查询的字段
  t.base_id,
  -- 提取展开后的单个数组元素的属性
  json_extract_scalar(u.item, '$.id') AS item_id,
  json_extract_scalar(u.item, '$.name') AS item_name
FROM your_table_name t
CROSS JOIN UNNEST(
  -- 先将JSON字符串解析为JSON类型，再强转为JSON数组适配UNNEST输入要求
  CAST(json_parse(t.json_arr) AS ARRAY<JSON>)
) AS u(item)

上述语句会自动将每个JSON数组的所有元素展开为独立行，数组长度不固定也可正常适配。

嵌套JSON数组展开 #

如果数组存储在JSON对象的嵌套key下，例如JSON内容为{"user_list":[{"id":1},{"id":2}]}，调整解析逻辑即可：

SELECT
  t.base_id,
  json_extract_scalar(u.item, '$.id') AS item_id
FROM your_table_name t
CROSS JOIN UNNEST(
  CAST(json_extract(json_parse(t.json_field), '$.user_list') AS ARRAY<JSON>)
) AS u(item)

特殊场景适配 #

• 若需要保留数组为空/无效的原行数据，将CROSS JOIN替换为LEFT JOIN并补充ON TRUE条件即可：

SELECT
  t.base_id,
  json_extract_scalar(u.item, '$.id') AS item_id
FROM your_table_name t
LEFT JOIN UNNEST(
  CAST(json_parse(t.json_arr) AS ARRAY<JSON>)
) AS u(item) ON TRUE

• 若你的待处理字段已经是Athena的JSON类型而非字符串类型，可省略json_parse步骤，直接强转为ARRAY<JSON>使用。

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