How to aggregate array of document in MongoDB

Question

I have the following schema:

var orderSchema = new mongoose.Schema({
    deleted: Boolean,
    clientName: String,
    clientPhone: String,
    products: [{
        name: String,
        quantity: Number,
        price: Number
    }],
    observations: String,
    date: {
        type: Date,
        default: Date.now
    },
    seller: String,
    orderNumber: Number,
    total: Number
});

And I want to make an aggregate query that sums each product and add quantity*price to the total, and return me the total. However, I only know how to do it if I had multiple products, each one as a document, and then sum over all the documents but not this.

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-02-07 21:20:37Z

1

You can do this with $reduce :

db.collection.aggregate([{
    $addFields: {
        "total": {
            $reduce: {
                input: "$products",
                initialValue: '$total',
                in: { $add: [{ $multiply: ["$$this.quantity", "$$this.price"] }, '$$value'] }
            }
        }
    }
}])

Test : MongoDB-Playground

answered Feb 7, 2020 at 21:20

whoami - fakeFaceTrueSoul

18.1k6 gold badges36 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Poperton Over a year ago

what is total??

whoami - fakeFaceTrueSoul Over a year ago

@GuerlandoOCs : I didn't get that ? total = total + sum of quantity*price of all products..

Poperton Over a year ago

You add a field "total" into the document. What if I want to return a new document only with the total field? instead of addFields I return a new document

whoami - fakeFaceTrueSoul Over a year ago

@GuerlandoOCs : You can try with $project instead $addFields, also add _id:0 in project to exclude that as well.. Generally $addFields would replace exiting Total field with new value or if it not there it will a new field to column..

Collectives™ on Stack Overflow

How to aggregate array of document in MongoDB

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related