MongoDB: Aggregation, group by several fields

Question

I have a collection with documents, of that structure

{
  type: 'a',
  date: '2014-01-04'
},
{
  type: 'b',
  date: '2014-01-04'
},
{
  type: 'b',
  date: '2014-01-04
},
{
  type: 'c',
  date: '2014-01-03'
},
{
  type: 'a',
  date: '2014-01-03'
}

I want to aggregate that data by date and type (group by date and count by type):

{
  date: '2014-01-04': {
    'a': 1,
    'b': 2
  },

  date: '2014-01'03': {
    'a': 1,
    'c': 1
  }
}

I have aggregate function, like this

db.items.aggregate([
    {
        $match: { user: user },
    },
    {
        $group: { _id: {date: '$date'}, count: {$sum: 1}, services: {$push: '$type'}}
    }
], function (err, results) {

But doing that I still need to reduce results by services.

Can this be done with one aggregation query?

Derick · Accepted Answer · 2014-01-09 15:28:56Z

3

You can of course group by more than one field:

{ $group: { _id: { date: '$date', services: '$services' } }

But that is not what you want it seems. You can not every easily convert data to keys, unless you can do that all by hand. The following query would be an option:

db.test.aggregate( [
    { $group: {
        '_id' : { date: '$date' },
        a: { $sum: { 
            $cond: [ { $eq: [ '$type', 'a' ] }, 1, 0 ]  
        } },
        b: { $sum: { 
            $cond: [ { $eq: [ '$type', 'b' ] }, 1, 0 ]
        } },
        c: { $sum: { 
            $cond: [ { $eq: [ '$type', 'c' ] }, 1, 0 ] 
        } },
    } },
    { $project: {
        _id: 0,
        date: '$_id.date',
        a: '$a',
        b: '$b',
        c: '$c',
    } }
] );

You will need to manually add a line for each new type.

edited Jan 9, 2014 at 15:28

answered Jan 9, 2014 at 15:07

Derick

37k7 gold badges82 silver badges104 bronze badges

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

2 Comments

Parvin Gasimzade Over a year ago

This is not correct. "$_id.a", "$_id.b" and "$_id.c" will not return any result. In the group part _id object does not contain a,b,c fields.

Alexander Beletsky Over a year ago

Yeap, I have fixed amount of types - so your query looks interesting. I didn't know $project could go after $group. will try it asap.

Parvin Gasimzade · Accepted Answer · 2014-01-09 14:54:28Z

1

By assuming you have fixed number of types, you can solve it as follows :

db.collection.aggregate(
{$group : {_id : "$date", 
           a:{$sum:{$cond:[{$eq:['$type','a']},1,0]}},
           b:{$sum:{$cond:[{$eq:['$type','b']},1,0]}},
           c:{$sum:{$cond:[{$eq:['$type','c']},1,0]}}
}},
{$project : {_id : 0, date : "$_id", a: "$a", b : "$b", c : "$c"}}
)

answered Jan 9, 2014 at 14:54

Parvin Gasimzade

26.2k8 gold badges58 silver badges83 bronze badges

2 Comments

Alexander Beletsky Over a year ago

that seems to me as very close to answer of @Derick. assume that his answer is editited..

Parvin Gasimzade Over a year ago

You can check the time of the answer. I have answered before than Derick.

Collectives™ on Stack Overflow

MongoDB: Aggregation, group by several fields

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related