SQL语法错误排查：拆分Email生成ldap并关联两张表

错误分析 #

1. 嵌套字段访问违规：t.user.email触发语法错误，核心原因是Table1的user字段为数组（或嵌套集合）类型，SQL引擎要求必须先通过UNNEST展开数组，才能访问内部的email属性，直接链式访问会被判定为语法错误。
2. 主查询表引用错误：主查询中使用t.status_update_time_millis，但主查询的FROM子句里并没有t表（仅包含子查询别名sub和Table2的别名v），运行时会提示表t不存在。

修正后的SQL语句 #

场景1：user字段为数组类型 #

SELECT 
    sub.ldap,
    sub.status_update_time_millis,
    v.bitrix_lead,
    v.current_shift
FROM 
    (
        SELECT 
            t.status_update_time_millis,
            u.email,
            SUBSTRING(u.email, 1, POSITION('@' IN u.email) - 1) AS ldap
        FROM 
            Table1 AS t
            -- 展开user数组，获取单个用户对象
            CROSS JOIN UNNEST(t.user) AS u
        WHERE 
            t.vendor IN ('ICO_BS')
            AND t.status IN ('ACTIVE')
            -- 过滤无@符号的无效邮箱，避免SUBSTRING参数异常
            AND POSITION('@' IN u.email) > 0
    ) AS sub
JOIN 
    Table2 AS v ON sub.ldap = v.ldap

场景2：user字段为单个嵌套对象（如STRUCT类型） #

若user是单个嵌套对象而非数组，仅需调整字段引用方式（部分SQL方言需用->或['email']替代.，具体取决于引擎），同时修复表引用问题：

SELECT 
    sub.ldap,
    sub.status_update_time_millis,
    v.bitrix_lead,
    v.current_shift
FROM 
    (
        SELECT 
            t.status_update_time_millis,
            t.user.email,
            SUBSTRING(t.user.email, 1, POSITION('@' IN t.user.email) - 1) AS ldap
        FROM 
            Table1 AS t
        WHERE 
            t.vendor IN ('ICO_BS')
            AND t.status IN ('ACTIVE')
            AND POSITION('@' IN t.user.email) > 0
    ) AS sub
JOIN 
    Table2 AS v ON sub.ldap = v.ldap

关键调整说明 #

• UNNEST数组处理：针对数组类型的user字段，用CROSS JOIN UNNEST(t.user) AS u将数组展开为行，才能正常访问email属性。
• 修复字段引用：将原主查询中的t.status_update_time_millis移至子查询，主查询通过sub.status_update_time_millis获取该字段。
• 防错过滤：添加POSITION('@' IN u.email) > 0条件，避免因无效邮箱导致SUBSTRING的第三个参数为负数，引发新的错误。

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