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

Need help to MongoDB aggregate $group state

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.

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0 
    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

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

Back To Top
Search