m101 - Mongo HW 5

5.1.js

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

5.2.js

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

5.3.js

db.grades.aggregate([
  {
    "$match": {
      "scores.type": {
        "$in": ["exam", "homework"]
      }
    }
  },
  {
    "$unwind": "$scores"
  },
  {
    "$group": {
      _id: {"student_id": "$student_id", "class_id": "$class_id"},
      sGPA: {"$avg": "$scores.score"}
    }
  },
  {
    "$group": {
      _id: {"class_id": "$_id.class_id"},
      cGPA: {"$avg": "$sGPA"}
    }
  },
  {
    "$sort": {
      cGPA: -1
    }
  }
])

5.4.js

db.zips.aggregate([
  {
    "$project": {
      "fc": {"$substr": ["$city", 0, 1]},
      "city": 1,
      "pop": 1
    }
  },
  {
    "$match": {
      "fc": {"$in": ["0", "1", "2", "3", "4", "5", "6", "7", "8", "9"]}
    }
  },
  {
    "$group": {
      _id: null,
      pop: {"$sum": "$pop"}
    }
  }
])

5.3.js change:

db.grades.aggregate([
{
"$unwind": "$scores"
},
{
"$match": {
"scores.type": {
"$in": ["exam", "homework"]
}
}
},
{
"$group": {
_id: {"student_id": "$student_id", "class_id": "$class_id"},
sGPA: {"$avg": "$scores.score"}
}
},
{
"$group": {
_id: {"class_id": "$_id.class_id"},
cGPA: {"$avg": "$sGPA"}
}
}
,
{"$sort":{cGPA:-1}}
])

Have to unwind the scores prior so you can correctly match the exam.

5.2
the last $group using "$_id.state"

{"$group":{"_id":"$_id.state",average:{"$avg":"$tPop"}}}

@marcoberri, I know that it's been two year since your comment, but actually it is not important which _id to use in the last group, if you write _id: null the answer will be the same