如何向Snowflake的OBJECT_CONSTRUCT函数动态添加字段

我在Snowflake中有一张大表，包含大量以RAW_为前缀的字段。为简化表结构，希望将所有这类字段合并为名为RAW_KEY_VALUE的字段，存储为键值对象。

原本想用OBJECT_CONSTRUCT函数，但该函数需要手动硬编码字段名。由于这类字段有90-120个，且经常变动，急需能动态识别并转换的方案（存储过程暂作为备选）。

示例数据 #

create or replace table reviews(name varchar(50), acting_rating int, raw_comments varchar(50), raw_rating int, raw_co varchar(50));

insert into reviews values
    ('abc', 4, NULL, 1, 'NO'),
    ('xyz', 3, 'some', 1, 'haha'),
    ('lmn', 1, 'what', 4, NULL);

期望的手动实现效果 #

select 
name , 
acting_rating ,
object_construct_keep_null ('raw_comments',raw_comments,'raw_rating',raw_rating,'raw_co',raw_co) as RAW_KEY_VALUE
from reviews;

可行解决方案 #

方案1：利用元数据视图动态生成SQL #

通过查询Snowflake的元数据视图INFORMATION_SCHEMA.COLUMNS，自动获取所有RAW_前缀的字段，拼接成OBJECT_CONSTRUCT_KEEP_NULL的参数：

-- 生成动态查询语句
SELECT 'SELECT name, acting_rating, OBJECT_CONSTRUCT_KEEP_NULL(' || STRING_AGG('''' || COLUMN_NAME || ''', ' || COLUMN_NAME, ', ') || ') AS RAW_KEY_VALUE FROM reviews;' AS dynamic_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'REVIEWS' AND COLUMN_NAME LIKE 'RAW_%';

执行该查询后，会得到完整的目标SELECT语句，直接运行生成的语句即可。后续字段变动时，重新执行这段生成代码即可更新查询逻辑。

方案2：使用OBJECT_AGG结合FLATTEN实现动态转换 #

这种方法无需手动生成SQL，每次执行都会自动识别当前表中所有RAW_前缀的字段：

SELECT 
    name,
    acting_rating,
    OBJECT_AGG(key, value) AS RAW_KEY_VALUE
FROM reviews
    , LATERAL FLATTEN(INPUT => OBJECT_CONSTRUCT_KEEP_NULL(*), PATH => 'RAW_%')
GROUP BY name, acting_rating;

原理说明：

1. OBJECT_CONSTRUCT_KEEP_NULL(*)将整行数据转换为一个包含所有字段的对象；
2. LATERAL FLATTEN(..., PATH => 'RAW_%')过滤并展开所有键名以RAW_开头的键值对；
3. OBJECT_AGG(key, value)将展开后的键值对重新聚合为一个对象，即RAW_KEY_VALUE。

方案对比 #

• 方案1生成静态SQL，执行效率更高，适合字段变动不频繁的场景；
• 方案2是动态查询，无需维护字段列表，灵活性更强，适合字段经常变动的场景。

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