Snowflake解析JSON数组：Redshift SQL转Snowflake SQL报错求解

问题背景 #

• 业务需要处理A/B/n测试数据，从experiments表的splits字段（JSON数组格式）中提取每个分组的id、split_type、weight字段
• 原Redshift SQL通过生成连续索引笛卡尔积遍历数组，迁移到Snowflake时出现invalid identifier 'N.N'报错

原Redshift可运行SQL #

SELECT JSON_EXTRACT_PATH_TEXT(json_extract_array_element_text (e.splits,n.n),'split_type') types
     , JSON_EXTRACT_PATH_TEXT(json_extract_array_element_text (e.splits,n.n),'weight') as weight
FROM experiments e, (SELECT  (p0.n + p1.n*2 + p2.n * POWER(2,2) + p3.n * POWER(2,3) + p4.n * POWER(2,4) + p5.n * POWER(2,5)
                              + p6.n * POWER(2,6) + p7.n * POWER(2,7) + p8.n * POWER(2,8) + p9.n * POWER(2,9))::int as n
                     FROM
                         (SELECT 0 as n UNION SELECT 1) p0,
                         (SELECT 0 as n UNION SELECT 1) p1,
                         (SELECT 0 as n UNION SELECT 1) p2,
                         (SELECT 0 as n UNION SELECT 1) p3,
                         (SELECT 0 as n UNION SELECT 1) p4,
                         (SELECT 0 as n UNION SELECT 1) p5,
                         (SELECT 0 as n UNION SELECT 1) p6,
                         (SELECT 0 as n UNION SELECT 1) p7,
                         (SELECT 0 as n UNION SELECT 1) p8,
                         (SELECT 0 as n UNION SELECT 1) p9
                     Order by 1
                     ) n
WHERE types <> ''
AND weight <> ''

最优Snowflake实现方案 #

Snowflake内置FLATTEN表函数专门用于数组打平，无需手动生成索引序列，性能更高且支持任意长度的JSON数组，完全适配A/B/n测试场景，最终输出和需求完全匹配：

SELECT
    e.exp_ID,
    f.value:id::INT AS ID,
    f.value:split_type::VARCHAR AS types,
    f.value:weight::FLOAT AS weight
FROM experiments e,
LATERAL FLATTEN(input => PARSE_JSON(e.splits)) f
WHERE
    types <> ''
    AND weight IS NOT NULL;

方案说明 #

• PARSE_JSON(e.splits)：将字符串格式的splits字段转换为Snowflake可识别的JSON对象
• LATERAL FLATTEN：将JSON数组按元素拆分为多行，每个元素对应一行，f.value即为数组中的单个JSON对象
• 直接用:语法提取JSON属性，后跟::类型完成类型转换，得到结构化输出

原写法报错原因 #

原笛卡尔积生成索引的写法在Snowflake中也可运行，报错是因为Snowflake默认标识符大写，子查询返回的列名如果是大写N，小写n.n无法识别，需要加双引号引用"n"."n"，但完全不推荐这种冗余写法，FLATTEN是Snowflake处理数组的标准方案。

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