[SQL] Parsing Nested JSON without headers into SQL Database

I have a nested JSON without headers and am not able to parse it using SQL. I'm trying to get this data into a SQL table. Here's the sample of the data

[[123,1123,"94.05",[[11234,31040,"100.00",[[112345,252783,20,"Pass","YES"],[61169237,252785,5,"Pass","YES"],[61169238,252788,5,"Pass","YES"]]]]]]

Here's my query. I'm able to parse the first part of the JSON but not the nested ones. How do I reference the nested JSONs since they dont have headers.

SELECT m.[udAuditID]
,m.[assessmentID]
,m.category_json
,s.CatID
,s.CategoryID
,s.Score
,s.SecID
,s.SectionID
FROM [dbo].auditfinal m
CROSS APPLY OPENJSON(m.category_json) WITH (
CatID BIGINT '$[0]',
CategoryID BIGINT '$[1]',
Score VARCHAR(max)'$[2]',
SecID BIGINT '$[0][0]',
SectionID BIGINT '$[0][1]'
) s


As you can see in the results below, I'm able to parse the first set shown in yellow, but not beyond that.

enter image description here

Any help or suggestion you can give is appreciated!

Continue reading...