How do I group by in mongodb to produce a list of objects?

Question

I have data like this,

{
    meta: {
        artist: "artist",
        album: "album",
        year: 2008
    }
}

and I want to do the equivilent of an SQL group by on the artist to produce a list of objects with { album: album, year: year } using mongodb aggregation.

I have this query which is almost what I want but I dont think its the correct way of doing it.

db.pending.aggregate( [ 
    { $project: 
      { 'meta.artist': 1, 'meta.album': 1, 'meta.year':1 } 
    }, 
    { $group: 
      {
       '_id': 
         { artist: '$meta.artist', album: '$meta.album', 
             year: { $year: '$meta.year'}  
         } 
      }
    }
] )

cubbuk · Answer 1 · 2013-01-26T11:55:49.950

I think you can do the following by aggregation framework like this:

db.pending.aggregate(
  {$group: {_id: '$artist', albums: {$push: {album: '$album', year: '$year'}}}}, 
  {$sort: {_id: 1}}
);

Or you can do that by a map reduce function:

var mapFunction = function() {
                       emit(this.meta.artist, {album: this.meta.album, year: this.meta.year});
                   };

var reduceFunction = function(artistId, albumValues) {
                     var reducedObject = {
                                artisId: artistId,
                                albums: []};

                    albumValues.forEach( function(albumValue) {
                                   reducedObject.albums.push(
                                       {album: albumValue.album,
                                       year: albumValue.year}
                                        );
                                   });
                        return reducedObject;
};

db.pending.mapReduce(
                     mapFunction,
                     reduceFunction,
                     { out: { inline: 1 } }
                   )

I don't think you can do what you want by SQL group by query either. This will return you a result similar to this {_id: "artisName", values[{album: 'album1', year: 'year1'}, {album: 'album2', year: 'year2'}]}

Is it possible to do this with aggregation instead of mapReduce? — FEiN, Jan 26 '13 at 11:25
There might be but I haven't played with aggregation framework and prefer map reduce rather than others as it is better documented right now and also it is more scalable as stated on the following post http://stackoverflow.com/questions/12337319/mongodb-aggregation-comparison-group-group-and-mapreduce — cubbuk, Jan 26 '13 at 11:38
added how you can do that with aggregation framework at the end. — cubbuk, Jan 26 '13 at 11:46

score 1 · Accepted Answer · answered Jan 26 '13 at 11:41

I think you need the $addToSet operator:

db.test.aggregate(
  { $project:
    { 'meta.artist': 1
    , 'meta.album': 1
    , 'meta.year': 1 }
  }
  , { $group:
      { _id: '$meta.artist'
      , albums: {
        $addToSet: {
          album: '$meta.album'
        , year: '$meta.year' }
      }
    }
  }
)

How do I group by in mongodb to produce a list of objects?

2 Answers2