m101 hw5.1 to 5.4 - Aggregation Framework

gistfile1.txt

// $sum
db.zips.aggregate([
	{ $group:
		{
			"_id": {
				"state": "$state"
			},
			"population": { "$sum": "$pop" }
		}
	}
])

// $sum correct
db.zips.aggregate([
	{ "$group":
		{ 
			"_id": "$state", 
			"population":	{ $sum:"$pop" }
		}
	}
])

// $avg
db.zips.aggregate([
	{ "$group":
		{
			"_id": "$state",
			"average_pop": { "$avg": "$pop"}
		}
	}
])

// $addToset
db.zips.aggregate([
	{ "$group":
		{
			"_id": "$city",
			"postal_codes":	{ "$addToSet": "$_id" }
		}
	}
])

// $max
db.zips.aggregate([
	{ "$group":
		{
			"_id": "$state",
			"pop":	{ "$max": "$pop" }
		}
	}
])

// $project
db.zips.aggregate([
	{ "$project":
		{
			"_id": 0,
			"city": { "$toLower": "$city" },
			"pop": 1,
			"state": 1,
			"zip": "$_id"
		}
	}
])

// $match
db.zips.aggregate([
	{ "$match":
		{
			"state": "NY"
		}
	},
	{ "$group":
		{
			_id: "$city",
			population: { "$sum": "$pop" },
			zip_codes: { "$addToSet": "$_id" }
		}
	}
])

db.zips.aggregate([
	{ "$match":
		{
			"pop": { "$gt": 100000 }
		}
	}
])

// $sort
db.zips.aggregate([
	{ "$sort":
		{
			"state": 1,
			"city": 1
		}
	}
])


// BLOG HW 5.1 (answer: Elizabet Kleine)

db.posts.aggregate([
	{ "$unwind": "$comments" },
	{ "$group":
		{
			"_id": {
				"author": "$comments.author"
			},
			"num_comments": { "$sum": 1 }
		}
	}
])

// HW 5.2 (answer: 82541.46420824295)
db.zips.aggregate([
	{ "$match":
		{
			"state": { "$in": ["CA", "NY"] }
		}
	},
	{ "$group":
		{
			"_id": {
				"state": "$state",
				"city": "$city"
			},
			"city_pop": { "$sum": "$pop" }
		}
	},
	{ "$match":
		{
			"city_pop": { "$gt": 25000 }
		}
	},
	{ "$group":
		{
			"_id": 0,
			"avg_pop": { "$avg": "$city_pop" }
		}
	}
])

// HW 5.3 

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


{ "$limit": 1 } ])

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