Snowflake中如何按前缀模糊匹配键名解析JSON字段？

针对你遇到的JSON字段键数量多、无法提前知晓，需要模糊匹配前缀键的需求，可以通过LATERAL FLATTEN展开JSON对象，再结合过滤条件实现，具体方案如下：

基础方案：提取符合条件的键值对 #

将JSON对象拆分为键值对行，过滤出前缀匹配的条目：

SELECT
    t.your_primary_key, -- 替换为表的主键/唯一标识字段
    f.key AS matched_key,
    f.value AS matched_value
FROM your_table t
-- 展开JSON对象为键值对行，若field1已是VARIANT类型，可去掉parse_json()
LATERAL FLATTEN(input => parse_json(t.field1), mode => 'OBJECT') f
-- 匹配以task_开头的键，可根据需求修改LIKE条件（如%keyname%匹配包含指定字符串的键）
WHERE f.key LIKE 'task_%';

进阶方案：将匹配结果重组为JSON对象 #

如果需要将每条原记录中符合条件的键值对重新聚合为一个JSON对象，使用OBJECT_AGG函数：

SELECT
    t.your_primary_key,
    -- 若无匹配项则返回空对象，否则聚合为新JSON
    COALESCE(OBJECT_AGG(f.key, f.value), OBJECT_CONSTRUCT()) AS matched_task_fields
FROM your_table t
LEFT JOIN LATERAL FLATTEN(input => parse_json(t.field1), mode => 'OBJECT') f
    ON f.key LIKE 'task_%'
GROUP BY t.your_primary_key;

关键说明 #

• LATERAL FLATTEN会将每个JSON对象的键值对拆分为单独的行，这是处理未知键集合的核心方法，避免了硬编码所有可能的键。
• 若你的JSON字段field1已经定义为VARIANT类型，无需调用parse_json()，直接使用input => t.field1即可。
• LEFT JOIN确保了即使没有匹配键的记录也会被保留，若只需要包含匹配键的记录，改用INNER JOIN即可。

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