How to aggregate by multiple fields in mongodb

Question

I want to aggregate by 2 fields and wish to have the gouping done in a nested way. How do I achieve it? Currently I do the grouping in following way

var query = [
  { '$group': {
    '_id': {
      'employee': '$employee',
      'sector': '$sector'
    },
    'bookCount': { '$sum': 1 }
  }},
  { '$sort': { 'count': -1 } }
];

Order.aggregate(query, function(err, results){
  res.json(results)
});

I want the results to be in the form

{abc:{sector1:4, sector3:5}, xyz: {sector1:10, sector2:23}}

where abc, xyz are employees and sector1, sector2 are sectors.

How do I aggregate to get nested results?

My original document is

[
  {
    "sector": "sector1",
    "employee": "xyz"
  },
  {
    "sector": "sector1",
    "employee": "abc"
  },
  {
    "sector": "sector1",
    "employee": "abc"
  },
  {
    "sector": "sector2",
    "employee": "abc"
  }
]

I want result to be of the form

{abc:{sector1:2,sector2:2}, xyz: {sector1:1}}

Show your original document.

Neil Lunn
– Neil Lunn

2015-01-15 11:03:10 +00:00
Commented Jan 15, 2015 at 11:03 — Neil Lunn
– Neil Lunn, Commented Jan 15, 2015 at 11:03

Neil Lunn · Accepted Answer · 2015-01-15 12:27:36Z

2

You cannot use "data" as "key names" in the aggregation framework, and nor can you create nested objects with nested properties. You shouldn't want to either, as this is an "anti-pattern". Data is data and should stay that way. Plus, there are better ways to do this:

Order.aggregate([
    { "$group": {
        "_id": {
           "employee": "$employee",
           "sector": "$sector"
        },
        "count": { "$sum": 1 }
    }},
    { "$group": {
       "_id": "$_id.employee",
       "sectors": { 
           "$push": {
               "sector": "$_id.sector",
               "count": "$count"
           }
       }
    }}
],function(err,docs) {

});

Which returns a structure like this:

[
    {
            "_id" : "xyz",
            "sectors" : [
                    {
                            "sector" : "sector1",
                            "count" : 1
                    }
            ]
    },
    {
            "_id" : "abc",
            "sectors" : [
                    {
                            "sector" : "sector2",
                            "count" : 1
                    },
                    {
                            "sector" : "sector1",
                            "count" : 2
                    }
            ]
    }
]

So you have a primary grouping key for "employee" values and the other results are "pushed" to an array.

It's a much better structure with consistent results in the naming of keys.

answered Jan 15, 2015 at 12:27

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

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

2 Comments

raju Over a year ago

Why did you call the nested pattern "anti-pattern"? I tried to get to that form because from usage point of view it seems more convenient. Is using data values as key names not a right thing to do? After I posted this question, I wrote some code to manipulate the data returned by aggregation and did return in the nested format to client

Neil Lunn Over a year ago

@raju Moving things into "keys" that is clearly data is bad practice. Good objects have a consistent interface for properties and not one that always changes. Lists belong in arrays. Listing lots of properties in a single object with required "unique" names in order to be able to even do that is bad.

Collectives™ on Stack Overflow

How to aggregate by multiple fields in mongodb

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related