如何用SQL Server的OPENJSON处理JSON可变长度fields列表生成动态SQL？

你完全可以通过嵌套OPENJSON+CROSS APPLY来解析内层的fields数组，不用自己做字符串解析，下面是具体实现：

首先，把外层和内层的JSON一起解析，再通过字符串聚合拼接出UPDATE需要的SET子句：

DECLARE @json NVARCHAR(MAX) = N'{
  "actions":[
     {"action":"delete","table":"users","key":4},
     {"action":"update","table":"users","key":5,
      "fields":[{"Name":"FirstName","OldValue":"X","NewValue":"Bob"},
                {"Name":"LastName","OldValue":"Y","NewValue":"Dobbs"}]
     }
  ]
}';

SELECT
  CASE 
    WHEN a.[Action] = 'update' THEN 
      CONCAT(
        'UPDATE [', a.[Table], '] SET ',
        STRING_AGG(
          CONCAT('[', f.Name, '] = ''', REPLACE(f.NewValue, '''', ''''''), ''''),
          ', '
        ),
        ' WHERE [key] = ', a.[Key]
      )
    WHEN a.[Action] = 'delete' THEN
      CONCAT('DELETE FROM [', a.[Table], '] WHERE [key] = ', a.[Key])
  END AS DynamicSQL
FROM OPENJSON(@json, '$.actions')
WITH (
  [Action] VARCHAR(25) '$.action',
  [Table] VARCHAR(25) '$.table',
  [Key] VARCHAR(25) '$.key',
  [fields] NVARCHAR(MAX) AS JSON
) a
-- 嵌套解析内层fields数组
CROSS APPLY OPENJSON(a.fields)
WITH (
  Name VARCHAR(50) '$.Name',
  NewValue VARCHAR(MAX) '$.NewValue'
) f
-- 按每个action条目分组，聚合SET子句的内容
GROUP BY a.[Action], a.[Table], a.[Key];

关键步骤说明： #

1. 嵌套解析：用CROSS APPLY OPENJSON(a.fields)把每个action里的fields数组拆成多行，每行对应一个字段的新旧值。
2. 字符串聚合：用STRING_AGG把每个字段的[字段名]='新值'拼接成逗号分隔的SET子句内容，用REPLACE(f.NewValue, '''', '''''')转义单引号，避免SQL语法错误和注入风险。
3. 分支生成SQL：根据action类型分别生成UPDATE或DELETE语句。

执行后会得到两条动态SQL：

DELETE FROM [users] WHERE [key] = 4
UPDATE [users] SET [FirstName]='Bob',[LastName]='Dobbs' WHERE [key] = 5

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