AWS Athena查询数组内JSON对象时遇INVALID_CAST_ARGUMENT错误

错误原因 #

你遇到的INVALID_CAST_ARGUMENT错误，核心原因是json_parse(plan)返回的不是JSON数组，而是单个JSON对象。大概率是两种情况导致：

• 表的默认输入格式（按换行分割行）把整个JSON数组拆成了多个片段，每个plan字段只包含数组的一部分，解析后无法识别为完整数组；
• 你的JSON文件实际结构和描述不符，并非外层包裹数组，而是单个JSON对象。

针对性解决方案 #

情况1：文件确实是单个JSON数组（整个文件为[{...}]） #

步骤1：修改表定义，确保整个文件作为一行读取

默认的文本输入格式会按换行分割内容，导致数组被拆碎。改用WholeFileInputFormat让整个文件作为一行：

DROP TABLE IF EXISTS aetna.adobe_temp_5;
CREATE EXTERNAL TABLE IF NOT EXISTS aetna.adobe_temp_5 (
  plan string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.WholeFileInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://insurance-transparency-data/'
TBLPROPERTIES (
  'has_encrypted_data' = 'false'
);

步骤2：解析数组并查询嵌套字段

展开数组后，用json_extract系列函数提取嵌套数据：

SELECT 
  json_extract_scalar(e, '$.reporting_entity_name') AS reporting_entity_name,
  json_extract_scalar(e, '$.reporting_entity_type') AS reporting_entity_type,
  date_parse(json_extract_scalar(e, '$.last_updated_on'), '%Y-%m-%d') AS last_updated_on,
  json_extract(e, '$.providers') AS providers,
  json_extract(e, '$.in-network') AS in_network
FROM adobe_temp_5
CROSS JOIN UNNEST(CAST(json_parse(plan) AS array(json))) AS t(e);

情况2：文件每行是单个JSON对象（无外层数组） #

如果你的文件实际是每行一个独立的JSON对象，直接用JSON SerDe解析更高效：

步骤1：重新定义表结构

DROP TABLE IF EXISTS aetna.adobe_temp_5;
CREATE EXTERNAL TABLE IF NOT EXISTS aetna.adobe_temp_5 (
  reporting_entity_name string,
  reporting_entity_type string,
  last_updated_on date,
  providers array<json>,
  `in-network` array<json>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'dots.in.keys' = 'true' -- 处理字段名中的短横线
)
LOCATION 's3://insurance-transparency-data/'
TBLPROPERTIES ('has_encrypted_data' = 'false');

步骤2：直接查询字段

无需额外解析，直接查询嵌套数据：

SELECT 
  reporting_entity_name,
  reporting_entity_type,
  last_updated_on,
  providers
FROM aetna.adobe_temp_5;

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