Snowflake中Flatten函数使用问题：提取JSON的performance路径数据

正确SQL语句 #

SELECT
    f1.key::DATE AS date,
    f2.value:ad::STRING AS ad,
    f2.value:ad_impressions::INT AS impressions
FROM <table>,
     LATERAL FLATTEN(input => CLMN:data:performance) f1,
     LATERAL FLATTEN(input => f1.value:ad_performances) f2;

原SQL的问题分析 #

• 路径错误：JSON中performance节点嵌套在data节点下，原SQL直接用CLMN:performances无法定位到目标数据，需修正为CLMN:data:performance
• 缺少层级扁平化：performance下的日期对应一个包含ad_performances数组的对象，仅扁平化第一层无法触达数组内的广告数据，需新增一次LATERAL FLATTEN处理数组
• 字段名与路径错误：JSON中曝光量字段是ad_impressions而非impressions，且广告数据需从第二层扁平化的结果中提取，原SQL的字段路径完全错误
• 重复别名：原SQL中两个字段使用了相同的performances_array别名，会导致语法错误

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