How to Use JSON_MODIFY for array within a array

Question

This is my Json string:

{  
   "JobID":1005,
   "ItemID":2,
   "Prefix":"CC",
   "Version":1,
   "ItemTypeID":4,
   "ItemType":"Change Control",
   "StageID":3,
   "StageName":"Change Control Closure",
   "WorkflowID":1,
   "WorkflowName":"Change Control Workflow",
   "JobStatus":0,
   "IsJobArchived":false,
   "IsJobCanceled":false,
   "IsJobOnHold":false,
   "IsJobPhasedOut":false,
   "RelationID":0,
   "JobLastUpdatedBy":"G4Admin",
   "JobStartDate":"2019-07-18T08:02:44.737718Z",
   "JobLastUpdated":"2019-07-18T08:02:44.9857249Z",
   "JobDueDate":"2019-07-23T08:02:44.8997205Z",
   "JobEstimatedDate":"2019-07-23T08:02:44.8877188Z",
   "JobCompletedDate":null,
   "RevisionNumber":1,
   "DocketNo":390,
   "JobVariance":"0",
   "Mappings":[  

   ],
   "Groups":[  
      12
   ],
   "InactiveGroups":null,
   "Attributes":[  
      {  
         "ID":22,
         "Name":"WorkflowType",
         "Value":"50",
         "DataType":"Text",
         "IsMaster":true,
         "ItemData":true,
         "IsUserGroup":false,
         "GroupId":null,
         "ValueString":"Change Control",
         "InactiveValue":null,
         "InactiveValueString":null
      },
      {  
         "ID":18,
         "Name":"Reason for change",
         "Value":"23",
         "DataType":"Text",
         "IsMaster":true,
         "ItemData":true,
         "IsUserGroup":false,
         "GroupId":null,
         "ValueString":"CMC change",
         "InactiveValue":null,
         "InactiveValueString":null
      },
      {  
         "ID":12,
         "Name":"Market",
         "Value":"12",
         "DataType":"Text",
         "IsMaster":true,
         "ItemData":true,
         "IsUserGroup":true,
         "GroupId":null,
         "ValueString":"Ireland",
         "InactiveValue":null,
         "InactiveValueString":null
      },
      {  
         "ID":14,
         "Name":"Product Name",
         "Value":"34",
         "DataType":"Text",
         "IsMaster":true,
         "ItemData":true,
         "IsUserGroup":false,
         "GroupId":null,
         "ValueString":"DROXIA",
         "InactiveValue":null,
         "InactiveValueString":null
      },
      {  
         "ID":13,
         "Name":"CCDS Impacted?",
         "Value":"15",
         "DataType":"Text",
         "IsMaster":true,
         "ItemData":true,
         "IsUserGroup":false,
         "GroupId":null,
         "ValueString":"No",
         "InactiveValue":null,
         "InactiveValueString":null
      },
      {  
         "ID":11,
         "Name":"JobOwner",
         "Value":"G4Admin",
         "DataType":"Text",
         "IsMaster":false,
         "ItemData":true,
         "IsUserGroup":false,
         "GroupId":null,
         "ValueString":"G4Admin",
         "InactiveValue":null,
         "InactiveValueString":null
      },
      {  
         "ID":50,
         "Name":"PartNumber",
         "Value":"CC-02-01",
         "DataType":0,
         "IsMaster":false,
         "ItemData":false,
         "IsUserGroup":false,
         "GroupId":null,
         "ValueString":"CC-02-01",
         "InactiveValue":null,
         "InactiveValueString":null
      },
      {  
         "ID":50,
         "Name":"JobInitiator",
         "Value":"G4Admin",
         "DataType":0,
         "IsMaster":false,
         "ItemData":false,
         "IsUserGroup":false,
         "GroupId":null,
         "ValueString":"G4Admin",
         "InactiveValue":null,
         "InactiveValueString":null
      },
      {  
         "ID":10,
         "Name":"TaskLeft",
         "Value":"3",
         "DataType":0,
         "IsMaster":false,
         "ItemData":false,
         "IsUserGroup":false,
         "GroupId":null,
         "ValueString":null,
         "InactiveValue":null,
         "InactiveValueString":null
      },
      {  
         "ID":4,
         "Name":"ProofCycle",
         "Value":"1",
         "DataType":"Integer",
         "IsMaster":false,
         "ItemData":true,
         "IsUserGroup":false,
         "GroupId":null,
         "ValueString":"1",
         "InactiveValue":null,
         "InactiveValueString":null
      },
      {  
         "ID":5,
         "Name":"PassCycle",
         "Value":"1",
         "DataType":"Integer",
         "IsMaster":false,
         "ItemData":true,
         "IsUserGroup":false,
         "GroupId":null,
         "ValueString":"1",
         "InactiveValue":null,
         "InactiveValueString":null
      },
      {  
         "ID":6,
         "Name":"TotalPassCount",
         "Value":"1",
         "DataType":"Integer",
         "IsMaster":false,
         "ItemData":true,
         "IsUserGroup":false,
         "GroupId":null,
         "ValueString":"1",
         "InactiveValue":null,
         "InactiveValueString":null
      }
   ],
   "LinkedParentJobs":[  

   ],
   "LinkedChildJobs":[  

   ],
   "FullText":null
}

