在SQL Server中合并两个JSON字符串（SCD Type 1更新场景）

需求说明 #

在使用SCD Type1更新包含JSON列的维度表时，需合并两个JSON字符串：

• 旧JSON（原表数据）：维度表中已存储的JSON内容
• 新JSON（待更新数据）：用于覆盖或补充的源JSON内容
  合并规则：重复属性以新JSON的值为准，新JSON新增的属性直接保留，旧JSON中未被新JSON覆盖的属性也保留（若无需保留旧属性，可调整逻辑仅保留新JSON内容）。

示例数据 #

旧JSON：

{ "Name":"Suneel","Age":23 }

新JSON：

{"Name":"Suneel Kumar","Age":23,"City":"Banglore"}

期望合并结果 #

{"Name":"Suneel Kumar","Age":23,"City":"Banglore"}

SQL实现方案（适用于SQL Server 2016+） #

以下代码可嵌入动态存储过程，兼容任意顶层属性的JSON合并：

-- 定义JSON变量（存储过程中可替换为表列或传入参数）
DECLARE @oldJson NVARCHAR(MAX) = N'{ "Name":"Suneel","Age":23 }';
DECLARE @newJson NVARCHAR(MAX) = N'{"Name":"Suneel Kumar","Age":23,"City":"Banglore"}';

-- 临时表存储合并后的键值对
SELECT 
    COALESCE(new_kv.[key], old_kv.[key]) AS [key],
    COALESCE(new_kv.value, old_kv.value) AS value
INTO #MergedJsonKv
FROM OPENJSON(@oldJson) old_kv
FULL OUTER JOIN OPENJSON(@newJson) new_kv ON old_kv.[key] = new_kv.[key];

-- 生成最终合并后的JSON
SELECT * FROM #MergedJsonKv
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;

-- 清理临时表
DROP TABLE #MergedJsonKv;

代码说明 #

1. 解析JSON：通过OPENJSON将两个JSON字符串转换为包含key（属性名）和value（属性值）的行集
2. 合并键值对：用FULL OUTER JOIN确保所有属性都被覆盖，COALESCE函数优先取新JSON的属性值，仅当新JSON无该属性时保留旧JSON的值
3. 生成JSON：通过FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER将合并后的键值对重新组装为单个JSON对象

若仅需保留新JSON的所有属性（忽略旧JSON未被覆盖的属性），可将FULL OUTER JOIN改为RIGHT JOIN，或直接读取新JSON的键值对即可。

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