CODEWITHSUNDEEP
mongodbaggregation-framework
Martin
Martin

Reputation: 12215

Return full document after an Aggregate on MongoDB

I'm grouping a Collection on two properties ($user & $game) to return the Document that matches the maximum of $score property

Here's my method:

• Sorting by score descending,
• grouping on (user/game),
• selecting each $first element on each property : 

db.session.aggregate(
    { $sort: {score: -1} },
    { $group: {
                    _id: { user: "$user", game: "$game"},
                    id: { $first: "$_id"},
                    score: { $first: "$score" }, 
                    user: { $first: "$user" },
                    game: { $first: "$game" },
                    startDate: { $first: "$startDate" },
                    .... & all remaining properties
                }
    },
    { $project: {_id: "$id", score: 1, user: 1, game: 1, startDate: 1, ... } }
)

But I'm not satisfied with this since it seems a bit redondant and laborious. If I need one more property in my Collection schema, I'll need to add the property at several places.

I'm sure there is a better method to do this. Any idea?

EDIT

My Schema is defined as (in javascript, with Mongoose) :

var sessionSchema = new Schema({
    user        : { type: Schema.ObjectId, ref: 'User' },
    game        : { type: Schema.ObjectId, ref: 'Game' },
    level       : Number,
    percent     : Number,
    score       : Number,
    startDate   : Date,
    endDate     : Date,
});

There is lots of Users and for each User, several Games. A Session is a Game played by a User at a given time. So a User can have many Sessions for one Game. I just want the Session where the player made his hi-score.
Something like : 

[ {_id: ..., user: u1, game: g1, score: 100, ...},
  {_id: ..., user: u2, game: g1, score: 120, ...},
  {_id: ..., user: u1, game: g2, score: 90, ...},
  ...
]

Note I already have something which works. I just wonder if there is something more elegant.

Upvotes: 2

Views: 3154

Answers (2)

chridam
chridam

Reputation: 103445

A much better option would be to use the $replaceRoot pipeline found in MongoDB server versions 3.4 and above. This will promote the embedded $$ROOT document to the top level and replaces all other fields:

var query = Session.aggregate([
    { '$sort': { 'score': -1 } },
    { 
        '$group': {
            '_id': { 'user': '$user', 'quizz': '$quizz' },
            'doc': { '$first': '$$ROOT' }
        }
    },
    { '$replaceRoot': { 'newRoot':  '$doc' } }
]); 

query.exec( function(err, results) {
    if (err)
        res.status(500).json({error: err});
    else {
        res.status(200).json(results);
    }
});

Upvotes: 5

Martin
Martin

Reputation: 12215

Thanks to @BlakesSeven, I ended up with the $$ROOT variable solution.

Since his solution adds an extra descendent in my Document hierarchy, I've decided to flatten the results after querying, with the javascript map method.

Here's the complete solution, using node.js & Mongoose.

var query = Session.aggregate();    
query.sort({ score: -1 });
query.group( {
                _id: { user: "$user", quizz: "$quizz"},
                doc: { "$first": "$$ROOT" }
            });
query.project( {_id: 0, doc: 1 } );
query.exec( function(err, results) {
    if (err)
        res.status(500).json({error: err});
    else {
        // flattening the results array
        results = results.map(function(session) {
            return session["doc"];
        });
        res.status(200).json(results);
    }
});

Upvotes: 2

Related Questions