Group By Count on Array MongoDB

Question

This is one out of ~10 million rows in my database:

{
    "_id" : ObjectId("58f569159f809c49ffc5cdbb"),
    "date" : ISODate("2017-03-21T09:10:07.686Z"),
    "p1" : {
        "_id" : 1765906,
        "deck" : [ 
            33, 
            25, 
            59, 
            38, 
            62, 
            3, 
            33, 
            57
        ],
        "crowns" : 3
    },
    "p2" : {
        "_id" : 2520156,
        "deck" : [ 
            25, 
            69, 
            86, 
            8, 
            44, 
            61, 
            69, 
            50
        ],
        "crowns" : 2
    }
}

It is a log of a game battle. The deck is an array which consists of 8 different cards. I am trying to find the card which has the most wins. A winner can be determined by comparing the crowns (I managed to select all documents where player 1 has won).

What I am trying to achieve:

Unfortunately I wasn't able to perform a group query which would return what I am looking for (the card with the most wins).
I am also trying to find the most successful deck (quantity of wins is enough - the order of the cards specified in that array should be ignored).

What I have tried, but returned an empty error after some time:

db.battle_logs.aggregate([
    {
        $addFields: {
            "player1won": { "$cmp": [ "$p1.crowns", "$p2.crowns" ] }
        }
    },
    { $match: { "player1won": 1 }},
    { 
        $group: 
        {
            _id: "$p1.deck",
            count: { $sum: 1 }
        }
    }
], {
  allowDiskUse:true,
  cursor:{}
 })

I expected to get all deck combinations grouped by its quantity of wins (only player1 wins considered)

it's not very clear, can you add the expected output for the document you provided? — felix, Apr 18 '17 at 06:17
@felix For 2) it would be the deck (which is an array consisting of ints) with the number/count of wins (I have filtered for player1Won already in the given query, so I only need to group by the decks and count it's results). I assume that I need to sort the int array (p1.deck) so that it does ignore the order of the cards — kentor, Apr 18 '17 at 11:34

score 1 · Accepted Answer · answered Apr 18 '17 at 23:10

As you are using the array p1.deck as _id in your group query, you need to sort the array, otherwise the order of the card will create a new group.

Here is a something that might work for your case:

1)

db.battle_logs.aggregate([
    {
        $addFields: {
            "player1won": { "$cmp": [ "$p1.crowns", "$p2.crowns" ] }
        }
    },
    //Fiter out ties
    { 
        $match: { 
            "$or" : [
                {"player1won": 1},  //Player 1 won
                {"player1won" : -1} //Player 2 won
            ]
        }
    },
    // Get the winning deck
    {
        $project: {
            "deck": { $cond : [ { $gte : [ "$p1.crowns", "$p2.crowns" ]}, "$p1.deck", "$p2.deck" ]} 
        }
    },
    //Unwind the deck to get every card
    { 
        $unwind : "$deck"
    },
    //Get count of each card
    { 
        $group : { 
            "_id" : "$deck" , 
            "count" : {"$sum" : 1}
        }
    }, 
    // Sort on count
    {
        $sort: {"count" : -1}

    },
    //Get the card with highest count
    { 
        $limit: 1
    }
], {
  allowDiskUse:true,
  cursor:{}
 })

2)

db.battle_logs.aggregate([
    {
        $addFields: {
            "player1won": { "$cmp": [ "$p1.crowns", "$p2.crowns" ] }
        }
    },
    //Fiter out ties
    { 
        $match: { 
            "$or" : [
                {"player1won": 1},  //Player 1 won
                {"player1won" : -1} //Player 2 won
            ]
        }
    },
    // Get the winning deck
    {
        $project: {
            "deck": { $cond : [ { $gte : [ "$p1.crowns", "$p2.crowns" ]}, "$p1.deck", "$p2.deck" ]} 
        }
    },
    //Unwind the deck to get every card
    { 
        $unwind : "$deck"
    },
    //Sort the cards
    { 
        $sort : {"deck": 1}
    },
    //Group sorted cards back in deck
    { 
        $group : { 
            "_id" : "$_id" , 
            "deck" : {"$push" : "$deck"}
        }
    }, 
    { 
        $group: 
        {
            _id: "$deck",
            count: { $sum: 1 }
        }
    }
], {
  allowDiskUse:true,
  cursor:{}
 })

Group By Count on Array MongoDB

1 Answers1