AWS Athena中的unnest用法

一、处理 arrays of array 结构

提出问题

在数据库中字段reward的数据格式如下：

[[1,0,200],[5,0,300],[1,0,400]]
是varchar格式，我希望取内部每个 中括号 中的第三个数字，并求平均值

解决方法

with  t1 as(
select '[[1,0,200],[5,0,300],[1,0,400]]' as reward
) ,t2 as (
select item[3] as reward_num
from t1 
cross join unnest(cast(json_parse(reward) as array(array(integer)))) as t(item)
)
select avg(reward_num) as avg_reward_num
from t2

详细解释

• json_parse(reward)将varchar类型转换为json类型
• unnest将横排的reward转化成了竖排，即在t2执行完后，得到的数据如下：

reward
[1,0,200]
[5,0,300]
[1,0,400]

二、处理 maps of array 结构

提出问题

在数据库中遇到如下格式字段：

[{"type":0,"num":10},{"type":1,"num":20},{"type":0,"num":30}]

是varchar格式
我们希望求type为0的总num

解决问题

方法与一中的类似，代码如下：

with t1 as (
select '[{"type":0,"num":10},{"type":1,"num":20},{"type":0,"num":30}]' as reward
),t2 as (
select cast(item['type'] as varchar) as type,cast(item['num'] as varchar) as num
from t1
cross join unnest(cast(json_parse(reward) as array(map(varchar,json)))) as t(item)
)
select sum(cast(num as integer)) as sum_num
from t2
where type='0'