Sum of subdocuments in Mongoose

Question

Currently I have this schema.

var cartSchema = new Schema({
    userPurchased: {type: Schema.Types.ObjectId, ref: 'users'},
    products: [
        {
            product: {type: Schema.Types.ObjectId, ref: 'products'},
            size: {type: String, required: true},
            quantity: {type: Number, required: true},
            subTotal: {type: Number, required: true}
        }
    ],
    totalPrice: {type: Number, default: 0, required: true}
});

Example of db record

{
    "_id": {
        "$oid": "586f4be94d3e481378469a08"
    },
    "userPurchased": {
        "$oid": "586dca1f5f4a7810fc890f97"
    },
    "totalPrice": 0,
    "products": [
        {
            "product": {
                "$oid": "58466e8e734d1d2b0ceeae00"
            },
            "size": "m",
            "quantity": 5,
            "subTotal": 1995,
            "_id": {
                "$oid": "586f4be94d3e481378469a09"
            }
        },
        {
            "subTotal": 1197,
            "quantity": 3,
            "size": "m",
            "product": {
                "$oid": "58466e8e734d1d2b0ceeae01"
            },
            "_id": {
                "$oid": "586f4ef64d3e481378469a0a"
            }
        }
    ],
    "__v": 0
}

Is there any way to sum all the subTotal and put it in the total price field? Right now I am thinking about aggregate function but I doubt it will be the right approach in here. I guess I need an update query and sum method at the same time. Can anyone help me in here?

chridam · Accepted Answer

Using the aggregate() function, you can run the following pipeline which uses the $sum operator to get the desired results:

const results = await Cart.aggregate([
    { "$addFields": {
        "totalPrice": {
            "$sum": "$products.subTotal"
        }
    } },
]);

console.log(JSON.stringify(results, null, 4));

and the corresponding update operation follows:

db.carts.updateMany(
   { },
   [
        { "$set": {
            "totalPrice": {
                "$sum": "$products.subTotal"
            }
        } },
    ]
)

Or if using MongoDB 3.2 and earlier versions, where $sum is available in the $group stage only, you can do

const pipeline = [
    { "$unwind": "$products" },
    {
        "$group": {
            "_id": "$_id",
            "products": { "$push": "$products" },
            "userPurchased": { "$first": "$userPurchased" },
            "totalPrice": { "$sum": "$products.subTotal" }
        }
    }
]

Cart.aggregate(pipeline)
    .exec(function(err, results){
        if (err) throw err;
        console.log(JSON.stringify(results, null, 4));
    })

In the above pipeline, the first step is the $unwind operator

{ "$unwind": "$products" }

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 _id field, thus effectively regrouping the denormalised documents back to their original schema.

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 (called accumulators) as well. You can read more about the accumulators here.

In this $group operation, the logic to calculate the totalPrice and returning the original fields is through the accumulators. You get thetotalPrice by summing up each individual subTotal values per group with $sum as:

"totalPrice": { "$sum": "$products.subTotal }

The other expression

"userPurchased": { "$first": "$userPurchased" },

will return a userPurchased value from the first document for each group using $first. Thus effectively rebuilding the original document schema before the $unwind

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 => result

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.cart.aggregate([
    { "$unwind": "$products" }
])

Check the result to see if the products array is deconstructed properly. If that gives the expected result, add the next:

db.cart.aggregate([
    { "$unwind": "$products" },
    {
        "$group": {
            "_id": "$_id",
            "products": { "$push": "$products" },
            "userPurchased": { "$first": "$userPurchased" },
            "totalPrice": { "$sum": "$products.subTotal" }
        }
    }
])

Repeat the steps till you get to the final pipeline step.

If you want to update the field then you can add the $out pipeline stage as the last step. This will write the resulting documents of the aggregation pipeline to the same collection, thus technically updating the collection.

var pipeline = [
    { "$unwind": "$products" },
    {
        "$group": {
            "_id": "$_id",
            "products": { "$push": "$products" },
            "userPurchased": { "$first": "$userPurchased" },
            "totalPrice": { "$sum": "$products.subTotal" }
        }
    },
    { "$out": "cart" } // write the results to the same underlying mongo collection
]

UPDATE

To do both the update and query, you could then issue a find() call in the aggregate callback to get the updated json i.e.

Cart.aggregate(pipeline)
    .exec(function(err, results){
        if (err) throw err;
        Cart.find().exec(function(err, docs){
            if (err) return handleError(err);
            console.log(JSON.stringify(docs, null, 4));
        })
    })

Using Promises, you could do this alternatively as

Cart.aggregate(pipeline).exec().then(function(res)
    return Cart.find().exec();
).then(function(docs){  
    console.log(JSON.stringify(docs, null, 4));
});

Sum of subdocuments in Mongoose

Answers (2)

Related Questions