SQL server getting data within nested JSON data structure

Question

I have nested JSON file that I am trying to use as data source for reports. I am "flattening" the structure, but not sure how to assess siblings data.

Sample Data:

{
"Cources": [{
        "ID": 1,
        "Name": "MAC100",
        "Room": 100,
        "TAID": 123,
        "StudentsIDs": [
            1, 2
        ]
    }
],

"TAs": [{
    "ID": 123,
    "Name": "Joe",
    "LName": "Smith"
}],

"Students": [{
    "ID": 1,
    "LName": "Clark"
}, {
    "ID": 2,
    "LName": "Peterson"
}]
 }

SQL Server: ingest data file and flatten data for report:

SELECT Cource.ID, 
Cource.Name as CName,
Cource.Room as CRoom,
CourceStudents.LName

FROM OPENROWSET (BULK 'C:\Data\file.json', SINGLE_CLOB) as jsonfile

CROSS APPLY OPENJSON(BulkColumn,'$[0]') WITH( 

   Cources nvarchar(max) AS JSON,
   TAs nvarchar(max) AS JSON,
   Students nvarchar(max) AS JSON

   ) AS [SampleData]

CROSS APPLY OPENJSON(Cources) WITH (
   Room integer,
   Name nvarchar(max),
   StudentsIDs nvarchar(max) AS JSON
)  as [Cources]

CROSS APPLY OPENJSON(Students) WITH ( 
    ID integer,
    LName nvarchar(max),
) as [Students]

CROSS APPLY OPENJSON(StudentsIDs) WITH ( 
 **//??? how to get full data for the student from "Students" joining by the student ID ?**
) as [CourceStudents]

I have stumbled of how to get all student data from "Students" into "StudentIDs" joining by the ID.

Answer 1

I created temp table, imported json data into it, which allowerd me to have WHERE clause:

DROP TABLE IF EXISTS tempdb.dbo.#temp;
CREATE TABLE #temp (
 InfoJson nvarchar(max) 
) 

ALTER TABLE #temp
ADD CONSTRAINT [Content should be formatted as JSON]
 CHECK (ISJSON(InfoJson)> 0)

 Insert INTO #temp (InfoJson)
 SELECT *
 FROM OPENROWSET (BULK 'C:\Data\file.json', SINGLE_CLOB) as j;

SELECT Cource.ID, 
Cource.Name as CName,
Cource.Room as CRoom,
CourceStudents.LName  
FROM #temp

CROSS APPLY OPENJSON(InfoJson,'$') WITH( 
   Cources nvarchar(max) AS JSON,
   TAs nvarchar(max) AS JSON,
   Students nvarchar(max) AS JSON
   ) AS [SampleData]

CROSS APPLY OPENJSON(Cources) WITH (
   Room integer,
   Name nvarchar(max),
   StudentsIDs nvarchar(max) AS JSON
)  as [Cources]

CROSS APPLY OPENJSON(StudentsIDs) WITH (
     value nvarchar(100) '$'
) as [StudentsIDs]

CROSS APPLY OPENJSON(Students) WITH ( 
    ID integer,
    LName nvarchar(max),
) as [Students]

WHERE StudentsIDs.value=Students.ID