I am trying to write a query by performing an aggregate on one of its properties that is an array of objects. As an example in the below json structure I want the country and the biggest airport as two columns in the output
[
{
"Country": "US",
"Airports": [
{
"Name": "Kodiak Airport",
"Area": "100"
},
{
"Name": "Homer Airport",
"Area": "87"
}
]
},
{
"Country": "Mexico",
"Airports": [
{
"Name": "Gulfport-Biloxi International Airport",
"Area": "94"
},
{
"Name": "El Paso International Airport",
"Area": "68"
}
]
}
]
so the reuslt will be 2 columns, country name and biggest airport's name as below:
Country Airport
US Kodiak Airport
Mexico Gulfport-Biloxi International Airport.
The following query returns country and the first airport's name in the array airports_s.
MyLogs_CL
| project country_s, Airports = todynamic(airports_s)
| project country_s, Airports[0].name
But I don't how to perform an aggregate on that array and return the object which has the highest area among them.