Home > Software engineering >  MongoDB - Count unique values with group by date
MongoDB - Count unique values with group by date

Time:10-31

Can anyone help, I have a data collection

Sample Data

[
  {
    "_id": 1,
    "player" : {
        "userId": "unique_1"
    },
    "total": 100,
    "updatedAt": "2020-10-05"
  },
  {
    "_id": 2,
    "player" : {
        "userId": "unique_2"
    },
    "total": 200,
    "updatedAt": "2020-10-05"
  },
  {
    "_id": 3,
    "player" : {
        "userId": "unique_2"
    },
    "total": 200,
    "updatedAt": "2020-10-06"
  }
]

Sample Output

[
  {
    "_id": "2020-10-05", (group by date)
    "uniquePlayers": 2, (count of unique player.userId)
    "total": 300 (sum of total)
  },
  {
    "_id": "2020-10-06", 
    "uniquePlayers": 1,
    "total": 100
  }
]

CodePudding user response:

  1. $group - Group by updatedAt. With $addToSet to add the player field (without duplicate) to the uniquePlayers array and $sum to sum the total.

  2. $set - Set the uniquePlayers field. With $size get the size of the uniquePlayers array.

db.collection.aggregate([
  {
    $group: {
      _id: "$updatedAt",
      uniquePlayers: {
        $addToSet: "$player"
      },
      total: {
        $sum: "$total"
      }
    }
  },
  {
    $set: {
      uniquePlayers: {
        $size: "$uniquePlayers"
      }
    }
  }
])

Demo @ Mongo Playground

  • Related