CODEWITHSUNDEEP
sql-servert-sql
tylkonachwile
tylkonachwile

Reputation: 2267

TSQL updating from JSON array

I am stuck on updating/inserting rows from json array into SQL Server 2017.

I have the following structure:

{
    "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"},
    ]
}

I am trying to do a merge basing on Cars array, when ID is -1 then insert to table, else update.

How to achieve this?

OK, I did it, but I've got an another problem. I'd like to get also ID from higher level (I mean 52 in this case), it should looks like:

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

Upvotes: 1

Views: 365

Answers (1)

gofr1
gofr1

Reputation: 15977

I fixed you JSON, it was a bit incorrect.

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

Output:

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

Put that in CTE or temp table, then MERGE the primary table. Or you can at first INSERT (WHERE ID = -1) and UPDATE (WHERE ID != -1)

Update:

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')

Output:

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

Upvotes: 1

Related Questions