TSQL updating from JSON array

Question

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

gofr1 · Accepted Answer · 2017-11-30 10:19:35Z

1

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

edited Nov 30, 2017 at 10:19

answered Oct 20, 2017 at 12:28

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tylkonachwile Over a year ago

I did it the same just few minutes ago, but I've got a 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

tylkonachwile Over a year ago

Do you have an idea how to achieve this? I've edited my post. Thanks for help!

gofr1 Over a year ago

If you JSON will not have more than 1 datapart you can simply add JSON_VALUE(@json,N'$.ID')

Collectives™ on Stack Overflow

TSQL updating from JSON array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related