如何在SnowSQL查询中过滤JSON对象（排除指定键）

针对你需要在查询JSON时排除特定键（如product下的commit-timestamp）且兼容多种JSON结构的需求，Snowflake提供了几种实用的方法：

方法1：使用OBJECT_REMOVE函数（推荐，简洁高效） #

OBJECT_REMOVE是Snowflake专门用于移除JSON对象指定键的函数，支持直接处理嵌套结构，无需提前知晓所有其他键。

场景1：目标键在顶层对象中 #

如果json['product-list']本身就是包含commit-timestamp的对象，直接调用函数移除即可：

SELECT OBJECT_REMOVE(json['product-list'], 'commit-timestamp') AS filtered_product_list
FROM some_table;

场景2：目标键在数组的元素对象中 #

如果product-list是数组，每个元素是包含commit-timestamp的product对象，结合ARRAY_TRANSFORM批量处理数组元素：

SELECT ARRAY_TRANSFORM(
    json['product-list'],
    element -> OBJECT_REMOVE(element, 'commit-timestamp')
) AS filtered_product_list
FROM some_table;

场景3：深层嵌套的键 #

如果commit-timestamp在多层嵌套下（如product-list -> product -> meta -> commit-timestamp），可以嵌套使用OBJECT_REMOVE，同时保留其他未知键的话可结合动态构造：

SELECT OBJECT_CONSTRUCT(
    'product', OBJECT_AGG(sub_key, sub_value)
    FROM LATERAL FLATTEN(INPUT => json['product-list']['product'], MODE => 'OBJECT')
    WHERE sub_key != 'commit-timestamp'
) AS filtered_product_list
FROM some_table;

方法2：动态构造对象（兼容未知键结构） #

如果JSON结构不固定，无法提前列举需要保留的键，可通过FLATTEN展开所有键值对，过滤掉目标键后重新聚合为JSON对象：

处理顶层对象 #

SELECT OBJECT_AGG(key, value) AS filtered_product_list
FROM some_table,
LATERAL FLATTEN(INPUT => json['product-list'], MODE => 'OBJECT')
WHERE key != 'commit-timestamp';

处理嵌套对象 #

如果需要移除嵌套在product子对象中的commit-timestamp，同时保留其他所有顶层和子层键：

SELECT OBJECT_AGG(
    top_key,
    CASE
        WHEN top_key = 'product' THEN (
            SELECT OBJECT_AGG(sub_key, sub_value)
            FROM LATERAL FLATTEN(INPUT => top_value, MODE => 'OBJECT')
            WHERE sub_key != 'commit-timestamp'
        )
        ELSE top_value
    END
) AS filtered_product_list
FROM some_table,
LATERAL FLATTEN(INPUT => json['product-list'], MODE => 'OBJECT') AS top_level(top_key, top_value);

注意事项 #

• OBJECT_REMOVE会返回移除指定键后的新JSON对象，原数据不会被修改；
• 若目标键不存在，函数会直接返回原对象，不会报错；
• 处理数组时，ARRAY_TRANSFORM会遍历每个元素并应用移除逻辑，确保数组中所有对象都排除指定键。

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