I'm working with an API that spits out a data in the form of dynamic json i.e. the location of some elements of json objects is not fixed, this leads to the path some elements in json arrays being dynamic. For example in the JSON object below the "Property" array under the can fall under n-level of nesting. Does OPENJSON has any search/lookup feature that can find the location of array with the name "Property" & just print out the elements into the table?
Here's the working model but I have to use forced index ('$.rows[2]') for the array to show the desired result. However the Property array can be anywhere.
JSON
Declare @Jsonobj as nvarchar(max)
Select @Jsonobj = N'{
"ID": "StudentInformation",
"Name": "Student Information",
"Type": "s_info",
"Details": [
"Student Information",
"Greendale Community College"
],
"Date": "21 October 2021",
"Rows": [
{
"RowType": "Header",
"Cells": [
{
"Value": ""
},
{
"Value": "21 Feb 2021"
},
{
"Value": "22 Aug 2020"
}
]
},
{
"RowType": "Section",
"Title": "Class",
"Rows": []
},
{
"RowType": "Section",
"Title": "Grade",
"Rows": [
{
"RowType": "Row",
"Cells": [
{
"Value": "5A",
"Property": [
{
"Id": "1",
"Value": "John Smith"
}
]
},
{
"Value": "5A",
"Property": [
{
"Id": "2",
"Value": "Jane Doe"
}
]
},
{
"Value": "5B",
"Property": [
{
"Id": "1",
"Value": "Ben Frank"
}
]
}
]
}
]
}
]
}'
SQL
SELECT JSON_VALUE(v.value, 'strict $.Value') as Names
FROM OPENJSON(@Jsonobj, 'strict $.Rows[2].Rows') c
CROSS APPLY OPENJSON(c.value, 'strict $.Cells') p
CROSS APPLY OPENJSON(p.value, 'strict $.Property') v
Here's what I found that works:
SELECT d.value Names
FROM OPENJSON(@Jsonobj,'$.Rows')
WITH([Rows] NVARCHAR(MAX) AS JSON) A
OUTER APPLY OPENJSON(A.[Rows]) B
OUTER APPLY OPENJSON(B.[value],'$.Cells') C
OUTER APPLY OPENJSON(c.[value],'$.Property') WITH (Value NVARCHAR(MAX)) d
where d.value is not null