SQL中如何从数组映射（Map of Arrays）提取单个值？

针对将map<int, array<int>>类型的列转换为map<int, int>，提取每个数组的第一个元素（空数组返回NULL）的需求，以下是不同SQL引擎的实现方法：

Spark SQL 实现 #

Spark SQL提供了transform_values函数，可直接对map的所有值进行转换，无需拆解聚合：

SELECT transform_values(profiles, (key, arr) -> IF(size(arr) > 0, arr[0], NULL)) AS new_profiles
FROM your_table;

• transform_values：遍历原map的每个键值对，对数组值执行自定义转换逻辑
• size(arr)：判断数组长度，避免空数组访问下标报错
• arr[0]：取数组的第一个元素（Spark数组下标从0开始）

Hive SQL 实现 #

Hive没有直接的map值转换函数，需要先拆解map为键值对行，处理后再聚合回map：

SELECT 
    collect_map(map_key, CASE WHEN size(map_arr) > 0 THEN map_arr[0] ELSE NULL END) AS new_profiles
FROM (
    -- 拆解原map为键值对行
    SELECT explode(profiles) AS (map_key, map_arr)
    FROM your_table
) t
-- 按原表的唯一标识分组，确保每个原行的键值对重新聚合为map
GROUP BY your_primary_key;

• explode(profiles)：将原map拆分为多行，每行对应一个键和数组值
• CASE语句：处理空数组场景，返回NULL
• collect_map：将处理后的键值对重新聚合为map

验证结果 #

对示例数据执行上述语句后，将得到目标结果：

new_profiles     
{1: 1}
{2: 3}
{3: 10}
{4: NULL}

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