How can i select a particuar field in my MongoDB array and sum it up

Question

I have a MongoDB schema that I have some time a single array and sometimes I have more than 20 arrays values in it, each array has a field value which I want to sum together and insert the sum into another field in my MongoDB. Here is what am trying to say, here is my schema, How can i add the value of weight together for every package array inserted to the schema and let it be my total weight schema

{
  "status": "In warehouse",
  "paymentStatus": "incomplete",
  "_id": "5d8b56476a3e4ae2d01f2953",
  "unitNo": "0002",
  "warehouseNo": "0001",
  "trackingNo": "FPZ848505936",
  "packages": [
    {
      "date": "2019-09-26T06:30:39.561Z",
      "_id": "5d8c5b0f756838f78d5205d7",
      "category": "chil",
      "quantity": "177",
      "description": "a valueablegoods",
      "width": 15,
      "itemweight": 123,
      "length": 17,
      "height": 21,
      "dimension": 31.25903614457831,
      "weight": 32.25903614457831 
    },
    {
      "date": "2019-09-26T06:30:39.561Z",
      "_id": "5d8c5b0f756838f78d5202dd,
      "category": "chil",
      "quantity": "177",
      "description": "a valueablegoods",
      "width": 15,
      "itemweight": 123,
      "length": 17,
      "height": 21,
      "dimension": 35.25903614457831,
      "weight": 30
    },
    {
      "date": "2019-09-26T06:30:39.561Z",
      "_id": "5d8c5b0f756838f78d51aeq",
      "category": "chil",
      "quantity": "177",
      "description": "a valueablegoods",
      "width": 15,
      "itemweight": 123,
      "length": 17,
      "height": 21,
      "dimension": 32.25903614457831,
      "weight": 44
    }
  ],
  "totalWeigth": "This should add all weight value in my packages array together and if it is only 1 it should brings only the one"
  "date": "2019-09-25T11:57:59.359Z",
  "__v": 0
}

This is the api route that add the packages to the package array field and i want the totalWeight to be update anytime new packge is add or updated

// @route   POST api/admins/addshipment/:unitNo
// @desc    Add shipment for each customer
// @access  Private
router.post(
  '/addshipment/:unitNo',
  passport.authenticate('jwt', { session: false }),
  (req, res) => {

     Shipments.findOne({unitNo: req.params.unitNo}, {paymentStatus: "incomplete"})
      .then(shipments => {
        if(shipments === null || shipments.paymentStatus === "complete"){
          const errwarehouse = "This user doesn't have an existing warehouse";
          return res.status(404).json(errwarehouse);
        }else{
          if (shipments.paymentStatus === "incomplete") {
         function getPrice(){
          if (initial > dimension){
            return initial
            }else if(initial === dimension) {
            return initial
          }else{
          return dimension
          }
        }
          const newPackages = {
          category: req.body.category,
          quantity: req.body.quantity,
          description: req.body.description,
          width: req.body.width,
          itemweight: req.body.itemweight,
          length: req.body.length,
          height: req.body.height,
          dimension,
          weight: getPrice(),
        };
          Shipments.findOneAndUpdate({unitNo: req.params.unitNo ,paymentStatus: "incomplete"}, 
            {"$push": {"packages": newPackages}}, {totalWeight: {"$sum" : {"packages.weight"}}}) //Here is were i add the package to the package array and here is where i tried sumup packages.weight for every time i add new package
            .then(shipments=> res.json(shipments))


          }
        }
        });
    });

Thank you

score 1 · Answer 1 · answered Sep 26 '19 at 10:57

1

   var users =db.users.aggregate([
    {$unwind:"$packages"},
    {
           $group:
            {
               _id: "$_id",
              totalWeigth: { $sum: "$packages.weight" }
            }
        }
    ]).toArray()



 users.forEach((ele)=>{
  db.users.update({_id:ele._id},{$set:{totalWeigth:ele.totalWeigth}})  
 })

answered Sep 26 '19 at 10:57

Anushka Ahir

136
7

Please can you help write this in mongoose – phemieny7 Sep 26 '19 at 16:24
using callback or async/await? – Anushka Ahir Sep 27 '19 at 05:29

Neil Lunn · Answer 2 · 2019-09-26T11:17:01.013

If you actually have MongoDB 4.2 or greater then you can use the new aggregation syntax available for updates. This essentially means adding one of the valid aggregation pipeline statements of either $addFields, $set ( alias to $addFields to make "updates" easier to read ), $projector $replaceRoot, and then actual aggregation operators in order to do the manipulation. In this case $sum:

