Is there a way to count distinct values in mongoose with specific cut off date
For example, I have the following two docs
[
{
"_id": "5fc534505144dd0030c44f8e",
"createdAt": "2020-12-14T15:11:21.327Z"
"user_id": "2",
},
{
"_id": "5fc534505144dd0030c44f8e",
"createdAt": "2020-12-14T14:10:40.427Z",
"user_id": "1"
},
{
"_id": "5fc534595144dd0030c44f95",
"createdAt": "2020-12-13T14:10:58.027Z",
"user_id": "1"
}
]
the results should be
[
{
"date": "2020-12-13",
"count":1
},
{
"date": "2020-12-14",
"count":2
}
]
where the count is the number of distinct docs via user_ids till the date that specific cut off date
given data
data=[
{
"createdAt": "2020-12-14T15:11:21.327Z",
"user_id": "2",
},
{
"createdAt": "2020-12-14T14:10:40.427Z",
"user_id": "1"
},
{
"createdAt": "2020-12-13T14:10:58.027Z",
"user_id": "1"
},{
"createdAt": new Date("2020-12-14T14:10:58.027Z"),
}
]
> db.dummy.insert(data)
You may use aggregate: use $group with _id being the date's day in conjunction with $sum)
> db.dummy.aggregate({$group:{_id:{$substr:['$createdAt', 0, 10]}, count:{$sum:1}}})
{ "_id" : "2020-12-14", "count" : 3 }
{ "_id" : "2020-12-13", "count" : 1 }
edit: mongoose wise same may hold
const mongoose = require('mongoose')
mongoose.connect('mongodb://localhost:27017/dummy')
const UDate = mongoose.model('X', { createdAt:String, user_id: String }, 'dummy')
;(async()=>{
mongoose.set('debug', true)
const group = {$group:{_id:{$substr:['$createdAt', 0, 10]}, count:{$sum:1}}}
const v = await UDate.aggregate([group])
console.log('s : ', JSON.stringify(v))
mongoose.disconnect()
})()
edit2: to handle unicity of userIds so there are not counted twice per date, you may use $addToSet instead of sum followed by a projection using $size
const group = {$group:{_id:{$substr:['$createdAt', 0, 10]}, userIds:{$addToSet:'$user_id'}}}
const count = {$project:{date:'$_id', count: {$size: '$userIds'}} }
const v = await Stock.aggregate([group, count])
Lastly, if you feel always more, you can "rename" the _id field as date during the projection
{$project:{date:'$_id', _id:0, count: {$size: '$userIds'}} }