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:
$group
- Group byupdatedAt
. With$addToSet
to add theplayer
field (without duplicate) to theuniquePlayers
array and$sum
to sum thetotal
.$set
- Set theuniquePlayers
field. With$size
get the size of theuniquePlayers
array.
db.collection.aggregate([
{
$group: {
_id: "$updatedAt",
uniquePlayers: {
$addToSet: "$player"
},
total: {
$sum: "$total"
}
}
},
{
$set: {
uniquePlayers: {
$size: "$uniquePlayers"
}
}
}
])