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

mongodb sum array values
mongodb find sum of field
mongodb sum with condition
mongodb sum multiple fields
mongodb sum nested array
mongodb aggregate sum string
mongodb group
mongodb group by multiple fields

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

   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}})  
 })

How can i select a particuar field in my MongoDB array and sum it up, var users =db.users.aggregate([ {$unwind:"$packages"}, { $group: { _id: "$_id", totalWeigth: { $sum: "$packages.weight" } } } ]).toArray() users. Non-Numeric or Non-Existent Fields¶. If used on a field that contains both numeric and non-numeric values, $sum ignores the non-numeric values and returns the sum of

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.

How to Calculate a Sum in MongoDB, How to Perform a Sum in MongoDB with code snippets and examples. The $​sum operator is used to sum up the values of fields in documents. We hope you learned what you needed to apply it to your specific problem. Filter Array Elements in MongoDB · Delete MongoDB Document · Add Element  When you’re working with data in MongoDB, it’s likely that you’ll have to add a new field to a document at some point. Fortunately, adding fields in a MongoDB array is a simple task that can be accomplished in just a few lines of code. In this step-by-step tutorial, we’ll show you how to add a new field in a MongoDB document array.

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)
            }) 
          }
        }
        });
    });

MongoDB Group by Multiple Fields Using Aggregation Function , sum – Returns the sum of all numeric fields. push – inserts the field value into the result field; $ addToSet – Inserts a value into an array of the resulting If a single-node pipeline exceeds the limit, MongoDB generates an error. First, the key on which the grouping is based is selected and then the  In this article, I will explain how to add a new field in a MongoDB document array. The Demo – Adding A New Field to a MongoDB Document Array. Suppose we have database which contain data of a few agents working for a company in New York. It consists of their names, age, city and an array of objects that contains the connections of that member.

The Definitive Guide to MongoDB: A Complete Guide to Dealing with , Let's say that you want to get a list of all contacts in your collection, grouped by to the database $c = new MongoClient(); // Select the collection 'people' from the is summarized using the $sum function, and returned using the total field. Note that the $sum function is represented by an array, and given the value of 1 as  Working with arrays as values in MongoDB Unlike relational database models, MongoDB documents can have fields which have values as arrays . The prototypical example in almost all MongoDB documentation is a document having a tags field, whose value is an array of strings, such as ["NoSQL", "Ruby", "MongoDB"] .

MongoDB: The Definitive Guide: Powerful and Scalable Data Storage, Filtering and selecting individual elements or slices of arrays are among the summing all values in a particular field ($sum), calculating an average ($avg), etc​. MongoDB Manual. Version 4.2 (current) Back Up and Restore with MongoDB Tools; Selects documents if the array field is a specified size.

Bridging Relational and NoSQL Databases, MongoDB query versus SQL statements MongoDB Statements SQL Statements db.vehicles.find() select * from vehicles View of all vehicles from the collection total: {$sum: “$quantity”}}} select vechicle_id, sum(quantity) from orders where the in-stock array has at least one embedded document that contains the field  Query an Array with Compound Filter Conditions on the Array Elements¶. The following example queries for documents where the dim_cm array contains elements that in some combination satisfy the query conditions; e.g., one element can satisfy the greater than 15 condition and another element can satisfy the less than 20 condition, or a single element can satisfy both:

Comments
  • Please can you help write this in mongoose
  • using callback or async/await?
  • thank you for helping me out but this is not the only thing am trying to acheive, so i updated the code
  • @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