w3resource

MongoDB Exercise - Find the average score for each borough


Write a MongoDB query to find the average score for each borough.

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: "$borough", avgScore: { $avg: "$grades.score" } } }
])

Output:

{ _id: 'Queens', avgScore: 11.603178953137846 },
{ _id: 'Manhattan', avgScore: 11.375442086648983 },
{ _id: 'Brooklyn', avgScore: 11.492063492063492 },
{ _id: 'Staten Island', avgScore: 11.142215568862275 },
{ _id: 'Bronx', avgScore: 11.30313124583611 }

Explanation:

The said query in MongoDB which groups the documents by borough and calculate the average score for each group, and then sort the results in descending order by the avgScore field.

The $unwind operator is used to flatten the grades array,

and creates a new document for each element in the array. Then, it groups the documents by borough and calculates the average score for each group using the $avg operator on the flattened grades.score field.

Note: This output is generated using MongoDB server version 3.6

Improve this sample solution and post your code through Disqus.

Previous: Find the lowest score for each cuisine.
Next: Find the highest score for each borough.

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/mongodb-exercises/mongodb-exercise-64.php