I have a collection of 1000 documents like this:

{ 
    "_id" : ObjectId("628b63d66a5951db6bb79905"), 
    "index" : 0, 
    "name" : "Aurelia Gonzales", 
    "isActive" : false, 
    "registered" : ISODate("2015-02-11T04:22:39.000 0000"), 
    "age" : 41, 
    "gender" : "female", 
    "eyeColor" : "green", 
    "favoriteFruit" : "banana", 
    "company" : {
        "title" : "YURTURE", 
        "email" : "[email protected]", 
        "phone" : " 1 (940) 501-3963", 
        "location" : {
            "country" : "USA", 
            "address" : "694 Hewes Street"
        }
    }, 
    "tags" : [
        "enim", 
        "id", 
        "velit", 
        "ad", 
        "consequat"
    ]
}

I want to group those by year and gender. Like In 2014 male registration 105 and female registration 131. And finally return documents like this:

{
    _id:2014,
    male:105,
    female:131,
    total:236
},
{
    _id:2015,
    male:136,
    female:128,
    total:264
}

I have tried till group by registered and gender like this:

db.persons.aggregate([
    { $group: { _id: { year: { $year: "$registered" }, gender: "$gender" }, total: { $sum: NumberInt(1) } } },
    { $sort: { "_id.year": 1,"_id.gender":1 } }
])

which is return document like this:

{ 
    "_id" : {
        "year" : 2014, 
        "gender" : "female"
    }, 
    "total" : 131
}
{ 
    "_id" : {
        "year" : 2014, 
        "gender" : "male"
    }, 
    "total" : 105
}

Please guide to figure out from this whole.

CodePudding user response：

db.collection.aggregate([
  {
    "$group": { //Group things
      "_id": "$_id.year",
      "gender": {
        "$addToSet": {
          k: "$_id.gender",
          v: "$total"
        }
      },
      sum: { //Sum it
        $sum: "$total"
      }
    }
  },
  {
    "$project": {//Reshape it
      g: {
        "$arrayToObject": "$gender"
      },
      _id: 1,
      sum: 1
    }
  },
  {
    "$project": { //Reshape it
      _id: 1,
      "g.female": 1,
      "g.male": 1,
      sum: 1
    }
  }
])

Play

CodePudding user response：

Just add one more group stage to your aggregation pipeline, like this:

db.persons.aggregate([
    { $group: { _id: { year: { $year: "$registered" }, gender: "$gender" }, total: { $sum: NumberInt(1) } } },
    { $sort: { "_id.year": 1,"_id.gender":1 } },
{
  $group: {
    _id: "$_id.year",
    male: {
      $sum: {
        $cond: {
          if: {
            $eq: [
              "$_id.gender",
              "male"
            ]
          },
          then: "$total",
          else: 0
        }
      }
    },
    female: {
      $sum: {
        $cond: {
          if: {
            $eq: [
              "$_id.gender",
              "female"
            ]
          },
          then: "$total",
          else: 0
        }
      }
    },
    total: {
      $sum: "$total"
    }
  },
}
]);

Here's the working link. We are grouping by year in this last step, and calculating the counts for gender conditionally and the total is just the total of the counts irrespective of the gender.

CodePudding user response：

Besides @Gibbs mentioned in the comment which proposes the solution with 2 $group stages,

You can achieve the result as below:

1. $group - Group by year of registered. Add gender value into genders array.

2. $sort - Order by _id.

3. $project - Decorate output documents.

   3.1. male - Get the size of array from $filter the value of "male" in "genders" array.

   3.2. female - Get the size of array from $filter the value of "female" in "genders" array.

   3.3. total - Get the size of "genders" array.

Propose this method if you are expected to count and return the "male" and "female" gender fields.

db.collection.aggregate([
  {
    $group: {
      _id: {
        $year: "$registered"
      },
      genders: {
        $push: "$gender"
      }
    }
  },
  {
    $sort: {
      "_id": 1
    }
  },
  {
    $project: {
      _id: 1,
      male: {
        $size: {
          $filter: {
            input: "$genders",
            cond: {
              $eq: [
                "$$this",
                "male"
              ]
            }
          }
        }
      },
      female: {
        $size: {
          $filter: {
            input: "$genders",
            cond: {
              $eq: [
                "$$this",
                "female"
              ]
            }
          }
        }
      },
      total: {
        $size: "$genders"
      }
    }
  }
])

Sample Mongo Playground