Mongo DB query for combining sorting and grouping

Question

Let's say I have a collection with the following (dummy) data:

Country	State	Population Density (people per km^2)	Cases (in millions)
USA	New York	161	1.03
USA	California	95	4.47
Germany	Berlin	4,227	0.19
India	Kerala	859	4.09
India	Karnataka	319	2.95
India	Maharashtra	370	6.47

What is an optimized mongodb query to get results grouped by country and sorted by the count of states, also each group should contain states sorted by 'Cases'?

The results should look like this in JSON

{
  'results': [
      {
         'country'   : 'India',
         'num_states': 3,
         'states': [
              {
                 'State': 'Maharashtra',
                 'Cases': 6.47,
                 'PPD'  : 370,
              },
              {
                 'State': 'Kerala',
                 'Cases': 4.09,
                 'PPD'  : 859,
              },
              {
                 'State': 'Karnataka',
                 'Cases': 2.95,
                 'PPD'  : 319,
              }
          ]
      },
      {
         'country'   : 'USA',
         'num_states': 2,
         'states': [
              {
                 'State': 'California',
                 'Cases': 4.47,
                 'PPD'  : 95,
              },
              {
                 'State': 'New York',
                 'Cases': 1.03,
                 'PPD'  : 161,
              }
          ]
      },
      {
         'country'   : 'Germany',
         'num_states': 1,
         'states': [
              {
                 'State': 'Berlin',
                 'Cases': 0.19,
                 'PPD'  : 4227,
              }
          ]
      },
  ]
}

Note: My actual data is different, but the use case is the same.

mohammad Naimi · Accepted Answer · 2021-09-02 11:28:49Z

1

first sort by cases -1 then group by country add new field for sorting and project

db.collection.aggregate(
[
  {
    '$sort': {
      'Cases': -1
    }
  }, {
    '$group': {
      '_id': {
        'country': '$country'
      }, 
      'num_states': {
        '$sum': 1
      }, 
      'states': {
        '$push': {
          'states': '$$ROOT.state', 
          'cases': '$$ROOT.Cases', 
          'ppd': '$$ROOT.Density'
        }
      }
    }
  }, {
    '$addFields': {
      'sorf': {
        '$max': '$states.cases'
      }
    }
  }, {
    '$sort': {
      'sorf': -1
    }
  }, {
    '$project': {
      'country': '$_id.country', 
      'num_states': '$num_states', 
      'states': 1, 
      '_id': 0
    }
  }
])

edited Sep 2, 2021 at 11:28

answered Sep 2, 2021 at 10:26

mohammad Naimi

2,3591 gold badge7 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Vaibhav Thakkar Over a year ago

If we group after sorting, then the initial sort order is lost: stackoverflow.com/questions/14513185/…

mohammad Naimi Over a year ago

so the initial sorting works for in array and for global sorting, I use $addFields and then sort the whole docs with sorf field

Vaibhav Thakkar Over a year ago

just tested it on mongo-playground, initial sort does not work

mohammad Naimi Over a year ago

and the about last sort ?

Vaibhav Thakkar Over a year ago

the last sort is sorting the countries, but not the states inside them

solanki bhargav · Accepted Answer · 2021-09-02 12:55:05Z

$sort - sort by cases in descending order
$group - group by country and construct an array of states, and get total count of states
$sort - sort by number of states in descending order
$project - rename the required fields

db.collection.aggregate([
  {
    "$sort": {
      "cases": -1
    }
  },
  {
    $group: {
      _id: "$country",
      num_states: {
        $sum: 1
      },
      states: {
        $push: {
          state: "$state",
          cases: "$cases",
          PPD: "$population"
        }
      }
    }
  },
  {
    "$sort": {
      "num_states": -1
    }
  },
  {
    $project: {
      country: "$_id",
      num_states: 1,
      states: 1,
      _id: 0
    }
  }
])

Mongo Playgroud

Collectives™ on Stack Overflow

Mongo DB query for combining sorting and grouping

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related