Snowflake中如何通过MERGE语句实现JSON数据的动态更新（避免列置空）

问题核心是：源JSON仅包含主键和需要更新的字段，直接在MERGE的UPDATE子句中赋值所有列会导致未更新的字段被NULL覆盖目标表原有值。可以通过条件赋值或动态SQL两种方式解决。

方法一：使用COALESCE条件赋值（适用于字段固定场景） #

通过COALESCE函数优先取源数据的非NULL值，若源数据字段为NULL（即JSON未提供该字段），则保留目标表的原有值。

修改后的MERGE语句如下：

-- 重新创建示例表（可选，用于验证）
create or replace table source_data as 
select parse_json(COLUMN1)::variant datacol
from values 
 ('{"metadata":{"OperationName":"UPDATE"},"data":{"id":"1234","status":"Active"}}'),
 ('{"metadata":{"OperationName":"UPDATE"},"data":{"id":"1235","name":"Johny"}}');

create or replace table employee_destination as
select column1::text as id,
       column2::text as name,
       column3::text as status
from values 
 ('1234','John','Inactive'),
 ('1235','Jack','Active');

-- 修正后的MERGE语句
MERGE into employee_destination as Target 
using (
    select 
        datacol:data:id::text as id,
        datacol:data:status::text as status,
        datacol:data:name::text as name,
        datacol:metadata:OperationName::text as operation_name 
    from SOURCE_DATA
) AS Source
ON Target.id = Source.id
when matched AND Source.operation_name = 'UPDATE'
THEN update set 
    Target.name = COALESCE(Source.name, Target.name),
    Target.status = COALESCE(Source.status, Target.status);

执行后验证目标表数据：

SELECT * FROM employee_destination;

结果符合预期：

方法二：动态生成UPDATE语句（适用于字段较多或不固定场景） #

当目标表字段数量多或字段不固定时，可以通过查询系统元数据动态生成更新子句，避免手动编写每个字段的条件赋值。

-- 1. 生成动态更新的SET子句
SET update_clause = (
    SELECT LISTAGG(col_name || ' = COALESCE(Source.' || col_name || ', Target.' || col_name || ')', ', ')
    FROM (
        -- 获取目标表中除主键ID外的所有字段
        SELECT column_name as col_name
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'EMPLOYEE_DESTINATION'
          AND column_name != 'ID'
          AND table_schema = CURRENT_SCHEMA()
    )
);

-- 2. 拼接完整的MERGE SQL语句
SET merge_sql = '
MERGE into employee_destination as Target 
using (
    select 
        datacol:data:id::text as id,
        datacol:data:status::text as status,
        datacol:data:name::text as name,
        datacol:metadata:OperationName::text as operation_name 
    from SOURCE_DATA
) AS Source
ON Target.id = Source.id
when matched AND Source.operation_name = ''UPDATE''
THEN update set ' || $update_clause || ';';

-- 3. 执行动态SQL
EXECUTE IMMEDIATE $merge_sql;

说明 #

• 动态SQL通过INFORMATION_SCHEMA.COLUMNS获取目标表的字段列表，自动生成每个字段的COALESCE赋值逻辑。
• 若源JSON新增了字段，只需确保目标表存在对应字段，动态SQL即可自动适配，无需手动修改MERGE语句。

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