如何在Snowflake中更新Variant列指定对象的特定值

需求说明：

• 表database.tmp.table1包含Variant类型列details，需修改其中JSON对象的channel属性
• 将channel值为"phone"或"cell"的替换为"mobile"，其他属性（如result）保持不变
• 避免解构重组整个JSON对象，无需使用UDF

示例数据创建语句 #

create or replace temporary table database.tmp.table1 as (
 select 1 as order_id, 
    '{
      "channel": "phone",
      "result": "approved" 
    }' as details union all
 select 2 as order_id, 
    '{
      "channel": "cell",
      "result": "approved" 
    }' as details union all
 select 3 as order_id, 
    '{
      "channel": "store",
      "result": "phone"
    }' as details
);

解决方案 #

使用Snowflake内置函数OBJECT_INSERT直接覆盖目标属性值，结合条件判断实现精准修改，无需删除原有属性再插入：

UPDATE database.tmp.table1
SET details = OBJECT_INSERT(
    details,
    'channel',
    -- 判断channel值，符合条件则替换为"mobile"，否则保留原值
    IFF(STRIP_JSON_VALUE(details:channel) IN ('phone', 'cell'), '"mobile"', details:channel),
    TRUE -- 设置为TRUE允许覆盖已存在的channel键
)
-- 仅更新需要修改的行，提升执行效率
WHERE STRIP_JSON_VALUE(details:channel) IN ('phone', 'cell');

代码说明 #

1. STRIP_JSON_VALUE(details:channel)：提取JSON属性的字符串值并去掉外层双引号，方便直接与字符串常量对比
2. IFF(condition, true_value, false_value)：条件判断函数，符合条件则返回"mobile"，否则保留原channel值
3. OBJECT_INSERT(..., TRUE)：第三个参数设为TRUE时，若目标键已存在则直接覆盖其值，无需先删除再插入，完美保留JSON对象中的其他属性

验证结果 #

执行更新后查询表数据：

SELECT order_id, details FROM database.tmp.table1;

预期输出：

ORDER_ID | DETAILS
1        | {"channel": "mobile", "result": "approved"}
2        | {"channel": "mobile", "result": "approved"}
3        | {"channel": "store", "result": "phone"}

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