MongoDB Exercise - Find the top 5 restaurants for each cuisine type, along with their average score
Write a MongoDB query to find the top 5 restaurants with the highest average score for each cuisine type, along with their average scores.
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"},
{$group: {
_id: {cuisine: "$cuisine", restaurant_id: "$restaurant_id"},
avgScore: {$avg: "$grades.score"}
}},
{$sort: {
"_id.cuisine": 1,
avgScore: -1
}},
{$group: {
_id: "$_id.cuisine",
topRestaurants: {$push: {restaurant_id: "$_id.restaurant_id", avgScore: "$avgScore"}}
}},
{$project: {
_id: 0,
cuisine: "$_id",
topRestaurants: {$slice: ["$topRestaurants", 5]}
}}
])
Output:
{ cuisine: 'Bagels/Pretzels', topRestaurants: [ { restaurant_id: '40396464', avgScore: 20.2 }, { restaurant_id: '40363565', avgScore: 19.166666666666668 }, { restaurant_id: '40667700', avgScore: 14.5 }, { restaurant_id: '40759924', avgScore: 14.4 }, { restaurant_id: '40392339', avgScore: 14.285714285714286 } ] }, { cuisine: 'Latin (Cuban, Dominican, Puerto Rican, South & Central American)', topRestaurants: [ { restaurant_id: '40596377', avgScore: 26.666666666666668 }, { restaurant_id: '40582271', avgScore: 25.285714285714285 }, { restaurant_id: '40393688', avgScore: 22.625 }, { restaurant_id: '40791454', avgScore: 21.5 }, { restaurant_id: '40743578', avgScore: 21.2 } ] }, { cuisine: 'Vietnamese/Cambodian/Malaysia', topRestaurants: [ { restaurant_id: '40700664', avgScore: 27.833333333333332 }, { restaurant_id: '40578058', avgScore: 15.2 }, { restaurant_id: '40751226', avgScore: 13.833333333333334 }, { restaurant_id: '40559606', avgScore: 8.6 } ] }, .....
Explanation:
The given query in MongoDB that finds the top 5 restaurants with the highest average score for each cuisine type.
The $unwind stage is used to create a separate document for each element in the grades array.
The $group stage is used to group the documents by cuisine and restaurant_id, and calculate the average score using the $avg aggregation operator.
The _id field in the $group stage is a composite key that consists of the cuisine and restaurant_id fields.
The $sort sorts the documents by cuisine and average score in descending order.
The $group stage is used again to group the documents by cuisine and create an array of the top 5 restaurants for each cuisine using the $push aggregation operator.
The $slice operator limits 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 restaurant with the most recent grade date.
Next: Each borough's top 5 restaurants with the most "A" grades.
What is the difficulty level of this exercise?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics