10gen: M101 MongoDB for Developers - Homework 5.1 - 5.4

homework5.js

// homework 5.1
db.posts.aggregate([
  {$project: {"_id": 0,
              "comments.author": 1
  }},
  {$unwind: "$comments"},
  {$group: {"_id": "$comments.author",
           sum: {"$sum": 1}
  }},
  {$sort: {"sum": -1}},
])


// homework 5.2
db.zips.aggregate([
  {$group:{
    "_id": {state: "$state",
            city: "$city"},
    pop: {"$sum": "$pop"}
  }},
  {$match:{"pop":{"$gt":25000}, "_id.state": {"$in": ["CA", "NY"]}}},
  {$group:{
    "_id":null,
    avg: {"$avg": "$pop"}
  }}  
])

// homework 5.3
db.grades.aggregate([
  {$unwind:"$scores"},
  {$match:{"scores.type":{"$ne": "quiz"}}},
  {$group:{"_id": {class_id: "$class_id",
            student_id: "$student_id"},
    avg_per_student: {"$avg": "$scores.score"}
  }},
  {$group:{
    "_id": "$_id.class_id",
    avg: {"$avg": "$avg_per_student"}
  }},
  {$sort: {"avg": -1}}
])


// homework 5.4
db.zips.aggregate([
    {$project: 
     {
	     first_char: {$substr : ["$city",0,1]},
	     pop:1
     }},
     {$match:{"first_char":{"$regex": "[0-9]"}}},
     {$group:{
       "_id":null,
       sum: {"$sum": "$pop"}
     }}
])

Sorry, @hardikbarot. I didn't receive any email about your comment. Did you solve the problem?

ans for 5.2

right one: db.posts.aggregate([{$project: {"_id": 0,"comments": 1}},{$unwind: "$comments"},{$group: {"_id": "$comments.author","num_comments": {$sum: 1}}},{$sort: {"num_comments": -1}},{$limit:1}])

Thanks a lot for sharing!

Hi all, about exercise homework 5.2;
I have a other answer maybe enhance a little performance and I want to share it for everybody
db.zips.aggregate([
{$match:{$or: [{"state":{$eq:"CA"}},{"state":{$eq:"NY"}}]}},
{$group:{_id:{"state":"$state","city":"$city"},sum:{$sum:"$pop"}}},
{$match:{"sum":{$gte:25000}}},
{$group:{_id:null,avgPop:{$avg:"$sum"}}}
])

5.4 :: Latest answer is : 76394871

db.zips.aggregate([
{ $project: { _id: 0, city: 1, pop: 1 } },
{ $match: { city: /^(B|D|O|G|N|M).*/ } },
{ $group: { _id: null, pop: { $sum: "$pop" } } },
{ $sort: { city: 1} }
])