SnowFlake中Variant字段JSON数组转常规列的查询方案

假设你的表名为my_table，存储JSON的variant字段名为json_data，JSON数组结构类似：

{"custom_attrs": [{"name": "attr1", "value": "val1"}, {"name": "attr2", "value": "val2"}]}

已知所有属性名（比如attr1、attr2、attr3），可以用以下SQL实现转换，无需复杂脚本：

SELECT
    -- 保留原表的主键或其他需要的字段，这里以id为例
    t.id,
    -- 按属性名匹配提取值，替换成你实际的属性名和类型
    MAX(CASE WHEN f.value:name::STRING = 'attr1' THEN f.value:value::STRING END) AS attr1,
    MAX(CASE WHEN f.value:name::STRING = 'attr2' THEN f.value:value::INT END) AS attr2,
    MAX(CASE WHEN f.value:name::STRING = 'attr3' THEN f.value:value::DATE END) AS attr3
FROM my_table t
-- 展开variant字段中的自定义属性数组，PATH根据你的JSON结构调整，比如数组在根节点就写t.json_data
LATERAL FLATTEN(input => t.json_data:custom_attrs) f
-- 按原表的唯一标识分组，合并同一行的属性
GROUP BY t.id

关键部分说明 #

• LATERAL FLATTEN：把JSON数组拆成单独的行，每个数组元素对应一行，关联原表的记录。
• CASE 判断：通过属性名（而非数组索引）匹配取值，完全不受数组内属性顺序影响。
• MAX 聚合：因为拆分后同一原行的每个属性会生成多行，分组后用聚合函数把同一属性的唯一值合并成一列（用MIN效果相同）。
• 类型转换：::STRING/::INT/::DATE是把JSON值转成SnowFlake的对应数据类型，根据你的属性值类型调整即可。

如果你的JSON数组结构不同（比如每个元素直接是键值对而非包含name/value的对象），可以调整CASE里的匹配逻辑，比如JSON是{"attrs": {"attr1": "val1", "attr2": "val2"}}时，直接用json_data:attr1::STRING即可，但你的场景是数组，上面的方法完全适用。

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