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

Question

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

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'}  
         } 
      }
    }
] )

javascript
node.js
mongodb

Answer 1

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'}]}

Answer 2

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