SQL中UNNEST数组后UNION ALL两CTE出现类型不匹配如何解决

根因说明 #

你遇到的UNION ALL报错核心是两个待合并的结果集字段顺序、字段类型不匹配：

• 第一个CTE unsubscribe_logs 的字段顺序中，domain 字段后紧跟的是STRING类型的 sub_engagement_type，之后才是job_id
• 第二个CTE unsubscribe_logs_part_two 原生字段中没有sub_engagement_type，你用* EXCEPT写法拼接UNNEST追加的字段时，会导致两个结果集的字段顺序错位，触发类型校验失败。

修复方案 #

直接弃用模糊的*查询写法，UNION ALL的两个子查询都显式声明字段，同时对UNNEST生成的字段做显式类型对齐即可，修改后代码如下：

WITH unsubscribe_logs AS (
        SELECT 
            Subscriber_Key AS cust_sf_id,
            'Email Unsubscribe' AS engagement_type,
            Email_Att1 AS campaign_code,
            PARSE_TIMESTAMP('%m/%d/%Y %I:%M:%S %p', Insert_Local_DT) AS engagement_datetime,
            Email_Name AS details,
            CAST(NULL as STRING) AS url,
            CAST(NULL as STRING) AS link_name,
            CAST(NULL as STRING) AS domain,
            unsubscribe_type AS sub_engagement_type,
            JobID AS job_id,
            CAST(NULL as INT64) AS list_id,
            CAST(NULL as INT64) AS batch_id,
            source_timestamp AS source_timestamp,
            ingestion_timestamp AS ingestion_timestamp
        FROM `dl_salesforce_marketingcloud_uk.behaviour_log_salesforce_daily_unsubscribe_v1`
        WHERE 1=1
            AND CAST(source_timestamp AS date) >= IFNULL(filter_date,"1990-01-01")
),
unsubscribe_logs_part_two AS (
        SELECT 
            Subscriber_Key AS cust_sf_id,
            'Email Unsubscribe' AS engagement_type,
            Email_Att1 AS campaign_code,
            PARSE_TIMESTAMP('%m/%d/%Y %I:%M:%S %p', Insert_Local_DT) AS engagement_datetime,
            Email_Name AS details,
            CAST(NULL as STRING) AS url,
            CAST(NULL as STRING) AS link_name,
            CAST(NULL as STRING) AS domain,
            JobID AS job_id,
            CAST(NULL as INT64) AS list_id,
            CAST(NULL as INT64) AS batch_id,
            source_timestamp AS source_timestamp,
            ingestion_timestamp AS ingestion_timestamp,
            split(unsubscribe_type, " and ") AS unsub_type_parts
        From `pah_andrew_test.unsubscribe_testing`
        WHERE 1=1
            AND unsubscribe_type != 'Marketing' 
            AND unsubscribe_type != 'Reminder' 
            AND unsubscribe_type != ''
            AND CAST(source_timestamp AS date) >= IFNULL(filter_date,"1990-01-01")
),
all_salesforce_engagement as (
    SELECT
        NULLIF(cust_sf_id, '') as cust_sf_id,
        engagement_type as engagement_type,
        NULLIF(campaign_code, '') as campaign_code,
        engagement_datetime as engagement_datetime,
        NULLIF(details, '') as details,
        NULLIF(url, '') as url,
        NULLIF(link_name, '') as link_name,
        NULLIF(domain, '') as domain,
        NULLIF(TRIM(sub_engagement_type), '') as sub_engagement_type,
        CAST(job_id AS STRING) as job_id,
        CAST(list_id AS STRING) as list_id,
        CAST(batch_id AS STRING) as batch_id,
        source_timestamp as source_timestamp,
        ingestion_timestamp as ingestion_timestamp
    FROM (
        -- 显式列字段保证顺序完全对齐
        SELECT 
            cust_sf_id,
            engagement_type,
            campaign_code,
            engagement_datetime,
            details,
            url,
            link_name,
            domain,
            sub_engagement_type,
            job_id,
            list_id,
            batch_id,
            source_timestamp,
            ingestion_timestamp
        FROM unsubscribe_logs
        
        UNION ALL
        
        SELECT 
            cust_sf_id,
            engagement_type,
            campaign_code,
            engagement_datetime,
            details,
            url,
            link_name,
            domain,
            -- 显式转STRING保证和上半部分字段类型一致
            CAST(sub_engagement_type AS STRING) AS sub_engagement_type,
            job_id,
            list_id,
            batch_id,
            source_timestamp,
            ingestion_timestamp
        FROM unsubscribe_logs_part_two 
        LEFT JOIN unnest(unsub_type_parts) as sub_engagement_type
    )
)

额外注意事项 #

• UNION ALL要求上下两个结果集的字段数量、顺序、类型三者完全一致，任何一处不匹配都会触发报错，显式列字段可以100%避免顺序错位问题
• 如果UNNEST后出现空值，显式CAST转换也会保证空值类型和上游对齐，不会出现类型不匹配问题

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