Query and sum all with mongoose

Question

I want to fetch all users user_totaldocs and user_totalthings and want to sum those variables.

How can it's possible? Here is user schema:

var user_schema = mongoose.Schema({
    local : {
        ...
        ...
        user_id          : String,
        user_totaldocs   : Number,
        user_totalthings     : Number
        ....

    }
});

Answer 1

You can use aggregation framework provided by mongodb. For your case --

if you want to fetch sum of user_totaldocs and sum of user_totalthings across the collection (meaning for all users), do --

db.user_schemas.aggregate(
 [
  {
    $group : {
       user_id : null,
       user_totaldocs: { $sum: "$user_totaldocs"}, // for your case use local.user_totaldocs
       user_totalthings: { $sum: "$user_totalthings" }, // for your case use local.user_totalthings
       count: { $sum: 1 } // for no. of documents count
    }
  }
])

To sum user_totaldocs and user_totalthings for particular user in a collection(assuming there are multiple document for a user), this will return sum for each user, DO --

db.user_schemas.aggregate(
 [
  {
    $group : {
       user_id : "$user_id", 
       user_totaldocs: { $sum: "$user_totaldocs"}, // for your case use local.user_totaldocs
       user_totalthings: { $sum: "$user_totalthings" }, // for your case use local.user_totalthings
       count: { $sum: 1 } // for no. of documents count
    }
  }
])

No need to provide individual user id.

For more info read: 1. http://docs.mongodb.org/manual/reference/operator/aggregation/group/#pipe._S_group 2. http://docs.mongodb.org/manual/core/aggregation/

Answer 2

You can use the Aggregation Pipeline to add calculated fields to a result. There are some examples below using the mongo shell, but the syntax in Mongoose's Aggregate() helper is similar.

For example, to calculate sums (per user document) you can use the $add expression in a $project stage:

db.user.aggregate(
    // Limit to relevant documents and potentially take advantage of an index
    { $match: {
        user_id: "foo"
    }},

    { $project: {
        user_id: 1,
        total: { $add: ["$user_totaldocs", "$user_totalthings"] }
    }}
)

To calculate totals across multiple documents you need to use a $group stage with a $sum accumulator, for example:

db.user.aggregate(
    { $group: {
        _id: null,
        total:       { $sum: { $add: ["$user_totaldocs", "$user_totalthings"] } },
        totaldocs:   { $sum: "$user_totaldocs" },
        totalthings: { $sum: "$user_totalthings" }
    }}
)

You may want only the one total field; I've added in totaldocs and totalthings as examples of calculating multiple fields.

A group _id of null will combine values from all documents passed to the $group stage, but you can also use other criteria here (such as grouping by user_id).