How is JSON_MODIFY in an array array?

Question

My strucutre looks like this

Declare @layout NVARCHAR(MAX) = N' { "Sections": [ { "SectionName":"Section1", "SectionOrder":1, "Renders":[ { "RenderName":"Render1", "RenderOrder":1, "Fields":[ { "FieldName":"Field1", "FieldData":"Data1" }, { "FieldName":"Field2", "FieldData":"Data2" } ] }, { "RenderName":"Render2", "RenderOrder":2, "Fields":[ { "FieldName":"Field1", "FieldData":"Data1" }, { "FieldName":"Field2", "FieldData":"Data2" } ] } ] }, { "SectionName":"Section2", "SectionOrder":2, "Renders":[ { "RenderName":"Render1", "RenderOrder":1, "Fields":[ { "FieldName":"Field1", "FieldData":"Data1" } ] }, { "RenderName":"Render2", "RenderOrder":2, "Fields":[ { "FieldName":"Field1", "FieldData":"Data1" }, { "FieldName":"Field2", "FieldData":"Data2" } ] } ] } ] } ' 

I would like to do this:

 update FieldData = 'DataUpdated' where FieldName = 'Field2' and RenderName = 'Render' and SectionName = 'Section1' 

How do I do this using JSON_MODIFY?

 I can GET the data using the following query: SELECT SectionName, SectionOrder, RenderName, RenderOrder, FieldName, FieldData FROM ( SELECT SectionName, SectionOrder, RenderName, RenderOrder, Fields FROM ( select SectionName, SectionOrder, Renders from OPENJSON(@layout,'$.Sections') WITH ( SectionName nvarchar(MAX) '$.SectionName', SectionOrder nvarchar(MAX) '$.SectionOrder', Renders nvarchar(MAX) '$.Renders' as JSON ) ) as Sections CROSS APPLY OPENJSON(Renders,'$') WITH ( RenderName nvarchar(MAX) '$.RenderName', RenderOrder nvarchar(MAX) '$.RenderOrder', Fields nvarchar(MAX) '$.Fields' as JSON ) ) as Renders CROSS APPLY OPENJSON(Fields,'$') WITH ( FieldName nvarchar(MAX) '$.FieldName', FieldData nvarchar(MAX) '$.FieldData' ) 

Answer 1

It is not as easy as one might hope. I was surprised that there seems to be no easy way to query the full path of an element in a JSON structure.

JSON_MODIFY can accept array attributes only when assigning an array member, so most of the work here is to create indexes for each nested array element. It appears that the [key] column can only be generated when using OPENJSON without the WITH clause, so I cannot reuse your query.

In addition, JSON_MODIFY will only accept a string literal for the JSON path, so updating should be done using dynamic SQL.

(Note that this solution assumes that you want to update a specific RenderName, for example, 'Render1' - the question is unclear in this question.)

 DECLARE @path nvarchar(2048) SELECT @path = FORMATMESSAGE('SET @layout = JSON_MODIFY(@layout, ''$.Sections[%s].Renders[%s].Fields[%s].FieldData'', @newValue)' ,sectionindex, renderindex, [key]) FROM ( SELECT sectionindex, sectionName, b.[key] as renderindex, b.[value] AS bvalue, JSON_VALUE([Value], '$.RenderName') AS renderName FROM (SELECT [key] AS sectionindex, [Value] AS avalue, JSON_VALUE([Value], '$.SectionName') AS sectionName FROM OPENJSON(@layout, '$.Sections') ) AS sections CROSS APPLY OPENJSON(sections.avalue, '$.Renders') AS b ) AS renders CROSS APPLY OPENJSON(renders.bvalue,'$.Fields' ) AS d WHERE JSON_VALUE([Value], '$.FieldName') = 'Field2' AND RenderName = 'Render1' AND SectionName = 'Section1' -- execute the update; this has to happen in dynamic SQL because the JSON_MODIFY path has to be a literal value, and cannot be a variable EXEC sp_executeSQL @path, N'@layout nvarchar(max) OUTPUT, @newValue nvarchar(max)', @layout = @layout OUTPUT, @newValue = 'DateUpdated' --check the results SELECT sectionindex, sectionName, renderindex, rendername, [key] AS fieldindex, JSON_VALUE([Value], '$.FieldName') AS FieldName, JSON_VALUE([Value], '$.FieldData') AS FieldName FROM ( SELECT sectionindex, sectionName, b.[key] AS renderindex, b.[value] AS bvalue, JSON_VALUE([Value], '$.RenderName') AS renderName FROM (SELECT [key] as sectionindex, [Value] as avalue, JSON_VALUE([Value], '$.SectionName') AS sectionName FROM OPENJSON(@layout, '$.Sections') ) AS sections CROSS APPLY OPENJSON(sections.avalue, '$.Renders') AS b ) AS renders CROSS APPLY OPENJSON(renders.bvalue,'$.Fields' ) AS d