T-SQL中使用OPENJSON区分NULL值与缺失值并实现精准更新

你的核心需求是当JSON中显式指定字段为null时将对应表字段更新为NULL，字段缺失时保留原有值，原代码的ISNULL(J.Category,E.Category)无法满足需求——因为无论字段是显式null还是缺失，解析后的J.Category都是NULL，ISNULL会统一保留原值，无法区分两种场景。

解决方案代码 #

通过在JSON解析时保留每个对象的原始JSON内容，结合EXISTS判断字段是否存在，就能精准区分两种场景：

DECLARE @json NVARCHAR(MAX) = '[{"ID": 1,"FullName": "Joe Brown","Category": "Sales"},{"ID": 2,"FullName": "Sue Walker","Category": null},{"ID": 3,"FullName": "Tom Jones"}]';

DECLARE @employees TABLE (ID Int, FullName VarChar(50), Category VarChar(50))
INSERT INTO @employees (ID, FullName, Category)
VALUES (1,'Joe Brown','Sales')
,(2,'Sue Walker','Sales')
,(3,'Tom Jones','Sales')

UPDATE E SET
    E.FullName = J.FullName,
    E.Category = CASE 
                    -- 判断当前JSON对象中是否存在Category字段
                    WHEN EXISTS (SELECT 1 FROM OPENJSON(J.json_obj) WHERE [key] = 'Category')
                    THEN J.Category -- 存在则使用解析后的值（包括显式null）
                    ELSE E.Category -- 不存在则保留原有值
                 END
FROM @employees E
INNER JOIN
    (SELECT *
    FROM OPENJSON(@json)
    WITH (
        ID INT,
        FullName NVARCHAR(MAX) '$.FullName',
        Category NVARCHAR(MAX) '$.Category',
        json_obj NVARCHAR(MAX) '$' -- 保留每个JSON对象的原始内容
    )) J ON J.ID = E.ID
;

SELECT * FROM @employees

执行后结果：

• Joe Brown：Category保持Sales（和JSON一致）
• Sue Walker：Category被更新为NULL（JSON显式指定null）
• Tom Jones：Category保留原有Sales（JSON缺失该字段）

处理超100个字段的批量生成 #

如果表中有大量字段，手动编写每个字段的CASE语句效率极低，可以通过系统表批量生成更新语句：

-- 替换为你的实际表名
SELECT 
    'E.' + c.name + ' = CASE WHEN EXISTS (SELECT 1 FROM OPENJSON(J.json_obj) WHERE [key] = ''' + c.name + ''') THEN J.' + c.name + ' ELSE E.' + c.name + ' END,'
FROM sys.columns c
WHERE object_id = OBJECT_ID('dbo.employees')
AND c.name NOT IN ('ID') -- 排除主键等不需要更新的字段

执行这段SQL会生成每个字段对应的更新代码，直接复制到UPDATE语句中即可。

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