I want to update the array in attributes ...

Example: I want to change the value in attributes where Name = ProofCycle.

Want a dynamic way to update json with the Json_Modify function.

I have queries to flatten the json, but json_Modify is not working to change values in array

DECLARE @KeyValue NVARCHAR(MAX) = '{"JobID":1005,"ItemID":2,"Prefix":"CC","Version":1,"ItemTypeID":4,"ItemType":"Change Control","StageID":3,"StageName":"Change Control Closure","WorkflowID":1,"WorkflowName":"Change Control Workflow","JobStatus":0,"IsJobArchived":false,"IsJobCanceled":false,"IsJobOnHold":false,"IsJobPhasedOut":false,"RelationID":0,"JobLastUpdatedBy":"G4Admin","JobStartDate":"2019-07-18T08:02:44.737718Z","JobLastUpdated":"2019-07-18T08:02:44.9857249Z","JobDueDate":"2019-07-23T08:02:44.8997205Z","JobEstimatedDate":"2019-07-23T08:02:44.8877188Z","JobCompletedDate":null,"RevisionNumber":1,"DocketNo":390,"JobVariance":"0","Mappings":[],"Groups":[12],"InactiveGroups":null,"Attributes":[{"ID":22,"Name":"WorkflowType","Value":"50","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"Change Control","InactiveValue":null,"InactiveValueString":null},{"ID":18,"Name":"Reason for change","Value":"23","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"CMC change","InactiveValue":null,"InactiveValueString":null},{"ID":12,"Name":"Market","Value":"12","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":true,"GroupId":null,"ValueString":"Ireland","InactiveValue":null,"InactiveValueString":null},{"ID":14,"Name":"Product Name","Value":"34","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"DROXIA","InactiveValue":null,"InactiveValueString":null},{"ID":13,"Name":"CCDS Impacted?","Value":"15","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"No","InactiveValue":null,"InactiveValueString":null},{"ID":11,"Name":"JobOwner","Value":"G4Admin","DataType":"Text","IsMaster":false,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"G4Admin","InactiveValue":null,"InactiveValueString":null},{"ID":50,"Name":"PartNumber","Value":"CC-02-01","DataType":0,"IsMaster":false,"ItemData":false,"IsUserGroup":false,"GroupId":null,"ValueString":"CC-02-01","InactiveValue":null,"InactiveValueString":null},{"ID":50,"Name":"JobInitiator","Value":"G4Admin","DataType":0,"IsMaster":false,"ItemData":false,"IsUserGroup":false,"GroupId":null,"ValueString":"G4Admin","InactiveValue":null,"InactiveValueString":null},{"ID":10,"Name":"TaskLeft","Value":"3","DataType":0,"IsMaster":false,"ItemData":false,"IsUserGroup":false,"GroupId":null,"ValueString":null,"InactiveValue":null,"InactiveValueString":null},{"ID":4,"Name":"ProofCycle","Value":"1","DataType":"Integer","IsMaster":false,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"1","InactiveValue":null,"InactiveValueString":null},{"ID":5,"Name":"PassCycle","Value":"1","DataType":"Integer","IsMaster":false,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"1","InactiveValue":null,"InactiveValueString":null},{"ID":6,"Name":"TotalPassCount","Value":"1","DataType":"Integer","IsMaster":false,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"1","InactiveValue":null,"InactiveValueString":null}],"LinkedParentJobs":[],"LinkedChildJobs":[],"FullText":null}'

