CODEWITHSUNDEEP
node.jsmongodbaggregation-framework
Alexander Beletsky
Alexander Beletsky

Reputation: 19841

MongoDB: Aggregation, group by several fields

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?

Upvotes: 2

Views: 2449

Answers (2)

Derick
Derick

Reputation: 36794

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.

Upvotes: 3

Parvin Gasimzade
Parvin Gasimzade

Reputation: 26032

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"}}
)

Upvotes: 1

Related Questions