Amazon Athena/Presto SQL中Unnest varchar列及类型转换报错问题

问题回顾 #

你的交易数据中labels列是varchar类型，存储格式类似数组字符串，但直接Unnest或转换数组时出现多个报错：

1. 直接Unnest报错：

   INVALID_FUNCTION_ARGUMENT: Cannot unnest type: varchar

2. JSON解析转数组报错：

   INVALID_CAST_ARGUMENT: Cannot cast to array(varchar). ['withdraw', 'holiday']

3. 测试数组语句报错：

   mismatched input '['. Expecting:

报错原因分析 #

1. Unnest报错：UNNEST函数仅支持数组、集合等可迭代类型，varchar字符串无法直接被Unnest处理。
2. JSON转数组报错：你的labels字符串用单引号包裹元素，不符合标准JSON格式（JSON要求字符串用双引号），导致json_parse解析后无法正确转换为array(varchar)。
3. 测试语句报错：SQL中数组字面量的正确语法是array['元素1','元素2']，直接写['元素1','元素2']属于语法错误。

解决方案 #

1. 修正测试语句的数组写法

正确的数组构造语法如下：

-- 直接构造数组
select array['first_record','transfer'] as array_varchar;

-- 强制转换类型（部分引擎需要）
select cast(array['first_record','transfer'] as array(varchar)) as array_varchar;

2. 正确转换Varchar字符串为数组并Unnest

针对你的labels格式（单引号包裹元素的类数组字符串），推荐以下两种方法：

方法一：修复JSON格式后解析（优先推荐）

先将字符串中的单引号替换为双引号，使其符合标准JSON格式，再解析为数组并Unnest：

select 
  record_id, 
  new_labels
from transactions,
unnest(
  cast(
    json_parse(regexp_replace(labels, '''', '"')) as array(varchar)
  )
) as t(new_labels)
order by record_id, new_labels;

方法二：字符串分割法（适用于不支持JSON解析的引擎）

如果你的SQL引擎不支持json_parse，可以通过去除首尾括号、分割字符串的方式生成数组：

select 
  record_id, 
  new_labels
from transactions,
unnest(
  split(trim(labels, '[]'), ', ')
) as t(new_labels)
order by record_id, new_labels;

注意：如果标签内容本身包含, （逗号加空格），这种方法会错误分割标签，此时优先使用方法一。

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