MongoDB Exercise - Find those restaurants with the highest number of "A" grades

Write a MongoDB query to find all the restaurants with the highest number of "A" grades.

Structure of 'restaurants' collection :

{
  "address": {
     "building": "1007",
     "coord": [ -73.856077, 40.848447 ],
     "street": "Morris Park Ave",
     "zipcode": "10462"
  },
  "borough": "Bronx",
  "cuisine": "Bakery",
  "grades": [
     { "date": { "$date": 1393804800000 }, "grade": "A", "score": 2 },
     { "date": { "$date": 1378857600000 }, "grade": "A", "score": 6 },
     { "date": { "$date": 1358985600000 }, "grade": "A", "score": 10 },
     { "date": { "$date": 1322006400000 }, "grade": "A", "score": 9 },
     { "date": { "$date": 1299715200000 }, "grade": "B", "score": 14 }
  ],
  "name": "Morris Park Bake Shop",
  "restaurant_id": "30075445"
}

Query:

db.restaurants.aggregate([
  {$unwind: "$grades"},
  {$match: {"grades.grade": "A"}},
  {$group: {
    _id: "$restaurant_id",
count: {$sum: 1}
  }},
  {$sort: {count: -1}},
  {$group: {
    _id: "$count",
restaurants: {$push: "$_id"}
  }},
  {$sort: {_id: -1}},
  {$limit: 1},
  {$project: {restaurants: 1}}
])

Output:

{
    _id: 7,
restaurants: [
      '40873102', '40752011', '40787054',
      '40513021', '40514492', '40720393',
      '40399340', '40816235', '40786301',
      '40863575', '40831075', '40868810',
      '40377294', '40735660', '40362432',
      '40798457', '40397159', '40401093',
      '40864834', '40393093', '40573964',
      '40399986', '40399778', '40761780',
      '40401151', '40606772', '40369087',
      '40865501'
    ]
  }

Explanation:

The said query in MongoDB returns an array of restaurant IDs for all the restaurants with the highest number of "A" grades.

The $unwind stage that split each document of the grades array into multiple documents.

The $match operator filters the resulting documents by the grades.grade field value of "A".

The $group groups the restaurant_id field, and a new field called count is created using the $sum aggregation operator to count the number of "A" grades of each restaurant.

The $sort operator sorts the resulting documents in descending order by the count field.

The $group groups again the count field, and a new field called restaurants is created using the $push aggregation operator to create an array of restaurant IDs for each count value.

The $sort operator again sorts the resulting documents in descending order by the _id field.

The $limit operator only returns the first document of group of restaurants with the highest count of "A" grades.

Note: This output is generated using MongoDB server version 3.6

Improve this sample solution and post your code through Disqus.

Previous: Find out which restaurants have the word "coffee" in their names.
Next: Find the cuisine type that is most likely to receive a "C" grade.