Skip to content

Instantly share code, notes, and snippets.

@mrmarcondes
Created November 30, 2012 19:48
Show Gist options
  • Save mrmarcondes/4178101 to your computer and use it in GitHub Desktop.
Save mrmarcondes/4178101 to your computer and use it in GitHub Desktop.
10gen: M101 MongoDB for Developers - Homework 5.1 - 5.4
// 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"}
}}
])
@hardikbarot
Copy link

I did HW5.2 in my java program like:
BasicDBList l=new BasicDBList();
l.add("CA");
l.add("NY");
DBObject group1 = new BasicDBObject("$group", new BasicDBObject("_id",new BasicDBObject("state","$state").append("city","$city").append("pop","$pop")));
DBObject match1 = new BasicDBObject("$match",new BasicDBObject("pop",new BasicDBObject("$gt",25000).append("_id.state", new BasicDBObject("$in",l))));
DBObject group = new BasicDBObject("$group", new BasicDBObject("_id",null).append("avg",new BasicDBObject("$avg","$pop")));
DBObject lim=new BasicDBObject("$limit",2);
AggregationOutput output = collection.aggregate(group1,match1,group);
System.out.println("Output=>"+output);

but
It gives this result

Output=>{ "serverUsed" : "localhost/127.0.0.1:27017" , "result" : [ ] , "ok" : 1.0}

@hardikbarot
Copy link

Please give me reply ,
do u have any idea where i may be wrong?

@mrmarcondes
Copy link
Author

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

@novfal
Copy link

novfal commented Oct 14, 2014

ans for 5.2

@sujitkumar77
Copy link

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}])

@paskal
Copy link

paskal commented Feb 10, 2015

Thanks a lot for sharing!

@QuocNguyen2501
Copy link

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"}}}
])

@gazulas
Copy link

gazulas commented Jul 4, 2017

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} }
])

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment