在Snowflake中基于关联表数据转换数组列并新增MAP字段

需求说明 #

现有两张Snowflake表：
Table_A（存储嵌套数组格式的关联数据）：

Table_B（存储BUILD_ID与NAME的映射关系）：

需要给Table_A新增MAP列，将EMP数组中每个子数组的第一个元素（BUILD_ID）替换为Table_B中对应的NAME值，最终得到如下结果：

实现SQL语句 #

SELECT 
    a.ID,
    a.EMP,
    ARRAY_AGG(ARRAY_CONCAT([b.NAME], ARRAY_SLICE(e.value, 1, 2))) AS MAP
FROM Table_A a
, LATERAL FLATTEN(a.EMP) e
LEFT JOIN Table_B b ON e.value[0]::INT = b.BUILD_ID
GROUP BY a.ID, a.EMP
ORDER BY a.ID;

语句解释 #

1. LATERAL FLATTEN(a.EMP)：将Table_A中EMP列的嵌套数组展开，每一行对应原数组中的一个子数组，e.value即为单个子数组。
2. LEFT JOIN Table_B b ON e.value[0]::INT = b.BUILD_ID：把子数组第一个元素转为INT类型，关联Table_B匹配对应的NAME值。
3. ARRAY_CONCAT([b.NAME], ARRAY_SLICE(e.value, 1, 2))：将NAME作为新子数组的首元素，拼接原数组中从索引1开始的数值部分，生成替换后的子数组。
4. ARRAY_AGG(...)：按原表ID和EMP列分组，把替换后的子数组重新聚合为嵌套数组，得到最终的MAP列。

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