TSQL更新

Question

我被困在更新/插入来自JSON数组行到SQL Server 2017

我有以下结构：

{ 
    "ID":52, 
    "Name":"Mark", 
    "Surname":"Blake", 
    "Age": 24 
    "Cars":[ 
     {"ID":110,"Volvo":"2001-10-01","Color":"red"}, 
     {"ID":110,"Volvo":"2001-10-01","Color":"red"}, 
     {"ID":110,"Volvo":"2001-10-01","Color":"red"}, 
     {"ID":-1,"Volvo":"2001-10-01","Color":"red"}, 
     {"ID":-1,"Volvo":"2001-10-01","Color":"red"}, 
    ] 
} 

我试图做一个合并基础上Cars数组，当ID为-1时插入到表中，否则更新。

如何实现这一目标？

好的，我做到了，但我又遇到了另一个问题。 我想从更高的层次也得到ID（我的意思是52在这种情况下），它应该是这样的：

110 52 2001-10-01 red 
110 52 2001-10-01 red 
110 52 2001-10-01 red 
-1 52 2001-10-01 red 
-1 52 2001-10-01 red 

Answer 1

我定你JSON，这是一个有点不正确。

DECLARE @json nvarchar(max) = '{ 
    "ID":52, 
    "Name":"Mark", 
    "Surname":"Blake", 
    "Age": 24, 
    "Cars":[ 
     {"ID":110,"Volvo":"2001-10-01","Color":"red"}, 
     {"ID":110,"Volvo":"2001-10-01","Color":"red"}, 
     {"ID":110,"Volvo":"2001-10-01","Color":"red"}, 
     {"ID":-1,"Volvo":"2001-10-01","Color":"red"}, 
     {"ID":-1,"Volvo":"2001-10-01","Color":"red"} 
    ] 
}' 


SELECT JSON_VALUE(t.[value], N'$.ID'), 
     JSON_VALUE(t.[value], N'$.Volvo'), 
     JSON_VALUE(t.[value], N'$.Color') 
FROM OPENJSON(@json, N'$.Cars') as t 

输出：

110 2001-10-01 red 
110 2001-10-01 red 
110 2001-10-01 red 
-1 2001-10-01 red 
-1 2001-10-01 red 

将在CTE或临时表，然后合并主表。或者你可以先INSERT（WHERE ID = -1）和UPDATE（WHERE ID != -1）

更新：

SELECT f.[value], 
     JSON_VALUE(c.[value], N'$.ID'), 
     JSON_VALUE(c.[value], N'$.Volvo'), 
     JSON_VALUE(c.[value], N'$.Color') 
FROM OPENJSON(@json) f 
CROSS APPLY OPENJSON(@json,N'$.Cars') c 
WHERE f.[key] IN ('ID') 

输出：

52 110 2001-10-01 red 
52 110 2001-10-01 red 
52 110 2001-10-01 red 
52 -1 2001-10-01 red 
52 -1 2001-10-01 red