I have sample json data in collections.
Sample data:
[{
"_id" : 1,
"username" : "abcd",
"createdDate" : ISODate("2016-06-03T08:52:32.434Z")
},
{
"_id" : 2,
"username" : "abcd",
"createdDate" : ISODate("2016-05-03T09:52:32.434Z")
},
{
"_id" : 3,
"username" : "abcd",
"createdDate" : ISODate("2016-04-03T10:52:32.434Z")
},
{
"_id" : 4,
"username" : "xyz",
"createdDate" : ISODate("2016-03-03T10:52:32.434Z")
},{
"_id" : 5,
"username" : "xyz",
"createdDate" : ISODate("2016-02-03T10:52:32.434Z")
},{
"_id" : 6,
"username" : "zzz",
"createdDate" : ISODate("2016-01-03T10:52:32.434Z")
}]
This data I need to retrieve data for following condtions.
Expecting output:
[{
"username" : "abcd",
"createdDate" : "2016-06-03T08:52:32.434Z",
"total" : 3
},
{
"username" : "xyz",
"createdDate" : "2016-03-03T10:52:32.434Z",
"total" : 2
}]
Query:
db.logs.aggregate([
{ "$match": { "username": { "$ne": "zzz" } }},
{ "$group": {
"_id": {
"username": "$username",
"createdDate": "$createdDate"
},
"count": { "$sum": 1 }
}}])
try this :
db.logs.aggregate([
{
"$match":{
"username":{
"$ne":"zzz"
}
}
},
{
"$group":{
_id:"$username",
"count":{
"$sum":1
},
date:{
$max:"$createdDate"
}
}
},
{
$project:{
username:"$_id",
total:"$count",
createdDate:"$date"
}
}
])
output
{
"_id":"xyz",
"username":"xyz",
"total":2,
"createdDate": ISODate("2016-03-03T10:52:32.434 Z")
}{
"_id":"abcd",
"username":"abcd",
"total":3,
"createdDate": ISODate("2016-06-03T08:52:32.434 Z")
}
try it online: mongoplayground.net/p/3_-s2tUjPFi