Snowflake关联数据后展平数组并提取STORE类型token的问题

问题分析 #

你的查询已经实现了筛选每个merchant_token的最新记录，并过滤出aliases数组包含STORE类型对象的行，但缺少提取对应token的步骤。核心问题是未在SELECT子句中引用扁平化后对象的token字段，同时逻辑顺序可以调整以提升效率。

修正后的查询语句 #

WITH unique_merchants AS (
  SELECT 
    updated_at, 
    aliases, 
    merchant_token, 
    identity_token, 
    ROW_NUMBER() OVER (
      PARTITION BY merchant_token 
      ORDER BY updated_at DESC
    ) AS row_number 
  FROM table.PUBLIC.business_info
) 
SELECT 
  um.identity_token, 
  um.merchant_token,
  obj.value:token::STRING AS store_token  -- 提取STORE类型的token并转为字符串类型
FROM unique_merchants um
INNER JOIN table.PUBLIC.stores s 
  ON s.parent_token = um.merchant_token,
LATERAL FLATTEN(input => um.aliases) AS obj
WHERE um.row_number = 1  -- 先过滤最新记录，减少后续处理的数据量
  AND obj.value:type::STRING = 'STORE';  -- 显式类型转换，避免隐式匹配问题

关键调整说明 #

• 提取目标字段：通过obj.value:token::STRING直接获取扁平化后STORE对象的token值，显式转换为字符串类型确保结果格式符合预期。
• 优化执行顺序：先通过row_number = 1筛选出每个商户的最新记录，再进行数组扁平化和类型筛选，减少需要处理的数据量，提升查询效率。
• 避免类型歧义：将value:type转为字符串后再与'STORE'比较，避免Snowflake因JSON类型与字符串隐式转换可能导致的匹配异常。

可选扩展 #

如果每个商户的aliases数组中存在多个STORE类型对象，当前查询会返回所有对应token的行；若仅需保留第一个匹配项，可以在LATERAL FLATTEN中添加LIMIT => 1参数。

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