Snowflake SQL：从带括号的字符串列表提取默认子品牌

要解决这个问题，核心是先将带嵌套转义的字符串转换为可解析的JSON格式，再通过Snowflake的JSON函数和FLATTEN方法提取目标数据，最优实现方案如下：

步骤拆解 #

• 清理转义字符串：源字段sub_brand_data是被外层双引号包裹、内部JSON双引号带转义的字符串，需先移除外层引号，再还原内部的转义双引号，得到标准JSON数组。
• 解析JSON：用PARSE_JSON函数将清理后的字符串转为JSON对象。
• 展开并过滤：通过LATERAL FLATTEN展开JSON数组，过滤出IsDefault = true的子项。
• 聚合结果：对每个brand_id聚合提取到的默认品牌名称，无匹配项时返回NULL。

完整SQL实现 #

WITH cleaned_data AS (
    SELECT 
        brand_id,
        -- 移除外层双引号，再将转义的双引号还原为正常双引号
        PARSE_JSON(REPLACE(TRIM(sub_brand_data, '"'), '\\"', '"')) AS sub_brand_json
    FROM sub_brand_extract
)
SELECT 
    brand_id,
    -- 聚合取默认品牌，无匹配则返回NULL
    ANY_VALUE(sb.value:Name::STRING) AS default_sub_brand
FROM cleaned_data
LEFT JOIN LATERAL FLATTEN(input => sub_brand_json) sb
WHERE sb.value:IsDefault::BOOLEAN = TRUE
GROUP BY brand_id
ORDER BY brand_id;

结果验证 #

执行后将得到预期结果：

补充说明 #

• 若确认每个brand_id最多仅一个默认子品牌，ANY_VALUE与MAX聚合效果一致；若存在多个默认项，可根据需求调整聚合逻辑。
• 若源数据转义规则有变化，可微调REPLACE参数适配，当前逻辑完全匹配你提供的测试数据格式。

内容的提问来源于stack exchange，提问作者parker.dillon