SELECT 
    -- Change Value to 80 where Name = 'Passcycle'
    JSON_MODIFY(@KeyValue, '$.Attributes', '80')

I get a null output.

Can anyone help me to sort out this issue?

Zhorov · Accepted Answer · 2019-08-02 07:16:40Z

Explanations:

You can access and modify an item in the "Attributes" JSON array with $.Attributes[x].Value path expression, where x is the index of the item. You need to pass this path expression to JSON_MODIFY. So, one possible approach here is to generate and execute a dynamic statement. But if you use SQL Server 2017+, you can provide a variable as the value of this path expression.

Dymanic statement (SQL Server 2016+):

DECLARE @KeyValue NVARCHAR(MAX) = N'{"JobID":1005,"ItemID":2,"Prefix":"CC","Version":1,"ItemTypeID":4,"ItemType":"Change Control","StageID":3,"StageName":"Change Control Closure","WorkflowID":1,"WorkflowName":"Change Control Workflow","JobStatus":0,"IsJobArchived":false,"IsJobCanceled":false,"IsJobOnHold":false,"IsJobPhasedOut":false,"RelationID":0,"JobLastUpdatedBy":"G4Admin","JobStartDate":"2019-07-18T08:02:44.737718Z","JobLastUpdated":"2019-07-18T08:02:44.9857249Z","JobDueDate":"2019-07-23T08:02:44.8997205Z","JobEstimatedDate":"2019-07-23T08:02:44.8877188Z","JobCompletedDate":null,"RevisionNumber":1,"DocketNo":390,"JobVariance":"0","Mappings":[],"Groups":[12],"InactiveGroups":null,"Attributes":[{"ID":22,"Name":"WorkflowType","Value":"50","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"Change Control","InactiveValue":null,"InactiveValueString":null},{"ID":18,"Name":"Reason for change","Value":"23","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"CMC change","InactiveValue":null,"InactiveValueString":null},{"ID":12,"Name":"Market","Value":"12","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":true,"GroupId":null,"ValueString":"Ireland","InactiveValue":null,"InactiveValueString":null},{"ID":14,"Name":"Product Name","Value":"34","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"DROXIA","InactiveValue":null,"InactiveValueString":null},{"ID":13,"Name":"CCDS Impacted?","Value":"15","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"No","InactiveValue":null,"InactiveValueString":null},{"ID":11,"Name":"JobOwner","Value":"G4Admin","DataType":"Text","IsMaster":false,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"G4Admin","InactiveValue":null,"InactiveValueString":null},{"ID":50,"Name":"PartNumber","Value":"CC-02-01","DataType":0,"IsMaster":false,"ItemData":false,"IsUserGroup":false,"GroupId":null,"ValueString":"CC-02-01","InactiveValue":null,"InactiveValueString":null},{"ID":50,"Name":"JobInitiator","Value":"G4Admin","DataType":0,"IsMaster":false,"ItemData":false,"IsUserGroup":false,"GroupId":null,"ValueString":"G4Admin","InactiveValue":null,"InactiveValueString":null},{"ID":10,"Name":"TaskLeft","Value":"3","DataType":0,"IsMaster":false,"ItemData":false,"IsUserGroup":false,"GroupId":null,"ValueString":null,"InactiveValue":null,"InactiveValueString":null},{"ID":4,"Name":"ProofCycle","Value":"1","DataType":"Integer","IsMaster":false,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"1","InactiveValue":null,"InactiveValueString":null},{"ID":5,"Name":"PassCycle","Value":"1","DataType":"Integer","IsMaster":false,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"1","InactiveValue":null,"InactiveValueString":null},{"ID":6,"Name":"PassCycle","Value":"1","DataType":"Integer","IsMaster":false,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"1","InactiveValue":null,"InactiveValueString":null}],"LinkedParentJobs":[],"LinkedChildJobs":[],"FullText":null}'

