MongoosJS: Best approach for a derived/calculated value
You could use the aggregation framework for calculating the aggregates. This is a faster alternative to Map/Reduce for common aggregation operations.In MongoDB, a pipeline consists of a series of special operators applied to a collection to process data records and return computed results. Aggregation operations group values from multiple documents together, and can perform a variety of operations on the grouped data to return a single result. For more details, please consult the documentation.
Consider running the following pipeline to get the desired result:
var pipeline = [ { "$unwind": "$userPicks" }, { "$group": { "_id": { "week": "$week", "user": "$userPicks.user" }, "weeklyScore": { "$sum": { "$cond": [ { "$eq": ["$userPicks.chosenTeam", "$winner"] }, 1, 0 ] } } } }, { "$group": { "_id": "$_id.user", "weeklyScores": { "$push": { "week": "$_id.week", "score": "$weeklyScore" } }, "totalScores": { "$sum": "$weeklyScore" } } }];Game.aggregate(pipeline, function(err, results){ User.populate(results, { "path": "_id" }, function(err, results) { if (err) throw err; console.log(JSON.stringify(results, undefined, 4)); });})
In the above pipeline, the first step is the $unwind
operator
{ "$unwind": "$userPicks" }
which comes in quite handy when the data is stored as an array. When the unwind operator is applied on a list data field, it will generate a new record for each and every element of the list data field on which unwind is applied. It basically flattens the data.
This is a necessary operation for the next pipeline stage, the $group
step where you group the flattened documents by the fields week
and the "userPicks.user"
{ "$group": { "_id": { "week": "$week", "user": "$userPicks.user" }, "weeklyScore": { "$sum": { "$cond": [ { "$eq": ["$userPicks.chosenTeam", "$winner"] }, 1, 0 ] } } } }
The $group
pipeline operator is similar to the SQL's GROUP BY
clause. In SQL, you can't use GROUP BY
unless you use any of the aggregation functions. The same way, you have to use an aggregation function in MongoDB as well. You can read more about the aggregation functions here.
In this $group
operation, the logic to calculate each user's weekly score (i.e. the number of football games they predict correctly each week) is done through the ternary operator $cond
that takes a logical condition as it's first argument (if) and then returns the second argument where the evaluation is true (then) or the third argument where false (else). This makes true/false returns into 1 and 0 to feed to $sum
respectively:
"$cond": [ { "$eq": ["$userPicks.chosenTeam", "$winner"] }, 1, 0]
So, if within the document being processed the "$userPicks.chosenTeam"
field is the same as the "$winner"
field, the $cond
operator feeds the value 1 to the sum else it sums zero value.
The second group pipeline:
{ "$group": { "_id": "$user", "weeklyScores": { "$push": { "week": "$_id.week", "score": "$weeklyScore" } }, "totalScores": { "$sum": "$weeklyScore" } }}
takes the documents from the previous pipeline and groups them further by the user
field and calculates another aggregate i.e. the total score, using the $sum
accumulator operator. Within the same pipeline, you can aggregate a list of the weekly scores by using the $push
operator which returns an array of expression values for each group.
One thing to note here is when executing a pipeline, MongoDB pipes operators into each other. "Pipe" here takes the Linux meaning: the output of an operator becomes the input of the following operator. The result of each operator is a new collection of documents. So Mongo executes the above pipeline as follows:
collection | $unwind | $group | $group => result
Now, when you run the aggregation pipeline in Mongoose, the results will have an _id
key which is the user id and you need to populate the results on this field i.e. Mongoose will perform a "join" on the users collection and return the documents with the user schema in the results.
As a side note, to help with understanding the pipeline or to debug it should you get unexpected results, run the aggregation with just the first pipeline operator. For example, run the aggregation in mongo shell as:
db.games.aggregate([ { "$unwind": "$userPicks" }])
Check the result to see if the userPicks
array is deconstructed properly. If that gives the expected result, add the next:
db.games.aggregate([ { "$unwind": "$userPicks" }, { "$group": { "_id": { "week": "$week", "user": "$userPicks.user" }, "weeklyScore": { "$sum": { "$cond": [ { "$eq": ["$userPicks.chosenTeam", "$winner"] }, 1, 0 ] } } } }])
Repeat the steps till you get to the final pipeline step.