MongoDB Exercise - Each borough's top 5 restaurants with the most "A" grades

Write a MongoDB query to find the top 5 restaurants in each borough 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: {borough: "$borough", restaurant_id: "$restaurant_id"},
gradeCount: {$sum: 1}
  }},
  {$sort: {
    "_id.borough": 1,
gradeCount: -1
  }},
  {$group: {
    _id: "$_id.borough",
topRestaurants: {$push: {restaurant_id: "$_id.restaurant_id", gradeCount: "$gradeCount"}}
  }},
  {$project: {
    _id: 0,
borough: "$_id",
topRestaurants: {$slice: ["$topRestaurants", 5]}
  }}
])

Output:

{
borough: 'Bronx',
topRestaurants: [
{ restaurant_id: '40863575', gradeCount: 7 },
{ restaurant_id: '40369087', gradeCount: 7 },
{ restaurant_id: '40399778', gradeCount: 7 },
{ restaurant_id: '40738028', gradeCount: 6 },
{ restaurant_id: '40607093', gradeCount: 6 }
    ]
  },
  {
borough: 'Manhattan',
topRestaurants: [
{ restaurant_id: '40752011', gradeCount: 7 },
{ restaurant_id: '40798457', gradeCount: 7 },
{ restaurant_id: '40873102', gradeCount: 7 },
{ restaurant_id: '40401093', gradeCount: 7 },
{ restaurant_id: '40735660', gradeCount: 7 }
    ]
  },
  {
borough: 'Queens',
topRestaurants: [
{ restaurant_id: '40362432', gradeCount: 7 },
{ restaurant_id: '40816235', gradeCount: 7 },
{ restaurant_id: '40787054', gradeCount: 7 },
{ restaurant_id: '40393093', gradeCount: 7 },
{ restaurant_id: '40786301', gradeCount: 7 }
    ]
  },

.....

Explanation:

The said query in MongoDB that finds the top 5 restaurants in each borough with the highest number of "A" grades.

The $unwind stage creates a separate document for each element in the grades array.

The $match stage filters the documents with grades that have a grade value of "A".

The $group operator groups the documents by borough and restaurant_id, and count the number of grades using the $sum aggregation operator.

The _id field in the $group stage is a composite key that consists of the borough and restaurant_id fields.

The $sort stage sorts the documents by borough and grade count in descending order.

The $group stage again groups the documents by borough and create an array of the top 5 restaurants for each borough using the $push aggregation operator.

The $slice operator is used to limit the size of the topRestaurants array to 5.

Note: This output is generated using MongoDB server version 3.6

Improve this sample solution and post your code through Disqus.

Previous: Find the top 5 restaurants for each cuisine type, along with their average score.
Next: Determine which borough has the most restaurants scoring 90 or higher and receiving an "A".