DECLARE @stm nvarchar(max) = N''
SELECT @stm = @stm + CONCAT(
   'SELECT @KeyValue = JSON_MODIFY(@KeyValue, ''$.Attributes[',
   [key],
   '].Value'', ''80''); '
)   
FROM OPENJSON(@keyValue, '$.Attributes')
WHERE JSON_VALUE([value], '$.Name') = N'PassCycle'

EXECUTE sp_executesql @stm, N'@KeyValue NVARCHAR(MAX) OUTPUT', @KeyValue OUTPUT

Using variable as path expression (SQL Server 2017+):

DECLARE @KeyValue NVARCHAR(MAX) = N'{"JobID":1005,"ItemID":2,"Prefix":"CC","Version":1,"ItemTypeID":4,"ItemType":"Change Control","StageID":3,"StageName":"Change Control Closure","WorkflowID":1,"WorkflowName":"Change Control Workflow","JobStatus":0,"IsJobArchived":false,"IsJobCanceled":false,"IsJobOnHold":false,"IsJobPhasedOut":false,"RelationID":0,"JobLastUpdatedBy":"G4Admin","JobStartDate":"2019-07-18T08:02:44.737718Z","JobLastUpdated":"2019-07-18T08:02:44.9857249Z","JobDueDate":"2019-07-23T08:02:44.8997205Z","JobEstimatedDate":"2019-07-23T08:02:44.8877188Z","JobCompletedDate":null,"RevisionNumber":1,"DocketNo":390,"JobVariance":"0","Mappings":[],"Groups":[12],"InactiveGroups":null,"Attributes":[{"ID":22,"Name":"WorkflowType","Value":"50","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"Change Control","InactiveValue":null,"InactiveValueString":null},{"ID":18,"Name":"Reason for change","Value":"23","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"CMC change","InactiveValue":null,"InactiveValueString":null},{"ID":12,"Name":"Market","Value":"12","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":true,"GroupId":null,"ValueString":"Ireland","InactiveValue":null,"InactiveValueString":null},{"ID":14,"Name":"Product Name","Value":"34","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"DROXIA","InactiveValue":null,"InactiveValueString":null},{"ID":13,"Name":"CCDS Impacted?","Value":"15","DataType":"Text","IsMaster":true,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"No","InactiveValue":null,"InactiveValueString":null},{"ID":11,"Name":"JobOwner","Value":"G4Admin","DataType":"Text","IsMaster":false,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"G4Admin","InactiveValue":null,"InactiveValueString":null},{"ID":50,"Name":"PartNumber","Value":"CC-02-01","DataType":0,"IsMaster":false,"ItemData":false,"IsUserGroup":false,"GroupId":null,"ValueString":"CC-02-01","InactiveValue":null,"InactiveValueString":null},{"ID":50,"Name":"JobInitiator","Value":"G4Admin","DataType":0,"IsMaster":false,"ItemData":false,"IsUserGroup":false,"GroupId":null,"ValueString":"G4Admin","InactiveValue":null,"InactiveValueString":null},{"ID":10,"Name":"TaskLeft","Value":"3","DataType":0,"IsMaster":false,"ItemData":false,"IsUserGroup":false,"GroupId":null,"ValueString":null,"InactiveValue":null,"InactiveValueString":null},{"ID":4,"Name":"ProofCycle","Value":"1","DataType":"Integer","IsMaster":false,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"1","InactiveValue":null,"InactiveValueString":null},{"ID":5,"Name":"PassCycle","Value":"1","DataType":"Integer","IsMaster":false,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"1","InactiveValue":null,"InactiveValueString":null},{"ID":6,"Name":"PassCycle","Value":"1","DataType":"Integer","IsMaster":false,"ItemData":true,"IsUserGroup":false,"GroupId":null,"ValueString":"1","InactiveValue":null,"InactiveValueString":null}],"LinkedParentJobs":[],"LinkedChildJobs":[],"FullText":null}'

SELECT @KeyValue = JSON_MODIFY(@KeyValue, '$.Attributes[' + [key] + '].Value', '80')
FROM OPENJSON(@keyValue, '$.Attributes')
WHERE JSON_VALUE([value], '$.Name') = N'PassCycle'

Notes:

Both examples will modify all items (not only one) in the JSON array with matching condition JSON_VALUE([value], '$.Name') = N'PassCycle'.

Collectives™ on Stack Overflow

How to Use JSON_MODIFY for array within a array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related