let writeResult = await db.collection("collection").updateMany(
  {},
  [{ "$set": { "totalWeight": { "$sum": "$packages.weight" } } }]
);

That adds a new fields of totalWeight to every document based on the values present in the whole array of each document.

The main benefit here is that this is a single request to the server which actually performs ALL updating on the server and requires no information from the collection to be sent back to the client for iteration.

If you have an earlier version ( I suggest you don't use anything earlier than 3.4, but even 3.2 would do here ), then you could use bulkWrite() in a loop:

async function updateCollection() {

  let cursor = db.collection("collection").aggregate([
    { "$project": {
      "totalWeight": { "$sum": "$packages.weight" }
    }}
  ]);

  let batch = [];

  while ( await cursor.hasNext() ) {
    let { _id, totalWeight } = await cursor.next();
    batch.push({
      "updateOne": {
        "filter": { _id },
        "update": { "$set": { totalWeight } }
      }
    });

    if ( batch.length > 1000 ) {
      await db.collection("collection").bulkWrite(batch);
      batch = [];
    })

  }

  if ( batch.length != 0 ) {
    await db.collection("collection").bulkWrite(batch);
    batch = [];
  }

}

And that would do the same thing, but of course actually requires some interaction back and forth with the server in both reading and writing the result back. Though using bulkWrite() you are only sending back writes in batches rather than per document of the collection.

If you have an even older MongoDB, then Update MongoDB field using value of another field has some references to the same techniques in loop iteration that may also apply. But I really do recommend that you should not have any older MongoDB version than those mentioned in the answer here.

N.B You probably would want to add some try/catch handlers in such update code as well in case of errors. Or on the other hand, such one off operations are probably better executed in something like the MongoDB shell.

Maintenance

The better solution overall however is to actually keep the total up to date on every addition to the array. As an example, this is basically what you would want when using $push for a new array element and $inc to add to the existing total:

   let package = {
     "date": "2019-09-26T06:30:39.561Z",
     "_id": "5d8c5b0f756838f78d5205d7",
     "category": "chil",
     "quantity": "177",
     "description": "a valueablegoods",
     "width": 15,
     "itemweight": 123,
     "length": 17,
     "height": 21,
     "dimension": 31.25903614457831,
     "weight": 32.25903614457831 
   };

   let writeResult = await db.collection('collection').udpdateOne(
     { "_id": myIdValue },
     {
       "$push": { "packages":  package },
       "$inc": { "totalWeight": package.weight
     }
   );

In that way you are actually making sure the total is adjusted with every change you make and therefore it does not need constant reprocessing of another statement in order to keep that total in the document. Similar concepts apply for other types of updates other than adding a new item to an array.

thank you for helping me out but this is not the only thing am trying to acheive, so i updated the code — phemieny7, Sep 26 '19 at 14:26
@Mason you apparently updated your question to the exact point I mentioned in closing and gave you code to handle that. You seem to have not really read that content and devised your own poorly implemented substitute, which notably has considerable problems — Neil Lunn, Sep 27 '19 at 09:46

score -1 · Answer 3 · answered Sep 27 '19 at 09:19

i rewrote the code so that i can use for each of the package weight a sum should be done on them and here is the code

// @route   POST api/admins/addshipment/:unitNo
// @desc    Add shipment for each customer
// @access  Private
router.post(
  '/addshipment/:unitNo',
  passport.authenticate('jwt', { session: false }),
  (req, res) => {

     Shipments.findOne({unitNo: req.params.unitNo}, {paymentStatus: "incomplete"})
      .then(shipments => {
          const newPackages = {
          category: req.body.category,
          quantity: req.body.quantity,
          description: req.body.description,
          width: req.body.width,
          itemweight: req.body.itemweight,
          length: req.body.length,
          height: req.body.height,
          dimension,
          weight: getPrice(),
        };
        let total = 0;
          Shipments.findOne({unitNo: req.params.unitNo ,paymentStatus: "incomplete"})
            .then(shipments=> {
             shipments.packages.push(newPackages)
            shipments.packages.forEach(i=>{ total += i.weight})
            shipments.totalWeight = total
            shipments.save()
            res.json(shipments)
            }) 
          }
        }
        });
    });

How can i select a particuar field in my MongoDB array and sum it up

3 Answers3

Maintenance