MongoDB aggregation cannot use $limit twice

Question

I have the following MongoDB collection "Games":

{ 
"_id" : ObjectId("515461d3c6c18efd4a811fd3"), 
"gameid" : NumberLong("86982207656"), 
"tableName" : "Hydra Zoom 40-100 bb", 
"nplayers" : 6, 
"playersList" : [   
                                    {           "exist" : true,         
                                                        "suspended" : false,
                                                        "grade" : 0,
                                                        "clusterId" : -1,
                                                        "playerid" : "DoomY9999",
                                                        "playsWithFriends" : 0,
                                                        "squeezePlay" : 0,
                                                        "weakShowdown" : 0,
                                                        "numberOfPlays" : 1
                                     },
                                  {
                                                        "exist": true,
                                                        "suspended" : false,

I would like to map the following MySQL query into MongoDB

String query = "SELECT idplayer, COUNT(idplayer) AS countplayer "
                    + "FROM (SELECT b.idgame, b.idplayer "
                    + "FROM associations a, associations b "
                    + "WHERE a.idplayer=? "
                    + "AND b.idgame=a.idgame "
                    + "AND b.idplayer <> ? "
                    + "ORDER BY b.idgame DESC LIMIT 1000) as c"
                    + " GROUP BY idplayer "
                    + "ORDER BY countplayer DESC LIMIT 5;";

Description of the Query: This SQL Query counts the most frequent players that appear to play the same game of Player 'X'. The result will be the name of the players and the number of times the play together

A short note for LIMIT: the first "LIMIT 1000" will be actually limit of games we want to examine, since the database could be really large we only analyse the last 1000-games in DESC order (most recent have higher "gameid").

The Second limit 5: is for the 'top 5' friends. We will sum their numbers.

So far I have accomplished: almost everything with the Aggregation Framework, made exception for the "ORDER BY b.idgame DESC LIMIT 1000) as c". This is important for me because the number of games it goes through might be really high.

Here is my query in MongoDB (Java driver):

 //build the query
    DBObject match1 = new BasicDBObject("$match", new BasicDBObject("playersList.playerid",_playerid));
    DBObject unwind = new BasicDBObject("$unwind", "$playersList");
    DBObject match2 = new BasicDBObject("$match", new BasicDBObject("playersList.playerid",new BasicDBObject("$ne",_playerid)));

    DBObject groupFields = new BasicDBObject("_id","$playersList.playerid");
    groupFields.put("times", new BasicDBObject("$sum",1));
    DBObject group = new BasicDBObject("$group", groupFields);
    DBObject sort = new BasicDBObject("$sort", new BasicDBObject("times",-1) );
    DBObject limit = new BasicDBObject("$limit", 5 );

    DBObject group2 = new BasicDBObject("$group", "gameid");
    DBObject sort2 = new BasicDBObject("$sort", new BasicDBObject("gameid",-1) );
    DBObject limit2 = new BasicDBObject("$limit", 1000 );

    DB db = mongoDb;
    DBCollection coll = db.getCollection("games");

    //aggregation query
    //THIS WORKS
    AggregationOutput output = coll.aggregate( match1, unwind, match2, group, sort, limit);

    //THIS DOESN'T WORK!
     AggregationOutput output = coll.aggregate( match1, unwind, match2, group, sort, limit, group2, sort2, limit2);

Please help me to fix this query. Thanks!

score 2 · Accepted Answer · answered Apr 12 '13 at 04:55

The field game wasn't in the result after the first group operation, so the second group operation which is based on the field game wouldn't work.

For a more efficient query, you should reorder your aggregation operations to reduce the data as early as possible. I moved the matching of the games before unwinding the playersList and it's not necessary to have the second group.

The aggregation operation is like this in the mongo shell:

// playerId to search for coplayers
var playerId = "DoomY9999"

db.game.aggregate([
    // First $match can take advantage of suitable index if available

    // Find all games that playerid X has played
    { $match : { "playersList.playerid" : playerId } },

    // Sort by most recent games (gameid descending)
    { $sort : { "_id.gameid" : -1 } },

    // Limit number of games to examine
    { $limit : 1000 },

    // Create a stream of documents from the playersList array
    { $unwind : "$playersList" },

    // Match players except for playerid X
    { $match : { "playersList.playerid" : {$ne : playerId }} },

    // Count number of games each player has played
    { $group : {
        _id : "$playersList.playerid",
        count : { $sum : 1 }
    }},

    // Sort by most frequent players (count descending)
    { $sort : { "count" : -1 } },

    // Limit results to 5 players
    { $limit : 5 },

    // Rename the result fields
    { $project : {
        _id : 0,
        coplayer : "$_id",
        count : 1
    }}
])

MongoDB aggregation cannot use $limit twice

1 Answers1