AWS Redshift UNPIVOT半结构化数据需保留空值学生ID的问题

问题背景 #

现有AWS Redshift表students，包含student_id字段和存储联系方式的super类型字段contact_numbers。使用UNPIVOT语句展开contact_numbers时，会丢失contact_numbers为空对象（如student_id 6、7、8）的学生记录；尝试LEFT JOIN方式未解决，需实现查询结果包含所有学生ID，空联系方式对应的attr和val为NULL或空白。

表结构 #

create table students (
    student_id varchar(255),
    contact_numbers super   
);

数据集 #

student_id, contact_numbers
    1      {"mobile":111,"office":222}
    2      {"mobile":4444}
    3      {"mobile":555}
    5      {"mobile":8888}
    6      {}
    7      {}
    8      {}
    9      {"office":222}

原UNPIVOT查询（丢失空对象记录） #

SELECT student_id, attr, val FROM students tb,
UNPIVOT tb.contact_numbers AS val AT attr;

尝试的LEFT JOIN查询（未解决问题） #

select student_id, attr, val  from students tb
LEFT JOIN tb.contact_numbers contact ON true,
UNPIVOT tb.contact_numbers AS val AT attr;

期望结果 #

student_id, attr,   val
1           mobile  111
1           office  222
2           mobile  4444
3           mobile  555
5           mobile  8888
6           NULL    NULL
7           NULL    NULL
8           NULL    NULL
9           office  222

解决方案 #

Redshift中，当super对象为空时，UNPIVOT不会生成任何行，直接使用交叉连接（逗号分隔）会过滤掉这些学生记录。正确的做法是使用LEFT JOIN LATERAL关联UNPIVOT的子查询，确保主表所有记录都能保留，即使UNPIVOT无输出。

正确查询语句 #

SELECT 
    s.student_id,
    up.attr,
    up.val
FROM students s
LEFT JOIN LATERAL (
    -- 对每个学生的contact_numbers执行UNPIVOT
    SELECT attr, val 
    FROM UNPIVOT s.contact_numbers AS val AT attr
) up ON true;

补充：处理NULL的super字段 #

如果表中存在contact_numbers为NULL的情况（而非空对象{}），可以用COALESCE将NULL转换为包含一行空值的super对象，确保记录被保留：

SELECT 
    s.student_id,
    up.attr,
    up.val
FROM students s
LEFT JOIN LATERAL (
    SELECT attr, val 
    FROM UNPIVOT COALESCE(s.contact_numbers, '{"":null}'::super) AS val AT attr
) up ON true;

原理说明 #

• 原查询使用隐式交叉连接（逗号分隔），当UNPIVOT空对象时无返回行，导致对应学生记录被过滤。
• 尝试的LEFT JOIN写法错误，UNPIVOT的位置未被包含在关联子查询中，本质还是交叉连接，无法保留空对象的学生记录。
• LEFT JOIN LATERAL会对主表的每一行执行子查询，即使子查询无结果，主表行也会被保留，对应字段填充为NULL。

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