I need a help for some advanced collection sorting in mongodb. Suppose that we have the database local and we have the following model:
collection continent with documents {"_id":1,"name":"Europe"},{"_id":2,"name":"Asia"},{"_id":3,"name":"North America"}, {"_id":4,"name":"South America"},{"_id":5,"name":"Australia"},{"_id":6,"name":"Africa"}
collection country with documents {"_id":1,"name":"France","populationInMillions":66,"continent":DBRef("continent",1,"local"),"cities":[{"name":"Paris"},{"name":"Marseille"},{"name":"Toulouse"}]},
{"_id":2,"name":"Spain","populationInMillions":47,"continent":DBRef("continent",1,"local"),"cities":[{"name":"Madrid"},{"name":"Seville"},{"name":"Valencia"}]},
{"_id":3,"name":"China","populationInMillions":1360,"continent":DBRef("continent",2,"local"),"cities":[{"name":"Beijing"},{"name":"Chongqing"},{"name":"Shanghai"}]},
{"_id":4,"name":"Brazil","populationInMillions":200,"continent":DBRef("continent",4,"local"),"cities":[{"name":"Sao Paulo"},{"name":"Rio de Janeiro"},{"name":"Salvador"}]}
So when we want to sort countries by some simple criteria like populationInMillions descending, we will use query: db.country.find({}).sort({ populationInMillions:-1 })
My question is what if we want to sort by some complex criteria like some of the following (Some of them in this example make sense in the real world context, some make not sense, but the focus is on the technical solution. I have to apply similar solutions in a real world project.)
Sort countries: 1. by the name of their continent (consider that we don't have subobject but DBRef)
- in a way that the countries with populationInMillions bigger than 1000 are located before the other countries
- by the total number of characters in all the names of its cities (example for france : Paris (5 chars), Marseille (9 chars), Toulouse (8 chars) - total 22 chars)
- alphabetically by the name of the second city of a country (in this example Marseille for France, Seville for Spain and so on)
If you have answer for all or some of those questions, please help. Thank you in advance!