Nested grouping with MongoDB

Question

Given a database the form of

[
{ gender: "m", age: 1, name: "A" },
{ gender: "f", age: 2, name: "B" },
{ gender: "m", age: 3, name: "C" },
{ gender: "f", age: 1, name: "D" },
{ gender: "m", age: 2, name: "E" },
{ gender: "f", age: 3, name: "F" },
{ gender: "m", age: 1, name: "G" },
{ gender: "f", age: 2, name: "H" },
{ gender: "m", age: 3, name: "I" },
{ gender: "f", age: 1, name: "J" }
]

I want to first group by age and secondly group by gender so that I get a nested result looking something like

[{
    _id: "1",
    children: [
        { _id: "f" },
        { _id: "m" }
    ]
}, {
    _id: "2",
    children: [
        { _id: "f" },
        { _id: "m" }
    ]
}, {
    _id: "3",
    children: [
        { _id: "f" },
        { _id: "m" }
    ]
}]

Here is what I tried so far:

db.example.aggregate(
{ $group: { _id: "$age", children: { $addToSet: {
    age: "$age", gender: "$gender", name: "$name"
}}}},
{ $group: { _id: "$children.gender"}}
)

But this returns an {_id: null} as its result. Is this possible and in case yes, how?

Joachim Isaksson · Accepted Answer · 2013-05-27 12:02:05Z

54

Something like this should do it;

db.example.aggregate( 
  { 
    $group: { 
      _id:   { age: "$age", gender: "$gender" }, 
      names: { $addToSet: "$name" } 
    } 
  }, 
  { 
    $group: {
      _id: { age: "$_id.age" }, 
      children: { $addToSet: { gender: "$_id.gender", names:"$names" } } 
    } 
  } 
)

...which gives the result;

{
  "_id" : {
    "age" : 1
  },
  "children" : [
    { "gender" : "m", "names" : [ "G", "A" ] },
    { "gender" : "f", "names" : [ "J", "D" ] }
  ]
}, 
...

If you want the age as _id as in your example, just replace the second grouping's _id by;

_id: "$_id.age",

edited May 27, 2013 at 12:02

answered May 27, 2013 at 11:55

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

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

1 Comment

RaduM Over a year ago

I was looking for this, as I failed all the time trying to recreate, but one more question how to I count the gender? How many times a gender appears for every age?

Collectives™ on Stack Overflow

Nested grouping with MongoDB

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related