如何在SQL Server中移除JSON嵌套序列化数据里的ContactDTO属性

问题的核心在于JsonEntity字段存储的是序列化后的JSON字符串，而非SQL Server原生的JSON类型，直接使用JSON_MODIFY无法生效，需要先将字符串解析为JSON结构再处理。

解决方案步骤 #

1. 用JSON_QUERY将JsonEntity的字符串内容解析为JSON对象
2. 用JSON_MODIFY将解析后的JSON对象中的ContactDTO属性设为NULL（SQL Server中通过此方式移除JSON属性）
3. 处理后的JSON对象会自动序列化为字符串，直接更新回JsonEntity字段即可

具体SQL代码 #

1. 先验证处理结果（可选）

SELECT 
    EventID,
    SiteID,
    JSON_MODIFY(JSON_QUERY(JsonEntity), '$.ContactDTO', NULL) AS ModifiedJsonEntity,
    TemplateID
FROM YourTableName

2. 执行更新操作

UPDATE YourTableName
SET JsonEntity = JSON_MODIFY(JSON_QUERY(JsonEntity), '$.ContactDTO', NULL)
-- 可添加WHERE条件过滤需要更新的行，例如
-- WHERE EventID = 901416

3. 带格式验证的更新（推荐）

如果JsonEntity可能存在格式错误，可先用ISJSON过滤有效JSON行：

UPDATE YourTableName
SET JsonEntity = JSON_MODIFY(JSON_QUERY(JsonEntity), '$.ContactDTO', NULL)
WHERE ISJSON(JsonEntity) = 1
-- 追加其他业务过滤条件

测试示例（用临时表模拟） #

-- 创建临时表
CREATE TABLE #TempEventData (
    EventID INT,
    SiteID INT,
    JsonEntity NVARCHAR(MAX),
    TemplateID INT
)

-- 插入示例数据
INSERT INTO #TempEventData VALUES (
    901416,
    11394,
    '{"ContactDTO":{"Contact":{"ContactByEmail":true,"ContactBySMS":false,"ContactID":"400002","CustomerID":"300001"},"RecipientType":"Responder"},"ContactName":"Tester", "Method":"Email"}',
    20001
)

-- 执行更新
UPDATE #TempEventData
SET JsonEntity = JSON_MODIFY(JSON_QUERY(JsonEntity), '$.ContactDTO', NULL)

-- 查看处理后的数据
SELECT * FROM #TempEventData

-- 清理临时表
DROP TABLE #TempEventData

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