如何在SQL中对多列执行Unnest操作并保留Null值

我有一张表，其中id是unique not null的integer类型：

我需要将该表转换为物化视图，拆分site_names、site_addresses、industries、feis字段的管道分隔值，返回所有可能的组合，且保留字段中的Null值。预期输出的部分行示例如下：

我目前已实现的查询代码如下：

create materialized view site_data_split as
(
with Expanded2 as (
    select raw_site_data.id as id_fei, feis.feis
    from raw_site_data,
        unnest(string_to_array(raw_site_data.feis, '|')) feis
    ),
    Expanded3 as (
        select raw_site_data.id as id_name,
             site_names.site_names
        from raw_site_data,
            unnest(string_to_array(raw_site_data.site_names, '|')) site_names
    ),
    Expanded4 as (
        select raw_site_data.id as id_address,
            site_addresses.site_addresses
        from raw_site_data,
            unnest(string_to_array(raw_site_data.site_addresses, '|')) site_addresses
    ),
     Expanded5 as (
         select raw_site_data.id as id_industry,
                industries.industries
         from raw_site_data,
              unnest(string_to_array(raw_site_data.industries, '|')) industries
    )
select id_fei as site_id, feis as fei, site_names as site_name,
    site_addresses as site_address, industries as industry
from Expanded2, Expanded3, Expanded4, Expanded5
where Expanded2.id_fei = Expanded3.id_name
and Expanded3.id_name = Expanded4.id_address
and Expanded4.id_address = Expanded5.id_industry
);

但该查询的结果没有包含任何带Null值的行，请问如何调整语句才能在结果中保留Null值？

问题原因 #

unnest函数传入null值时会返回空结果集：比如某行的site_addresses为null，string_to_array(null, '|')会返回null，unnest(null)就没有输出，对应的CTE就不会包含该id的记录。你原代码中多个CTE的内连接（逗号连接加id匹配本质是内连接）会过滤掉所有只要有一个字段拆分无结果的id行，自然没有带null的结果。

修正后的代码 #

create materialized view site_data_split as
select 
    r.id as site_id,
    f.fei,
    sn.site_name,
    sa.site_address,
    ind.industry
from raw_site_data r
-- 对每个字段左连接 lateral 展开结果，无展开结果则返回null
left join lateral unnest(string_to_array(r.feis, '|')) f(fei) on true
left join lateral unnest(string_to_array(r.site_names, '|')) sn(site_name) on true
left join lateral unnest(string_to_array(r.site_addresses, '|')) sa(site_address) on true
left join lateral unnest(string_to_array(r.industries, '|')) ind(industry) on true;

逻辑说明 #

直接以原表的每行为基础，用LEFT JOIN LATERAL关联每个字段的拆分结果，on true表示只要原行存在，不管拆分是否有结果都保留原行，拆分无结果的字段自动填充null，同时天然生成同id下所有拆分值的笛卡尔积，完全符合需求，代码也更简洁易读。

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