如何使用T-SQL更新对象数组中的插入JSON属性 [英] How to update insert JSON property in an array of object with T-SQL
问题描述
我试图找到一种有效的方法来将属性插入现有的json数组中,而不必按索引进行操作。例如说我们有:
I am trying to find an effective way to insert a property into an existing json array with out having to do it by index. For example say we had:
DECLARE @json NVARCHAR(MAX);
SET @json = N'
{
"objs":[
{"id":1},
{"id":2}
]
}
'
如何为每个对象添加属性在数组中?我想做的是这样的:
How do I add a property to each object in the array? What I would like to do is something like this:
JSON_MODIFY(@json,'$.objs[].parent_id',1);
但这不起作用,因为我没有提供数组索引。我敢肯定有一个简单的解决方案,但是我在文档中找不到。
But this does not work because I did not provide an array index. I am sure there is a simple solution to this, but I could not find one in the docs.
推荐答案
如果使用SQL在Server 2017+中,您可以将 JSON_MODIFY()
与表达式一起用作路径,如文档中所述:
If you use SQL Server 2017+, you may use JSON_MODIFY()
with an expression as path, as is explained in the documentation:
在SQL Server 2017(14.x)和Azure SQL数据库中,可以提供
变量作为path的值。
In SQL Server 2017 (14.x) and in Azure SQL Database, you can provide a variable as the value of path.
JSON:
DECLARE @json NVARCHAR(MAX);
SET @json = N'{
"objs":[
{
"id":1
},
{
"id":2
}
]
}';
声明:
SELECT @json = JSON_MODIFY(@json, CONCAT('$.objs[', [key], '].parent_id'), 1)
FROM OPENJSON(@json, '$.objs')
结果:
{
"objs":[
{
"id":1,
"parent_id":1
},
{
"id":2,
"parent_id":1
}
]
}
这篇关于如何使用T-SQL更新对象数组中的插入JSON属性的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!