如何在SQL Server 2022中以JSON格式存储数据变更日志

需求背景 #

需在SQL Server 2022中将旧版数据变更日志格式转换为JSON格式，记录数据变更内容及操作人，以便.NET 8.0应用消费。

旧格式示例 #

u=[m,id:1],  [Name]=([Old name] : [New name])  [Address]=([1 Old street] : [500 New street])

期望JSON格式 #

SET @json = N'[{        "req": { "type": "m", "id": 1, "via" : "website"},        "log": {                 "name":{"o":"Old name", "n":"New name" },                 "address":{"o":"Old address", "n":"500 New street" }               }    }]';

前期说明 #

• UPDATE 1：时态表不适用于此场景（存储成本因素，需修改现有数据模型）。
• 伪逻辑：

  if data has changed
  create JSON root doc
  add JSON user details to root doc
  foreach data change, append changelog to root doc
  

当前实现及问题 #

已基于FOR JSON PATH完成基础实现，但代码存在重复检查逻辑（多次判断字段是否变更），需优化。

当前实现代码 #

DROP TABLE IF EXISTS #Data    
CREATE TABLE #Data (
    Id int primary key,
    Name nvarchar(255), 
    Address nvarchar(255)
)

INSERT INTO #Data (Id,Name,Address) VALUES (1,'Old name','Old address')

DROP TABLE IF EXISTS #TempLog
CREATE TABLE #TempLog (
    DataId int,
    LogChanges NVARCHAR(max)
);

declare @id int = 1
declare @Name nvarchar(255) = 'New name'
declare @Address nvarchar(255) = '500 New street'

INSERT INTO #TempLog (DataId,LogChanges)
SELECT 
    123,
    (SELECT  
        'm' AS 'req.type',
        @id as 'req.id',
        'website' AS 'req.via',
        IIF(ISNULL(@Name,'')<>ISNULL([Name],''), [Name],NULL) as 'log.name.o',
        IIF(ISNULL(@Name,'')<>ISNULL([Name],''), @Name,NULL) as 'log.name.n', 
        IIF(ISNULL(@Address,'')<>ISNULL([Address],''), [Address],NULL) as 'log.address.o',
        IIF(ISNULL(@Address,'')<>ISNULL([Address],''), @Address,NULL) as 'log.address.n'    
    FROM 
        #Data
    WHERE  
        Id = @id
        AND (
                (@Name<>[Name] OR ISNULL(@Name,'')<>ISNULL([Name],''))
                OR (@Address<>[Address] OR ISNULL(@Address,'')<>ISNULL([Address],''))
            )
    FOR JSON PATH);

生成的JSON示例 #

[
   {
      "req":{
         "type":"m", "id":1,"via":"website"
      },
      "log":{
         "name":{"o":"Old name","n":"New name"},
         "address":{"o":"Old address","n":"500 New street"}
      }
   }
]

优化方案 #

思路：复用变更判断逻辑 #

通过预先计算字段变更标记，避免重复编写ISNULL(@字段,'')<>ISNULL([字段],'')类判断，简化代码结构并提升可维护性。

优化后代码 #

DROP TABLE IF EXISTS #Data    
CREATE TABLE #Data (
    Id int primary key,
    Name nvarchar(255), 
    Address nvarchar(255)
)

INSERT INTO #Data (Id,Name,Address) VALUES (1,'Old name','Old address')

DROP TABLE IF EXISTS #TempLog
CREATE TABLE #TempLog (
    DataId int,
    LogChanges NVARCHAR(max)
);

declare @id int = 1
declare @Name nvarchar(255) = 'New name'
declare @Address nvarchar(255) = '500 New street'

INSERT INTO #TempLog (DataId,LogChanges)
SELECT 
    123,
    (SELECT  
        'm' AS 'req.type',
        @id as 'req.id',
        'website' AS 'req.via',
        IIF(NameChanged, [Name], NULL) as 'log.name.o',
        IIF(NameChanged, @Name, NULL) as 'log.name.n', 
        IIF(AddressChanged, [Address], NULL) as 'log.address.o',
        IIF(AddressChanged, @Address, NULL) as 'log.address.n'    
    FROM (
        SELECT 
            *,
            ISNULL(@Name,'') <> ISNULL([Name],'') AS NameChanged,
            ISNULL(@Address,'') <> ISNULL([Address],'') AS AddressChanged
        FROM #Data
        WHERE Id = @id
    ) AS Source
    WHERE NameChanged OR AddressChanged
    FOR JSON PATH);

优化点说明 #

1. 子查询中预先计算NameChanged和AddressChanged标记字段，将重复判断逻辑统一计算一次
2. 主查询直接复用标记字段，简化IIF函数的条件表达式
3. 外层WHERE条件使用标记字段，逻辑更直观清晰
4. 新增字段时只需添加对应变更标记即可，扩展性更强

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