Presto中将字典列表格式varchar解析为行数组报错解决方法

需要将varchar类型字段（存储内容为字典列表格式JSON）转换为结构化的行数组，字段存储样例如下：

初始尝试的转换SQL如下：

select  cast(json_parse(sponsored_bids_filtered) as array(row (bid varchar, day date, eventtime timestamp))) 

执行后返回错误：

INVALID_CAST_ARGUMENT: Cannot cast JSON to array(row(bid varchar, day date, eventtime timestamp))

Presto/Trino（含基于其封装的Athena等查询引擎）中，json_parse输出的JSON类型直接cast为ROW结构时，仅支持自动映射JSON原生类型：字符串转varchar、数值转数字类、布尔转boolean、数组转array、对象转row，不会自动将字符串格式的日期、时间值转换为date、timestamp类型。cast时直接指定day date、eventtime timestamp两个非JSON原生类型，是触发报错的核心原因。
少数场景下，字段内存在个别行JSON格式异常、key名大小写不匹配、值类型不一致（比如bid存为数值、day格式不是标准日期格式），也会触发同类报错。

• 第一步校验JSON合法性：单独执行select json_parse(sponsored_bids_filtered) from 你的表 limit 10，确认不存在单引号代替双引号、多余逗号、引号不匹配等格式错误。
• 第二步校验值格式一致性：抽样检查字段内的day值是否均为yyyy-mm-dd格式、eventtime是否为标准可解析的时间格式、bid是否均为字符串类型、三个key是否在所有行都存在。

通用兼容写法（支持所有Presto/Trino/Athena版本） #

先将JSON cast为全varchar类型的ROW数组，再通过transform函数逐字段做类型转换，代码如下：

select 
  transform(
    cast(json_parse(sponsored_bids_filtered) as array(row(bid varchar, day varchar, eventtime varchar))),
    item -> row(
      item.bid,
      -- 若day不是yyyy-mm-dd标准格式，替换为date_parse(item.day, '自定义格式串')
      date(item.day),
      -- 若eventtime带时区/非标准格式，替换为date_parse或对应时区转换函数
      timestamp(item.eventtime)
    )
  ) as sponsored_bids_struct
from 你的表名

如果存在部分行格式异常可能导致转换失败，可以用try()包裹类型转换逻辑，失败时返回null避免整个查询中断，例如try(date(item.day)) as day。

高版本Trino简化写法 #

如果你使用的是Trino 393及以上版本，可以直接用from_json函数传入schema定义，引擎会自动解析标准格式的日期时间类型：

select 
  from_json(
    sponsored_bids_filtered,
    'array(row(bid varchar, day date, eventtime timestamp))'
  ) as sponsored_bids_struct
from 你的表名

如果该写法仍报错，换回前面的通用写法即可。

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