CODEWITHSUNDEEP
azureazure-cosmosdbazure-cosmosdb-sqlapi
met.lord
met.lord

Reputation: 618

Cosmos Db: How to query for the maximum value of a property in an array of arrays?

I'm not sure how to query when using CosmosDb as I'm used to SQL. My question is about how to get the maximum value of a property in an array of arrays. I've been trying subqueries so far but apparently I don't understand very well how they work.

In an structure such as the one below, how do I query the city with more population among all states using the Data Explorer in Azure:

{
    "id": 1,
    "states": [
        {
            "name": "New York",
            "cities": [
                {
                    "name": "New York",
                    "population": 8500000
                },
                {
                    "name": "Hempstead",
                    "population": 750000
                },
                {
                    "name": "Brookhaven",
                    "population": 500000
                }           
            ]
        },
        {
            "name": "California",
            "cities":[
                {
                    "name": "Los Angeles",
                    "population": 4000000
                },
                {
                    "name": "San Diego",
                    "population": 1400000
                },
                {
                    "name": "San Jose",
                    "population": 1000000
                }
            ]
        }
    ]
}

Upvotes: 0

Views: 1730

Answers (1)

Alex
Alex

Reputation: 18546

This is currently not possible as far as I know.

It would look a bit like this:

SELECT TOP 1 state.name as stateName, city.name as cityName, city.population FROM c
join state in c.states
join city in state.cities
--order by city.population desc <-- this does not work in this case

You could write a user defined function that will allow you to write the query you probably expect, similar to this: CosmosDB sort results by a value into an array

The result could look like:

SELECT c.name, udf.OnlyMaxPop(c.states) FROM c

function OnlyMaxPop(states){
    function compareStates(stateA,stateB){  
       stateB.cities[0].poplulation - stateA.cities[0].population;
  }
  
  onlywithOneCity = states.map(s => {
    maxpop = Math.max.apply(Math, s.cities.map(o => o.population));
    return {
      name: s.name,
      cities: s.cities.filter(x => x.population === maxpop)
    }
  });
  
    return onlywithOneCity.sort(compareStates)[0];
}

You would probably need to adapt the function to your exact query needs, but I am not certain what your desired result would look like.

Upvotes: 2

Related Questions