query to count based on condition with group by in mongodb

Question

I have data in mongodb like

[
    {
        "_id" : {
                "mtid" : 155118690058836,
                "uid" : 152732244430131
        },
        "winnings" : {
                "coins" : 130
        }
    },
    {
        "_id" : {
                "mtid" : 156939993909534,
                "uid" : 156889803109404
        }
    },
    {
        "_id" : {
                "mtid" : 155118690058836,
                "uid" : 152726461974666
        },
        "winnings" : {
                "coins" : 132
        }
    },
    {
        "_id" : {
                "mtid" : 155118690058836,
                "uid" : 152724558996107
        },
        "winnings" : {
                "coins" : 128
        }
    },
    {
        "_id" : {
                "mtid" : 156940003209590,
                "uid" : 156889803109404
        }
    }
]

I want to group by this on uid to find out count of winner and looser. What I am trying to do is

// fix this query to get counts
var result = await db.collection('registrations').aggregate([
    { $match: {
        "_id.mtid": { $in: Object.keys(good_mtid) },
    } },
    { $group: {
        "_id": "$_id.uid",
        "winnerCount": { $sum: 1 }, // count of winners who have more than 0 coins
        "looserCount": { $sum: 1 }  // count of loosers who dont have any coins
    } }
]).toArray()

How to fix this above query to find out correct counts with keeping conditions
like mtid should belong to good_mtid,
group by on uid
winnerCount is who have more than 0 coins
looseCount is who have no coins

Serkan Arslan · Accepted Answer · 2019-12-02 08:34:56Z

1

you can use $cond at $group part like this.

 { $group: {
        "_id": "$_id.uid",
        "winnerCount": { $sum: {$cond: [{$gt: ['$winnings.coins', 0]}, 1, 0]} }, // count of winners who have more than 0 coins
        "looserCount": { $sum: {$cond: [{$gt: ['$winnings.coins', 0]}, 0, 1]} }  // count of loosers who dont have any coins
    } }

answered Dec 2, 2019 at 8:34

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

query to count based on condition with group by in mongodb

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related