Mongo Aggregate by multiple props-CodePudding

I need to generate stats from users collection in Mongodb.

USER schema:

{
  _id: ObjectId,
  name: string,
  city: string,
  gender: enunm["Male", "Female"],
}

STATS schema:

[
  {
    city: string,
    Male: number, (number of males in that $city)
    Female: number, (number of females in that $city)
  }
]

What aggregation pipeline I should use?

I tried something like this:

db.testCollection.aggregate([
  { $group: { _id: "$status", totalQuantity: { $count: "$stats" } } },
]);

CodePudding user response：

You want to be using $cond to sum conditionally on gender value while grouping, like so:

db.collection.aggregate([
  {
    $group: {
      _id: "$city",
      Male: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$gender",
                "Male"
              ]
            },
            1,
            0
          ]
        }
      },
      Female: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$gender",
                "Female"
              ]
            },
            1,
            0
          ]
        }
      }
    }
  },
  {
    $project: {
      Female: 1,
      Male: 1,
      city: "$_id",
      _id: 0
    }
  }
])

Mongo Playground