Question 117953 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – How to merge all the objects in a nested array to one object by summing each property on mongo db?

Given 1st block of code is the sample data and the second block is my desired output
What is the query needed to add the fields of Machine Stats in MongoDB So that my desired output is this (basically addition of all the fields in machine stats array)

{
    "date" : ISODate("2022-04-01T00:00:00.000Z"),
    "intervalName" : "Shift A",
    "operatorId" : "85875678",
    "__v" : 0,
    "clientId" : "ywegduywy",
    "createdAt" : ISODate("2022-05-05T07:33:08.183Z"),
    "deleted" : false,
    "machineStats" : [ 
        {
            "idleTime" : 10,
            "breaks" : 10,
            "loading" : 10,
            "unloading" : 10,
            "runtime" : 11,
            "total" : 100,
            "activity" : {}
        }, 
        {
            "idleTime" : 10,
            "breaks" : 10,
            "loading" : 10,
            "unloading" : 10,
            "runtime" : 10,
            "total" : 100,
            "activity" : {}
        }
    ],
    "plantId" : "AACCS3034M-SEZ-01",
    "totalActivity" : 10,
    "totalAll" : 100,
    "totalBreaks" : 10,
    "totalIdleTime" : 10,
    "totalLoadUnload" : 10,
    "totalRuntime" : 10,
    "updatedAt" : ISODate("2022-05-05T07:33:30.213Z")
}

Desired output I Want (basically addition of all the fields in machine stats array except activities)

{
    "date" : ISODate("2022-04-01T00:00:00.000Z"),
    "intervalName" : "Shift A",
    "operatorId" : "495632582487",
    "__v" : 0,
    "clientId" : "AACCS3034M",
    "createdAt" : ISODate("2022-05-05T07:33:08.183Z"),
    "deleted" : false,
    "machineStats" : [ 
        {
            "idleTime" : 20,
            "breaks" : 20,
            "loading" : 20,
            "unloading" :20,
            "runtime" : 21,
            "total" : 200,
            "activity" : {}
        }, 
       
    ],
    "plantId" : "AACCS3034M-SEZ-01",
    "totalActivity" : 10,
    "totalAll" : 100,
    "totalBreaks" : 10,
    "totalIdleTime" : 10,
    "totalLoadUnload" : 10,
    "totalRuntime" : 10,
    "updatedAt" : ISODate("2022-05-05T07:33:30.213Z")
}
Tags:

2

Answers


  1. 0

    You can use map and reduce methods to complete this kind of calculation in a mongo query.

    Login or Signup to reply.
  2. 0

    One way to do it is using $reduce to iterate over the array and add each item’s data to the cumulative data, like this:

    db.collection.aggregate([
  {
    $set: {
      machineStats: {
        $reduce: {
          input: "$machineStats",
          initialValue: {
            idleTime: 0,
            breaks: 0,
            loading: 0,
            unloading: 0,
            runtime: 0,
            total: 0
          },
          in: {
            idleTime: {$add: ["$$value.idleTime", "$$this.idleTime"]},
            breaks: {$add: ["$$value.breaks", "$$this.breaks"]},
            loading: {$add: ["$$value.loading", "$$this.loading"]},
            unloading: {$add: ["$$value.unloading", "$$this.unloading"]},
            total: {$add: ["$$value.total", "$$this.total"]},
            runtime: {$add: ["$$value.runtime", "$$this.runtime"]}
          }
        }
      }
    }
  }
])

    Playground example.

    Another option is to use $unwind and $group but it should be less efficient for this specific requested output.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search