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

Question

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

That depends. You can $unwind and $group or you can just use $map. Why is the expected outcome "machineId" : "VIC-AUTO-0173", and not "VIC-AUTO-0172"? Is this arbitrary? — nimrod serok
– nimrod serok, Commented May 17, 2022 at 7:14
it does have the sense to have machineid in the desired output — Alex
– Alex, Commented May 17, 2022 at 7:16
Sorry that was just id of the machine that's need not to be added.. i have updated the question — Rahul Pandey
– Rahul Pandey, Commented May 17, 2022 at 7:16
@Alex Sorry that was just id of the machine that's need not to be added.. i have updated the question — Rahul Pandey
– Rahul Pandey, Commented May 17, 2022 at 7:17
What about the activity? What happens if one of them have an activity in it? — nimrod serok
– nimrod serok, Commented May 17, 2022 at 7:26

nimrod serok · Accepted Answer · 2022-05-17 08:42:44Z

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.

Alex · Accepted Answer · 2022-05-17 07:22:37Z

0

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

answered May 17, 2022 at 7:22

Alex

1419 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related