如何在Array of Array上进行JSON_MODIFY? [英] How to JSON_MODIFY on Array of Array?
问题描述
我的结构看起来像这样
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"
}
]
}
]
}
]
}
'
我想做的是做到这一点:
What I would like to do is to do this:
update FieldData = 'DataUpdated'
where FieldName = 'Field2'
and RenderName = 'Render'
and SectionName = 'Section1'
我该如何使用JSON_MODIFY做到这一点?
How would 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'
)
推荐答案
这并不像人们希望的那么简单.令我惊讶的是,似乎没有一种简单的方法可以查询JSON结构中项目的完整路径.
This is not as straightforward as one might hope. I was surprised that there seems to be no simple way to query the full path of an item in a JSON structure.
JSON_MODIFY
仅在定位目标数组的成员,因此此处的大部分工作将为每个嵌套的数组成员生成索引.看来[key]
列只能在不使用WITH
子句的情况下使用OPENJSON
时生成,因此我无法重用您的查询.
JSON_MODIFY
can only accept array indicies when targetting a member of an array, so most of the work here goes into generating the indexes for each nested array member. It appears that the [key]
column can only be generated when using OPENJSON
without a WITH
clause, so I couldn't reuse your query.
此外,JSON_MODIFY
将仅接受JSON路径的字符串文字,因此必须使用动态SQL进行更新.
Additionally, JSON_MODIFY
will only accept a string literal for the JSON path, so the update has to be carried out using dynamic SQL.
(请注意,该解决方案假定您要更新特定的RenderName,例如'Render1'
-此时问题尚不清楚.)
(Please note that this solution assumes that you want to update a specific RenderName e.g. 'Render1'
- the question is unclear on this point.)
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
这篇关于如何在Array of Array上进行JSON_MODIFY?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!