MongoDB aggregate for daily won/lost games, $cond is always false-CodePudding

I'm doing a couple of $match's and $unwind's and end up with a collection of documents looking like this:

{
_id:249776348,
StartGameTime:1615789789,
team:0,
winner:0
}

So I'm converting the _id field to a date (that works fine) and then I'm trying to do a daily won/lost count. The data should be read like this: team is the team the player was on, and winner is the team that won, so { team: 0, winner: 0 } and { team: 1, winner: 1 } indicated the player won.

This is what I got, a $group done in MongoDB Compass' UI:

{
  _id: {$dateToString: { format: "%Y-%m-%d", date: {$add: [ISODate('1970-01-01T00:00:00Z'),  {$multiply: [1000,"$StartGameTime"]  }]}}},
  won: {
    $sum: {
        $cond: [{$or: [{$and: [{$eq: ["team", 1]},{$eq: ["winner", 1]}]},{$and: [{$eq: ["team", 0]},{$eq: ["winner", 0]}]}]}, 1, 0]
    }
  },
  lost: {
    $sum: {
        $cond: [{$or: [{$and: [{$eq: ["team", 1]},{$eq: ["winner", 0]}]},{$and: [{$eq: ["team", 1]},{$eq: ["winner", 0]}]}]}, 1, 0]
    }
  }
}

And unfortunately it always gives my won: 0 and lost: 0

I bet it's something obvious, but I just can't spot it!

Thanks

CodePudding user response：

How about simply comparing the team and winner value? For won case, if team == winner, then count 1, else count 0. Vise versa for lost, but the condition become team != winner

db.collection.aggregate([
  {
    $group: {
      _id: {
        $dateToString: {
          format: "%Y-%m-%d",
          date: {
            $add: [
              ISODate("1970-01-01T00:00:00Z"),
              {
                $multiply: [
                  1000,
                  "$StartGameTime"
                ]
              }
            ]
          }
        }
      },
      won: {
        $sum: {
          "$cond": {
            "if": {
              $eq: [
                "$team",
                "$winner"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      lost: {
        $sum: {
          "$cond": {
            "if": {
              $ne: [
                "$team",
                "$winner"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      }
    }
  }
])

Here is the Mongo playground for your reference.