如果我理解正确，你有两个选择：
修改 Acts json数组使用 JSON_MODIFY （但您需要sql server 2017+将变量用作路径表达式）。这种方法在 SELECT 声明，所以哟一定不要用 ORDER BY 或者 DISTINCT 在声明中。
解析输入的json，使用基于集合的方法以表的形式获得预期的结果，并使用 FOR JSON AUTO json码：

DECLARE @ChangeSet varchar(MAX) = 
'{
    "Acts":
    [
        {"ActId":100,"ActText":"Intro","ActNumber":1},
        {"ActId":0,  "ActText":"Beginning","ActNumber":2},
        {"ActId":0,  "ActText":"Middle","ActNumber":3},
        {"ActId":0,  "ActText":"End","ActNumber":4}
    ]
}';

对账单 JSON_MODIFY :

SELECT @ChangeSet = JSON_MODIFY(
   @ChangeSet,
   CONCAT('$.Acts[', j1.[key], '].ActId'), 
   v.[Id]
)   
FROM OPENJSON(@ChangeSet, '$.Acts') j1
CROSS APPLY OPENJSON(j1.[value]) WITH (ActNumber int '$.ActNumber') j2
JOIN (VALUES 
   (100, 'Act', 'UPDATE', 'Intro',     1),
   (101, 'Act', 'INSERT', 'Beginning', 2),
   (102, 'Act', 'INSERT', 'Middle',    3),
   (103, 'Act', 'INSERT', 'End',       4)
) v ([Id], [Type], [Action], [Value], [ActNumber]) ON v.[ActNumber] = j2.[ActNumber]

对账单 FOR JSON :

SELECT @ChangeSet = (
   SELECT v.[Id] AS ActId, j.ActText, j.ActNumber
   FROM OPENJSON(@ChangeSet, '$.Acts') WITH (
      ActId int '$.ActId',
      ActText varchar(50) '$.ActText',
      ActNumber int '$.ActNumber'
   ) j
   JOIN (VALUES 
      (100, 'Act', 'UPDATE', 'Intro',     1),
      (101, 'Act', 'INSERT', 'Beginning', 2),
      (102, 'Act', 'INSERT', 'Middle',    3),
      (103, 'Act', 'INSERT', 'End',       4)
   ) v ([Id], [Type], [Action], [Value], [ActNumber]) ON v.[ActNumber] = j.[ActNumber]
   FOR JSON AUTO, ROOT ('Acts')
)

结果：

{
    "Acts":
    [
        {"ActId":100, "ActText":"Intro", "ActNumber":1},
        {"ActId":101, "ActText":"Beginning", "ActNumber":2},
        {"ActId":102, "ActText":"Middle", "ActNumber":3},
        {"ActId":103, "ActText":"End", "ActNumber":4}
    ]
}

为了完整地回答这个问题，下面是一个完成的例程，它获取merge语句的输出，将其插入temp表变量，然后用新插入的actid主键更新输入json，这样就可以由procedure out变量返回。

-- SQL 2017+ REQUIRED

DECLARE @ActActions  table( [ActId] int, [Action] varchar(30),
                     [Value] nvarchar(max), [ActNumber] int );

---------------------------------------------------------------------------------

OUTPUT   COALESCE (INSERTED.ActId, DELETED.ActId), $action, 
         COALESCE (INSERTED.ActText, DELETED.ActText),
         COALESCE (INSERTED.ActNumber, DELETED.ActNumber)
INTO    @ActActions;   -- Required semi-colon at end of MERGE

---------------------------------------------------------------------------------

SELECT  @ChangeSetJson = JSON_QUERY(JSON_MODIFY( 
        @ChangeSetJson, '$.Acts[' + j1.[key] + '].ActId', a.[ActId] )  )
FROM    OPENJSON(@ChangeSetJson, '$.Acts') j1 CROSS APPLY 
        OPENJSON(j1.[value])
        WITH (ActNumber int '$.ActNumber') j2 INNER JOIN
        @ActActions a
            ON a.[ActNumber] = j2.[ActNumber]
WHERE  a.[Action] = 'INSERT'