I am new to mongoDB and I came across a problem. The data was imported according to official doc.
As you can see, each of restaurant has a grades array and the nested document contains a score field. What I want to do is according to the average score of each restaurant's grades and retrive the restaurant that has the top average score. This may need to use mongoDB's aggregate method but the document doesn't cover the nested document situation and I googled but without a result. There's a similar question on this site but it's not so clear.
[
{ "_id" : ObjectId("56a9f39cae1902590811dffc"),
"address" : { "building" : "284",
"coord" : [ -73.9829239, 40.6580753 ],
"street" : "Prospect Park West",
"zipcode" : "11215" },
"borough" : "Brooklyn",
"cuisine" : "American ",
"grades" : [ { "date" : ISODate("2014-11-19T00:00:00Z"), "grade" : "A", "score" : 11 },
{ "date" : ISODate("2013-11-14T00:00:00Z"), "grade" : "A", "score" : 2 },
{ "date" : ISODate("2012-12-05T00:00:00Z"), "grade" : "A", "score" : 13 },
{ "date" : ISODate("2012-05-17T00:00:00Z"), "grade" : "A", "score" : 11 } ],
"name" : "The Movable Feast",
"restaurant_id" : "40361606" },
...
]
Using the mongo shell, try the below , change "collecttionname" to the name of your your restaurant collections
db.collectionname.aggregate( { '$unwind' : '$grades' } , { '$group' : { '_id' : '$_id' , 'average' : { $avg : '$grades.score' } } } , { '$sort' : { 'average' : -1 } } , { '$limit' : 1 } )