How to insert nested JSON array into SQL Server table

Question

I am trying to insert a JSON file into a table using SQL Server's "OPENJSON WITH (..." syntax). However, this file contains nested arrays, which I do not know how to handle.

Here is my JSON file:

}
    "Person_ID":["7120","4816","6088"],
    "Occupant_Type":["ADT","SCD","MCD"],
    "Occupant_Gender":["M","F","M"],
    "Occupant_Height":[180,102,127],
    "Occupant_Weight":[68,20,22],
    "Occupant_Age":[23,2.5,5.5],
    "Occupied_Region":[],
    "Occupant_Type_Region":[]
}

and here is the code I tried to use:

DECLARE @test_data varchar(max)

SELECT @test_data = BulkColumn
FROM OPENROWSET (BULK 'C:\Users\ofiri\OneDrive\Desktop\אופיר\BWR\Data for testing\chevrolet_spark json files\03.03.2020 copy14', SINGLE_CLOB) import 


insert into Person1([ID])
select [ID]
from openjson(@test_data,'$."Person_ID"')
with(
    [ID] VARCHAR '$."Person_ID"'
)

cross apply 

openjson (@test_data,'$."Occupant_Type"')

But after I run the code, all the attributes in the table are null.

How can I insert the values into my table?

Does this JSON contain data for 3 different persons? What is the logic for parsing the JSON data? — Zhorov
– Zhorov, Commented Mar 27, 2020 at 13:44

Zhorov · Accepted Answer · 2020-03-28 15:55:31Z

The syntax might be different and depends on the expected results. Of course, the destination table structure is also important. Note, that the JSON in the question has a typing error. The correct JSON is:

DECLARE @json nvarchar(max) = N'
{
    "Person_ID":["7120","4816","6088"],
    "Occupant_Type":["ADT","SCD","MCD"],
    "Occupant_Gender":["M","F","M"],
    "Occupant_Height":[180,102,127],
    "Occupant_Weight":[68,20,22],
    "Occupant_Age":[23,2.5,5.5],
    "Occupied_Region":[],
    "Occupant_Type_Region":[]
}'

The error in your OPENSON() call is the wrong path - $.Person_ID instead of $. If you want to parse each nested JSON array separately, you should use OPENJSON() with explicit schema and the correct statement is:

SELECT [ID]
FROM OPENJSON (@json, '$.Person_ID') WITH ([ID] varchar(4) '$')

and the result is:

But if the JSON holds the information for different persons, you should probably use OPENJSON() with default schema and a statement like the following:

SELECT 
   j1.[value] AS Person_ID, 
   j2.[value] AS Occupant_Type, 
   j3.[value] AS Occupant_Gender,
   j4.[value] AS Occupant_Height,
   j5.[value] AS Occupant_Weight,
   j6.[value] AS Occupant_Age,
   j7.[value] AS Occupied_Region,
   j8.[value] AS Occupant_Type_Region
FROM OPENJSON(@json, '$."Person_ID"') j1
FULL JOIN OPENJSON(@json, '$."Occupant_Type"') j2 ON j1.[key] = j2.[key]
FULL JOIN OPENJSON(@json, '$."Occupant_Gender"') j3 ON j1.[key] = j3.[key]
FULL JOIN OPENJSON(@json, '$."Occupant_Height"') j4 ON j1.[key] = j4.[key]
FULL JOIN OPENJSON(@json, '$."Occupant_Weight"') j5 ON j1.[key] = j5.[key]
FULL JOIN OPENJSON(@json, '$."Occupant_Age"') j6 ON j1.[key] = j6.[key]
FULL JOIN OPENJSON(@json, '$."Occupied_Region"') j7 ON j1.[key] = j7.[key]
FULL JOIN OPENJSON(@json, '$."Occupant_Type_Region"') j8 ON j1.[key] = j8.[key]

with result:

Person_ID   Occupant_Type Occupant_Gender Occupant_Height Occupant_Weight Occupant_Age Occupied_Region Occupant_Type_Region
7120        ADT           M               180             68              23           
4816        SCD           F               102             20              2.5          
6088        MCD           M               127             22              5.5

Collectives™ on Stack Overflow

How to insert nested JSON array into SQL Server table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related