从openjson SQL Server 2016中的嵌套数组中删除对象 [英] Delete an object from nested array in openjson SQL Server 2016
问题描述
我想从 SQL Server 2016 中的以下 json 中删除 "AttributeName" : "Manufacturer"
:
I want to delete the "AttributeName" : "Manufacturer"
from the below json in SQL Server 2016:
declare @json nvarchar(max) = '[{"Type":"G","GroupBy":[],
"Attributes":[{"AttributeName":"Class Designation / Compressive Strength"},{"AttributeName":"Size"},{"AttributeName":"Manufacturer"}]}]'
这是我试过但不起作用的查询
This is the query I tried which is not working
select JSON_MODIFY((
select JSON_Query(@json, '$[0].Attributes') as res),'$.AttributeName.Manufacturer', null)
推荐答案
Here is the working solution using the for json and open json. The point is to:
确定您要删除的项目并将其替换为
NULL
.这是由JSON_MODIFY(@json,'$[0].Attributes[2]', null)
完成的.我们只是说,取Attributes
中的第二个元素并将其替换为 null
Identify the item you wish to delete and replace it with
NULL
. This is done byJSON_MODIFY(@json,'$[0].Attributes[2]', null)
. We're simply saying, take the 2nd element inAttributes
and replace it by null
将此数组转换为行集.我们需要以某种方式摆脱这个 null
元素,这是我们可以在 SQL 中通过 where [value] is not null
Convert this array to a row set. We need to somehow get rid of this null
element and that's something we can filter easily in SQL by where [value] is not null
将其全部组装回原始 JSON.这是由 FOR JSON AUTO
Assemble it all back to original JSON. That's done by FOR JSON AUTO
请记住此类 JSON 数据转换的一个重要方面:
JSON 设计用于信息交换或最终存储信息.但是您应该避免在 SQL 级别上进行更复杂的数据操作.
JSON is designed for information exchange or eventually to store the information. But you should avoid more complicated data manipulation on SQL level.
无论如何,这里的解决方案:
Anyway, solution here:
declare @json nvarchar(max) = '[{"Type": "G","GroupBy": [],"Attributes": [{"AttributeName": "Class Designation / Compressive Strength"}, {"AttributeName": "Size"}, {"AttributeName": "Manufacturer"}]}]';
with src as
(
SELECT * FROM OPENJSON(
JSON_Query(
JSON_MODIFY(@json,'$[0].Attributes[2]', null) , '$[0].Attributes'))
)
select JSON_MODIFY(@json,'$[0].Attributes', (
select JSON_VALUE([value], '$.AttributeName') as [AttributeName] from src
where [value] is not null
FOR JSON AUTO
))
这篇关于从openjson SQL Server 2016中的嵌套数组中删除对象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!