Snowflake嵌套JSON数组：ISRC与关联样式查询方案咨询

核心思路 #

用Snowflake的LATERAL FLATTEN函数分别展开productCodes和styles两个嵌套数组，再通过交叉关联实现每个ISRC与所在行的所有样式一一匹配。

具体SQL实现 #

假设你的表名为your_table，执行以下查询：

SELECT
    -- 提取ISRC值
    pc.value:value::STRING AS isrc_code,
    -- 提取样式ID和权重
    s.value:styleId::STRING AS style_id,
    s.value:weight::FLOAT AS style_weight,
    -- 可选：提取艺术家信息
    v.value:artist:artistID::STRING AS artist_id,
    v.value:artist:artistName::STRING AS artist_name
FROM
    your_table v,
    -- 展开productCodes数组，过滤出type为ISRC的元素
    LATERAL FLATTEN(input => v.value:descriptors:productCodes) pc
    -- 展开styles数组，与ISRC交叉关联
    CROSS JOIN LATERAL FLATTEN(input => v.value:descriptors:styles) s
WHERE
    pc.value:type::STRING = 'ISRC'

关键说明 #

• LATERAL FLATTEN：Snowflake专门用于展开嵌套数组的函数，LATERAL关键字确保每一行的数组展开后与原行数据保持关联。
• 字段路径调整：如果你的JSON结构中ISRC值的字段不是value，或者样式ID字段不是styleId，直接修改对应的路径即可，比如s.value:id::STRING。
• 空数组处理：如果需要兼容无样式的行，把CROSS JOIN换成LEFT JOIN，此时无样式的ISRC对应的style字段会返回NULL。

示例效果 #

假设某一行JSON数据为：

{
  "artist": {"artistID": "A123", "artistName": "Taylor Swift"},
  "descriptors": {
    "styles": [{"styleId": "S001", "weight": 0.8}, {"styleId": "S002", "weight": 0.5}],
    "duration": 240,
    "productCodes": [{"type": "ISRC", "value": "US-T12-23-00001"}, {"type": "ISRC", "value": "US-T12-23-00002"}]
  }
}

执行查询后会返回4行结果，每个ISRC分别对应两个样式：

额外提示 #

如果存在同一ISRC+样式组合重复的情况，可在SELECT后添加DISTINCT关键字去重。

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