SQL Server中SCD Type1维度表的JSON字段合并问题求助

需求说明 #

基于Userid主键合并多文件数据，采用SCD Type1维度处理规则：

• 普通字段：新数据非空则覆盖旧值，空值保留原数据
• Custom_value JSON字段：旧字段保留、新字段添加、重复字段以新值覆盖

实现步骤 #

1. 导入待更新数据到临时表 #

先将File2的导入数据存入临时表（避免直接操作原表，同时方便后续关联更新）：

CREATE TABLE #File2Data (
    Userid INT,
    username VARCHAR(50),
    addresss VARCHAR(100),
    Service_Type VARCHAR(50),
    User_Type VARCHAR(50),
    Custom_value NVARCHAR(MAX)
);

-- 示例插入File2数据（实际可通过BULK INSERT等方式导入文件）
INSERT INTO #File2Data (Userid, username, addresss, Service_Type, User_Type, Custom_value)
VALUES (234, NULL, NULL, 'Customer', 'DVV', '{"Birthdate":"19-09-1995","State":"Karnataka"}');

2. 执行合并更新语句 #

通过UPDATE结合JSON解析与合并逻辑，完成所有字段的更新：

UPDATE ud
SET 
    -- 普通字段：新值非空则覆盖，否则保留原数据
    username = ISNULL(f2.username, ud.username),
    addresss = ISNULL(f2.addresss, ud.addresss),
    Service_Type = ISNULL(f2.Service_Type, ud.Service_Type),
    User_Type = ISNULL(f2.User_Type, ud.User_Type),
    -- JSON字段合并：旧字段保留、新字段添加、重复字段取新值
    Custom_value = (
        SELECT 
            COALESCE(f2j.[key], udj.[key]) AS [key],
            COALESCE(f2j.value, udj.value) AS value
        FROM 
            OPENJSON(ud.Custom_value) udj
        FULL JOIN 
            OPENJSON(f2.Custom_value) f2j ON udj.[key] = f2j.[key]
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )
FROM 
    User_Dimension ud
JOIN 
    #File2Data f2 ON ud.Userid = f2.Userid;

逻辑说明 #

• 普通字段更新：用ISNULL(f2.字段, ud.字段)实现SCD Type1的覆盖规则，仅当新数据字段非空时替换旧值。
• JSON字段合并：
  1. 用OPENJSON分别解析原表和临时表的Custom_value为键值对集合
  2. 通过FULL JOIN关联两个集合，确保原表的旧键和新数据的新键都被保留
  3. 用COALESCE优先取新数据的键值，实现重复字段覆盖
  4. 最后用FOR JSON PATH, WITHOUT_ARRAY_WRAPPER将合并后的键值对重新生成为JSON对象

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