SQL使用collect_list(group_map['key'])时如何保留重复ID行

你的代码存在3个核心错误，导致无法得到预期结果：

• CTE语法顺序写反：标准CTE写法是WITH CTE名称 AS (子查询逻辑)，你写的WITH AS my_map_select不符合语法规则
• 表别名引用错误：FROM子句给表取的别名是a，GROUP BY中却引用了不存在的b.cntrl、b.var
• 函数逻辑误用：collect_list是聚合函数，作用是将同分组内的多行值合并为数组，你的需求是保留独立行、不合并同ID记录，根本不需要使用聚合函数，也不需要加GROUP BY子句。直接逐行从map结构中提取对应key的值即可，不存在的key会默认返回空值，完全匹配你的预期。

WITH my_map_select AS (
    -- 此处替换为你原my_map_select的内部查询逻辑
    ...
)
SELECT 
    a.id,
    a.group_map['B'] AS new_b,
    a.group_map['A'] AS new_A,
    a.cntrl,
    a.var
FROM my_map_select AS a
-- 按ID排序，同ID下可按需调整排序字段，保证结果顺序符合要求
ORDER BY a.id, a.var

• 单条记录的group_map仅包含对应存在的key，提取不存在的key时会返回空值：group_map为{"B": true}、var=634的行，new_b返回true、new_A返回空；group_map为{"A": true}、var=79的行，new_A返回true、new_b返回空
• 同ID的多条记录不会被合并，完全保留独立行形态
• 最终结果按ID排序，满足排序要求

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