更新JSON数据时如何动态定位数组索引？

我有一列JSON数据需要更新，需通过DocumentTemplatePageId动态定位JSON中Pages数组的对应索引，目标是对任意Page对象的IssuesList节点进行增、删、改操作。

JSON结构示例 #

{ "Pages":
    [
        {
            "DocumentTemplatePageId": "C359FB3F-F36B-1410-8C7C-006D55A93A8F",
            "DocumentTemplatePageTypeId": 9,
            "PageData": {
              "IssuesList": [
                {
                  "IssueOrderNumber": 1,
                  "Description": "Houston, we have a problem.",
                  "Resolution": "We are working on it.",
                  "Severity": 5,
                  "DueDate": "2024-01-15"
                },
                {
                  "IssueOrderNumber": 2,
                  "Description": "We have issues with employee retention.",
                  "Resolution": "We will start giving retention bonuses.",
                  "Severity": 2,
                  "DueDate": "2023-12-30"
                },
                {
                  "IssueOrderNumber": 3,
                  "Description": "Increasing incidents of sexual harassment.",
                  "Resolution": "Sexual harassment training is now doubled, and an independent investigator/arbitrator has been hired.",
                  "Severity": 3,
                  "DueDate": "2023-12-01"
                }
              ]
            }
        },
        { /* 重复N个类似Page对象 */ }
    ]
}

已实现的部分代码 #

目前已能通过JSON_MODIFY定位特定DocumentTemplatePageId并更新其IssuesList数据，代码如下：

DECLARE @cdtid UNIQUEIDENTIFIER  = '2380493F-F36B-1410-8C7D-006D55A93A8F'
DECLARE @pageid uniqueidentifier = 'C359FB3F-F36B-1410-8C7C-006D55A93A8F'
DECLARE @strNewJsonIssuesValue NVARCHAR(MAX) = N'[{"IssueOrderNumber":1,"Description":"Houston, we have a problem.","Resolution":"We are working on it.","Severity":5,"DueDate":"/Date(1705298400000)/"},{"IssueOrderNumber":2,"Description":"We have issues with employee retention.","Resolution":"We will start giving retention bonuses.","Severity":2,"DueDate":"/Date(1703916000000)/"},{"IssueOrderNumber":3,"Description":"Increasing incidents of harassment.","Resolution":"Harassment training is now doubled, and an independent investigator/arbitrator has been hired.","Severity":3,"DueDate":"/Date(1701410400000)/"},{"IssueOrderNumber":4,"Description":"My descr","Resolution":"My reso","Severity":5,"DueDate":"/Date(1709294400000)/"}]'

DECLARE @originalJsonIssues NVARCHAR(MAX) = (
SELECT
  j1.IssuesList
FROM dbo.CompanyxDocumentTemplate cdt
CROSS APPLY OPENJSON(cdt.JsonData, '$.Pages')
  WITH (
    DocumentTemplatePageId uniqueidentifier,
    DocumentTemplatePageTypeId int,
    IssuesList nvarchar(max) '$.PageData.IssuesList' AS JSON
  ) j1
WHERE cdt.Id = @cdtid
AND j1.DocumentTemplatePageId = @pageid
FOR JSON PATH
)

DECLARE @path VARCHAR(40) = '$[0].IssuesList'

-- 更新IssuesList数据
SET @originalJsonIssues = JSON_MODIFY(@originalJsonIssues, @path, @strNewJsonIssuesValue)

当前困境 #

已更新了特定页面的IssuesList节点，但不知道如何将修改后的内容插回整体JSON中，考虑过使用复杂的JSON_QUERY语句，甚至想过用字符串替换的方式解决。

解决方案 #

不需要单独提取IssuesList修改后再插回，可直接通过定位目标Page在数组中的索引，构造动态路径直接更新原JSON：

DECLARE @cdtid UNIQUEIDENTIFIER  = '2380493F-F36B-1410-8C7D-006D55A93A8F'
DECLARE @pageid uniqueidentifier = 'C359FB3F-F36B-1410-8C7C-006D55A93A8F'
DECLARE @strNewJsonIssuesValue NVARCHAR(MAX) = N'[{"IssueOrderNumber":1,"Description":"Houston, we have a problem.","Resolution":"We are working on it.","Severity":5,"DueDate":"/Date(1705298400000)/"},{"IssueOrderNumber":2,"Description":"We have issues with employee retention.","Resolution":"We will start giving retention bonuses.","Severity":2,"DueDate":"/Date(1703916000000)/"},{"IssueOrderNumber":3,"Description":"Increasing incidents of harassment.","Resolution":"Harassment training is now doubled, and an independent investigator/arbitrator has been hired.","Severity":3,"DueDate":"/Date(1701410400000)/"},{"IssueOrderNumber":4,"Description":"My descr","Resolution":"My reso","Severity":5,"DueDate":"/Date(1709294400000)/"}]'

-- 1. 获取目标Page在Pages数组中的索引
DECLARE @index INT
SELECT @index = [key]
FROM dbo.CompanyxDocumentTemplate cdt
CROSS APPLY OPENJSON(cdt.JsonData, '$.Pages')
  WITH (
    DocumentTemplatePageId uniqueidentifier
  ) j1
WHERE cdt.Id = @cdtid
AND j1.DocumentTemplatePageId = @pageid

-- 2. 构造动态JSON路径
DECLARE @jsonPath NVARCHAR(100) = '$.Pages[' + CAST(@index AS VARCHAR(10)) + '].PageData.IssuesList'

-- 3. 直接更新原JSON数据
UPDATE dbo.CompanyxDocumentTemplate
SET JsonData = JSON_MODIFY(JsonData, @jsonPath, JSON_QUERY(@strNewJsonIssuesValue))
WHERE Id = @cdtid

关键说明 #

• 通过OPENJSON返回的[key]字段直接获取数组的索引（OPENJSON解析数组时，key就是元素的索引值）
• 使用JSON_QUERY包裹新的JSON字符串，确保SQL Server不会将其转义为普通字符串，而是保留JSON结构
• 直接在原表的JsonData字段上执行JSON_MODIFY，一步完成更新，无需单独提取再合并

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