CODEWITHSUNDEEP
arraysjsonsql-servert-sqlfor-json
Nosnetrom
Nosnetrom

Reputation: 161

Target an array index when updating JSON data?

I have a column of JSON data that I need to update; the problem is that I need to dynamically target the index of an array within the JSON (specifically, by DocumentTemplatePageId). Here's the structure of my JSON, showing the page object that I need to modify; what I want to do is add to, edit, or delete from the IssuesList node on any given Page object.

{ "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"
                }
              ]
            }
        },
        { repeat N times }
    ]
}

I can target a specific DocumentTemplatePageId and update its data using JSON_MODIFY, as follows:

DECLARE @cdtid UNIQUEIDENTIFIER  = '2380493F-F36B-1410-8C7D-006D55A93A8F'
DECLARE @pageid uniqueidentifier = 'C359FB3F-F36B-1410-8C7C-006D55A93A8F' --'1A5AFB3F-F36B-1410-8C7C-006D55A93A8F' --'F472F93F-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'

-- Insert the new data
SET @originalJsonIssues = JSON_MODIFY(@originalJsonIssues, @path, @strNewJsonIssuesValue)

Having updated the IssuesList node for a specific page, I'm stumped on how I might insert that chunk of data into the overall JSON. I imagine it would take a series of complex JSON_QUERY statements to do the job... (I even considered taking the coward's way out by just doing a big string replace).

Upvotes: 1

Views: 49

Answers (1)

Charlieface
Charlieface

Reputation: 71364

You can rebuild the whole JSON inside an APPLY, then just UPDATE based on that

DECLARE @cdtid UNIQUEIDENTIFIER  = '2380493F-F36B-1410-8C7D-006D55A93A8F';
DECLARE @pageid uniqueidentifier = 'C359FB3F-F36B-1410-8C7C-006D55A93A8F'; --'1A5AFB3F-F36B-1410-8C7C-006D55A93A8F' --'F472F93F-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)/"}]';


UPDATE cdt
SET cdt.JsonData = j.NewJson
FROM dbo.CompanyxDocumentTemplate cdt
CROSS APPLY (
    SELECT
      j1.DocumentTemplatePageId,
      j1.DocumentTemplatePageTypeId,
      IIF(j1.DocumentTemplatePageId = @pageid, @strNewJsonIssuesValue, j1.IssueList) AS [PageData.IssuesList]
    FROM OPENJSON(cdt.JsonData, '$.Pages')
      WITH (
        DocumentTemplatePageId uniqueidentifier,
        DocumentTemplatePageTypeId int,
        IssuesList nvarchar(max) '$.PageData.IssuesList' AS JSON
      ) j1
    FOR JSON PATH, ROOT('Pages')
) j(NewJson)
WHERE cdt.Id = @cdtid;

You could also add further joins. For example:

UPDATE cdt
SET cdt.JsonData = j.NewJson
FROM dbo.CompanyxDocumentTemplate cdt
CROSS APPLY (
    SELECT
      j1.DocumentTemplatePageId,
      j1.DocumentTemplatePageTypeId,
      ISNULL(jd.NewIssueList, j1.IssueList) AS [PageData.IssuesList]
    FROM OPENJSON(cdt.JsonData, '$.Pages')
      WITH (
        DocumentTemplatePageId uniqueidentifier,
        DocumentTemplatePageTypeId int,
        IssuesList nvarchar(max) '$.PageData.IssuesList' AS JSON
      ) j1
    LEFT JOIN dbo.NewJsonData jd ON jd.DocumentTemplatePageId = j1.DocumentTemplatePageId
    FOR JSON PATH, ROOT('Pages')
) j(NewJson)
WHERE cdt.Id = @cdtid;

Upvotes: 0

Related Questions