无法从Athena表提取JSON嵌套数组子字段的问题求助

问题根源 #

你的requestbody字段是字符串类型，而非Athena可直接解析的结构化JSON/ROW类型，直接用点语法提取嵌套字段会触发类型不匹配错误。

具体解决方案 #

1. 先将字符串转为JSON类型

使用Athena内置的json_parse()函数（或cast(requestbody as json)）把字符串解析成结构化JSON，之后才能识别嵌套结构：

SELECT json_parse(requestbody) AS parsed_json
FROM your_external_table

2. 提取嵌套数组中的子字段

如果applicant是数组内的元素，需要先用unnest()展开数组，再通过JSON路径提取字段：
示例SQL（假设applicant在parsed_json的applications数组下）：

SELECT 
  -- 提取字符串类型字段用json_extract_scalar，保留JSON结构用json_extract
  json_extract_scalar(app_item, '$.applicant.name') AS applicant_name,
  json_extract_scalar(app_item, '$.applicant.id') AS applicant_id
FROM your_external_table,
     -- 展开applications数组，别名t(app_item)
     unnest(json_parse(requestbody)->'applications') AS t(app_item)

3. 复杂JSON的路径验证

如果JSON层级深、字段多，可先通过json_keys()查看顶层键，确认路径正确性：

-- 查看JSON顶层键
SELECT json_keys(json_parse(requestbody)) AS top_level_keys
FROM your_external_table LIMIT 1

注意事项 #

• 确保JSON路径书写正确，多层嵌套用$.parent.child格式，数组元素用[*]或通过unnest展开
• 针对5000字段的超大JSON，尽量只提取需要的字段，避免全量解析影响查询性能

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