Need help to MongoDB aggregate $group state

Question

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.

You can group again on _id.year and take gender $first, total $first — Gibbs
– Gibbs, Commented May 30, 2022 at 7:06
@Gibbs, if you have enough, would you please help me how I do this? — Nasir
– Nasir, Commented May 30, 2022 at 7:25

Gibbs · Accepted Answer · 2022-05-30 07:38:58Z

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

Think you need to update the Mongo Playground link as the query inside the link is different from the posted answer. Anyway, the query from the answer works smoothly. =)

Charchit Kapoor · Accepted Answer · 2022-05-30 07:27:59Z

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.

It works fine, but there is a hard core check 'male' or 'female'. If any change on my document I have to put hands on my aggregation query

Yong Shun · Accepted Answer · 2022-05-30 07:29:26Z

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

You can achieve the result as below:

$group - Group by year of registered. Add gender value into genders array.
$sort - Order by _id.
$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

It works fine, but there is a hard core check 'male' or 'female'. If any change on my document I have to put hands on my aggregation query

Collectives™ on Stack Overflow

Need help to MongoDB aggregate $group state

3 Answers 3

2 Comments

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Related