Snowflake关联维度表与数组值时报错Invalid Identifier F.INDEX求助

错误根本原因 #

你出现Invalid Identifier F.INDEX报错的核心原因是FROM子句的表引用顺序错误：

• SQL执行FROM子句的顺序是从左到右，你在json js join DIM_PRODUCTS c的关联条件里用到了f.index，但生成f结果集的LATERAL FLATTEN语句写在了这个JOIN的后面，此时数据库还没识别到f这个别名，自然无法调用f.index字段。
• 另外你用逗号分隔隐式连接的写法可读性差，建议改成显式的关联语法避免逻辑混乱。

修正后的完整代码 #

merge into PRODUCTDETAILS as a using (
    select 
        c.PRODUCTID,
        f.value as PRODUCTunit,
        js.PRODUCTDisplayname[f.index]::Varchar as PRODUCTDisplayname,
        js.PRODUCTCODE[f.index]::Varchar as PRODUCTCODE,
        js.PRODUCTquantity[f.index]::Varchar as PRODUCTquantity,
        js.PRODUCTDate[f.index]::Timestamp as PRODUCTDate
    from json js,
         LATERAL FLATTEN(js.PRODUCTunit, RECURSIVE=>true) f
    join DIM_PRODUCTS c 
      on js.PRODUCTDisplayname[f.index] = c.PRODUCTNAME
) as b 
on a.PRODUCTDate = b.PRODUCTDate
and a.PRODUCTCODE = b.PRODUCTCODE 
and a.PRODUCTID = b.PRODUCTID
and a.PRODUCTquantity = b.PRODUCTquantity
when not matched then insert (
    CUSTOMERID, PRODUCTID, PRODUCTunit, 
    PRODUCTDisplayname, PRODUCTCODE, PRODUCTquantity, PRODUCTDate
) 
values (
    b.CUSTOMERID, b.PRODUCTID, b.PRODUCTunit, 
    b.PRODUCTDisplayname, b.PRODUCTCODE, b.PRODUCTquantity, b.PRODUCTDate
)

补充注意事项 #

• 请确认PRODUCTDisplayname、PRODUCTCODE、PRODUCTquantity、PRODUCTDate这几个数组的长度和PRODUCTunit展开后的元素数量完全一致，否则会出现索引越界或者字段匹配错误的问题
• 修正代码里给所有数组字段加上了js.前缀明确来源，避免出现字段歧义报错
• INSERT部分的VALUES统一加上了b.前缀，避免数据库无法识别取值来源的报错

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