MongoDB Aggregation with Sum and Multiple Group Results

Question

Let's say I have these collections members and positions

[
    {
        "church":"60dbb265a75a610d90b45c6b", "parentId":"60dbb265a75a610d90b45c6b", name: "Jonah John", status: 1, birth: "1983-01-01", position: "60f56f59-08be-49ec-814a-2a421f21bc08"
    },
    {
        "church":"60dbb265a75a610d90b45c6b", "parentId":"60dbb265a75a610d90b45c6b", name: "March John", status: 1, birth: "1981-01-23", position: "60f56f59-08be-49ec-814a-2a421f21bc08"
    },
    {
        "church":"60dbb265a75a610d90b45c6b", "parentId":"60dbb265a75a610d90b45c6b",name: "Jessy John", status: 0, birth: "1984-08-01", position: "e5bba609-082c-435a-94e3-0997fd229851"
    }
]

[
    {_id: "60f56f59-08be-49ec-814a-2a421f21bc08", name: "Receptionist"},
    {_id: "5c78ba5a-3e6c-4d74-8d4a-fa23d02b8003", name: "Curtain"},
    {_id: "e5bba609-082c-435a-94e3-0997fd229851", name: "Doorman"}
]

I want to aggregate in a way I can get:

inactiveMembers
activeMembers
totalMembers
totalPositionsOcuppied

And two arrays with:

positionsOcuppied {name, quantity}
birthdays {month, quantity.

I need an output like this:

{
    "_id": {
        "church":"60dbb265a75a610d90b45c6b",
        "parentId":"60dbb265a75a610d90b45c6b"
    },
    "inactiveMembers":1,
    "activeMembers":2,
    "totalMembers":3,
    "birthdays": [
        {january:2}, {august:1}
    ],
    "positionsOcuppied": [
        {Doorman: 1}, {Receptionist:2}
    ],
    "totalPositionsOcuppied": 3
}

How can I do that?

PS.: Very sorry for unclear values...

nimrod serok · Accepted Answer · 2022-07-13 13:16:07Z

1

Update:

$addFields with birthMonth string
$lookup to add positions
$facet to $group by birthdays, positionsOcuppied, and all docs tougher as other
$map to format birthdays and positionsOcuppied
Format the answer

db.people.aggregate([
  {$addFields: {
      birthMonth: {
        $arrayElemAt: [  
["","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"],
          {$month: {$toDate: "$birth"}}
        ]
      }
    }
  },
  {$lookup: {from: "positions", localField: "position", foreignField: "_id",
      as: "position"}},
  {$facet: {
      birthdays: [{$group: {_id: "$birthMonth", count: {$sum: 1}}}],
      positionsOcuppied: [{$group: {_id: {$first: "$position.name"}, count: {$sum: 1}}}],
      other: [
        {$group: {_id: 0, 
            activeMembers: {$sum: "$status"},
            totalMembers: {$sum: 1},
            church: {$first: "$church"},
            parentId: {$first: "$parentId"},
            totalPositionsOcuppied: {$sum: {$size: "$position"}}
          }
        }
      ]
    }
  },
  {$set: {
      birthdays: {
        $map: {input: "$birthdays", in: [{k: "$$this._id", v: "$$this.count"}]}
      },
      positionsOcuppied: {
        $map: {input: "$positionsOcuppied", in: [{k: "$$this._id", v: "$$this.count"}]}
      },
      other: {$first: "$other"}
    }
  },
  {$set: {
      "other.birthdays": {
        $map: {input: "$birthdays", in: {$arrayToObject: "$$this"}}
      },
      "other.positionsOcuppied": {
        $map: {input: "$positionsOcuppied", in: {$arrayToObject: "$$this"}}
      },
      "other.inactiveMembers": {
        $subtract: ["$other.totalMembers", "$other.activeMembers"]
      },
      "other._id": {church: "$other.church", parentId: "$other.parentId"},
      birthdays: "$$REMOVE",
      "other.church": "$$REMOVE",
      "other.parentId": "$$REMOVE",
      positionsOcuppied: "$$REMOVE"
    }
  },
  {$replaceRoot: {newRoot: "$other"}}
])

See how it works on the playground example

edited Jul 13, 2022 at 13:16

answered Jul 12, 2022 at 9:47

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

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

2 Comments

DValdir Martins Over a year ago

I've updated the question trying to clarify it. I hope now it is more understandable.

nimrod serok Over a year ago

Updated the answer accordingly

Collectives™ on Stack Overflow

MongoDB Aggregation with Sum and Multiple Group Results

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related