更新时间:2023-02-04 21:30:07
如果使用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
}
]
}