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.
What is the difficulty level of this exercise?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics