Presto中展开Map内日期数组为行（仅提取RETURNED键值）

问题场景 #

现有表结构如下（map字段为键值对类型，部分键对应的值是日期数组）：

---
id | map
---
1 | {"LEGAL_HOLD": ["2021-06-10"], "RETURNED": ["2021-06-10", "2022-07-01"]}
2 | {"RETURNED": ["2021-06-10", "2022-07-10"]}

需要提取RETURNED键对应的日期数组，并将数组元素展开为单独行，得到目标结果：

---
id | dates
---
1  | 2021-06-10 
1  | 2022-07-01
2  | 2021-06-10
2  | 2022-07-10

原SQL的问题 #

你提供的SQL存在两个核心问题：

1. 测试数据构造错误：原CTE中MAP函数的参数未将值定义为数组，导致RETURNED对应的值是单个日期而非数组；
2. Unnest用法错误：cross join unnest (table)是对整个表进行unnest，而非针对map字段中的数组值，因此无法将数组展开为行。

正确解法 #

先提取map中RETURNED对应的日期数组，再对该数组执行unnest操作实现行展开，同时可选过滤掉没有RETURNED键的行：

with your_table as (
-- 正确构造测试数据：每个键对应的值为数组
SELECT 1 as id, MAP(ARRAY['LEGAL_HOLD','RETURNED'], ARRAY[ARRAY[date('2021-06-10')], ARRAY[date('2021-06-10'), date('2022-07-01')]]) as map
union all  
SELECT 2 as id, MAP(ARRAY['RETURNED'], ARRAY[ARRAY[date('2021-06-10'), date('2022-07-10')]]) as map
)
select 
    id, 
    date as dates
from your_table
-- 对RETURNED对应的数组执行unnest，将每个元素转为单独行
cross join unnest(map['RETURNED']) as t(date)
-- 过滤掉不存在RETURNED键的行（可选）
where map['RETURNED'] is not null;

如果你的SQL方言支持更简洁的写法（比如BigQuery），也可以直接在SELECT子句中使用unnest：

with your_table as (
SELECT 1 as id, MAP('LEGAL_HOLD', ['2021-06-10'], 'RETURNED', ['2021-06-10', '2022-07-01']) as map
union all  
SELECT 2 as id, MAP('RETURNED', ['2021-06-10', '2022-07-10']) as map
)
select 
    id, 
    unnest(map['RETURNED']) as dates
from your_table
where map['RETURNED'] is not null;

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