I have JSON like below. and I want to find all models from the JSON.
When I give below SQL query it gives me only Sedan models
DECLARE @PermsJSON NVARCHAR(MAX) =<json>;
select * from OPENJSON(@PermsJSON, '$.Models[0].Make')
But, when I gave below its giving null (removing [0]).
DECLARE @PermsJSON NVARCHAR(MAX) =<json>;
select * from OPENJSON(@PermsJSON, '$.Models.Make')
What should I do if I want to get list of Models irrespective of Make and Models?
Something like: "Corolla", "Camry", "Civic", "Santa", "Tucson", "Bronco"
{
"Root": "Vehicles",
"Models": [
{
"Type": "Sedan",
"Make": [
{
"color": "Red",
"name": "Toyota",
"Models": [
"Corolla",
"Camry"
]
},
{
"color": "Blue",
"Make": "Honda",
"Models": [
"Civic"
]
}
]
},
{
"Type": "SUV",
"Make": [
{
"color": "White",
"name": "Hyundai",
"Models": [
"Santa",
"Tucson"
]
},
{
"color": "Black",
"Make": "Ford",
"Models": [
"Bronco"
]
}
]
}
]
}
Since your values are part of individual nested arrays, perhaps this will give you a nudge
Example
Select C.Value
From OpenJSON(@JSON ,'$.Models' ) A
Cross Apply OpenJSON(A.value,'$.Make' ) B
Cross Apply OpenJSON(B.value,'$.Models' ) C
Results
Value
Corolla
Camry
Civic
Santa
Tucson
Bronco