How to insert nested JSON array into SQL Server table

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:UsersofiriOneDriveDesktopאופירBWRData for testingchevrolet_spark json files3.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?

Answer

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:

ID
7120
4816
6088

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