Snowflake查询提取customizedFields字段值返回NULL求助

问题根源 #

你的customizedFields字段是字符串类型的JSON数组，而非Snowflake原生支持的VARIANT类型。原查询直接对字符串执行FLATTEN，会把字符串拆分为单个字符，导致f.value是单个字符，自然无法通过GET_PATH提取title和value，最终返回NULL。

修正后的查询 #

SELECT
    S.src:auditoriumName::STRING AS auditoriumName,
    S.src:country::STRING AS country,
    -- 保留原字段（可选）
    S.src:customizedFields::STRING AS customizedFields_title,
    -- 提取并清理title：先转VARIANT，再取title，最后移除HTML标签和引号
    REGEXP_REPLACE(f.value:title::STRING, '["<>/p]', '') AS customizedFields_title1,
    -- 提取并清理value
    f.value:value::STRING AS customizedFields_value,
    TO_TIMESTAMP(S.src:date::STRING) AS date 
FROM Source_Data AS S,
-- 先将字符串类型的JSON数组转为VARIANT，再执行FLATTEN
LATERAL FLATTEN(input => S.src:customizedFields::VARIANT) AS f

关键修改点 #

• 类型转换：在FLATTEN前将customizedFields从STRING转为VARIANT，让Snowflake正确识别为JSON数组。
• 简化字段提取：直接用f.value:title::STRING提取JSON属性，比GET_PATH更简洁直观。
• 正则替换清理：用REGEXP_REPLACE一次性移除所有不需要的字符（引号、HTML标签），替代多次嵌套的REPLACE。

验证说明 #

针对你提供的示例值[{"title":"<p>Yes, please, I would like to receive the newsletter</p>","value":"true"}]，修正后的查询会输出：

• customizedFields_title1：Yes, please, I would like to receive the newsletter
• customizedFields_value：true

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