Snowflake使用lateral flatten解析JSON时如何同时提取CLUB与免费配送字段

• 提前过滤了非CLUB类型的拆分行：WHERE f3.value:type='CLUB'的硬过滤逻辑，会把所有FREE_DELIVERY类型的JSON拆分行直接排除，完全无法参与后续字段计算
• 缺少行转列聚合逻辑：lateral flatten操作会把单个JSON数组拆成多行，没有聚合的前提下，单条结果行只能承载一个类型的元素属性，没法同时返回两个独立字段

调整核心思路为：全量拆分JSON元素，通过条件聚合按业务主键归并，分别提取两类目标字段：

• 不提前过滤JSON元素的类型，保留所有拆分后的行参与计算
• 用CASE WHEN判断元素的type值，分别提取对应属性
• 按采购单关联维度做GROUP BY聚合，把拆分的多行合并回单条结果

调整后的可直接执行代码：

SELECT 
  LPAD(PURCHASE_ID,10, '0') AS PURCHASE_ID, 
  DELIVERY_ID, 
  PARTNER_ID,
  HAS_LABEL_FDEL,
  -- 提取CLUB类型对应的标签值，与原有验证逻辑完全一致
  MAX(CASE WHEN f3.value:type = 'CLUB' THEN f3.value:commonLabels[0]::VARCHAR END) AS CLUB,
  -- 提取FREE_DELIVERY类型的标识，可根据业务需要调整返回值
  MAX(CASE WHEN f3.value:type = 'FREE_DELIVERY' THEN TRUE END) AS IS_FREE_DELIVERY
FROM (
  SELECT DISTINCT 
    PURCHASE_ID, 
    DELIVERY_ID, 
    PARTNER_ID,
    HAS_LABEL_FDEL, 
    PARSE_JSON(PR_TRANSPORT) AS PR_TRANSPORT 
  FROM "DWHM_MALLGROUP"."SRC_ORACLEIWP"."PURCHASE_ANALYSIS_RULES" 
  -- 提前过滤空值，减少后续flatten处理的数据量
  WHERE PR_TRANSPORT IS NOT NULL
) AS PAR
LATERAL FLATTEN(input => PAR.PR_TRANSPORT) f3
-- 按业务主键分组，将拆分的多行聚合为单行
GROUP BY PURCHASE_ID, DELIVERY_ID, PARTNER_ID, HAS_LABEL_FDEL

若同一采购单下存在多个同类型规则，上述聚合逻辑会自动返回匹配到的首个值，不会因JSON数组多元素导致结果行数膨胀。

• CLUB字段：匹配type为CLUB的元素，取commonLabels数组的第一个字符串值，和原有已验证的提取逻辑完全对齐
• FREE_DELIVERY标识列：匹配type为FREE_DELIVERY的元素，可灵活调整返回值：
  • 仅需判断是否存在免费配送规则：返回布尔值TRUE/空值（代表不存在）
  • 需要关联具体规则：将CASE分支内的返回值替换为f3.value:ruleId即可获取对应规则ID

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