Expected Output is:
{ "accountID" : "01", "remarks" : "One", "typeKey" : "A", "count": 8 } { "accountID" : "02", "remarks" : "One", "typeKey" : "A", "count": 4 }
I want to get the accountID and it’s count of documents which are inserted in last 30 days.
I see that the instertedDate for accountID: '01' within the last 30 days includes all 8 documents.
But for accountID:02 the count for last 30 days is 4
If you want a count that accountID, remarks and typeKey specific you must add remarks and typeKey into the _id of $group like
_id : { accountID:$accountID,typeKey:$typeKey,remarks:$remarks }
Those documents are dated 2020-11-19 or older, more that 30 days.
But in 30 days the count should be shown which is 4
@Nabeel_Raza, for accountID: '02' all the instertedDate values are before 2020-11-18 - and that is more than 30 days - so the count is zero.
Yeah i know but the count for documents in 30 days for account 02 is 4.
We should exclude all those document which are more then 30 days from the first document.
How do you determine which one is the first document?
the first document for each accountID
1 Like
30days=insertedDate-userMinIsertedDate(the oldest)?
What is the criteria to determine which is the first document for each of the accountIDs? How do you know this “first” document?
We can also sort the document on insertedDate field for each accountID.
The first entered document for each accountID is the first document for each accountID
For the accountID=02
The oldest = ISODate(“2020-07-19T00:00:00Z”)
The newest= ISODate(“2020-11-19T00:00:00Z”)
we want to keep
oldest + max30 days?
or
newest - max30days? (i think you want this but not sure)
Yesterday i sended
current_date - inserted_date <= 30 days (first query + merge on other collection)
inserted_date-olderst_of_user <= 30days (second query + merge on other collection)
This one is
newest_of_user-insertedDate <=30 days (this query but no merge)
Its 4 for user 02 and 8 for user=01 , i think its ok but not sure if this you need
{
"aggregate": "testcollA",
"pipeline": [
{
"$lookup": {
"from": "testcollA",
"let": {
"acid": "$accountID",
"d": "$insertedDate"
},
"pipeline": [
{
"$match": {
"$expr": {
"$eq": [
"$accountID",
"$$acid"
]
}
}
},
{
"$group": {
"_id": "$accountID",
"userNewestPostedDate": {
"$max": "$insertedDate"
}
}
},
{
"$addFields": {
"accountID": "$_id"
}
},
{
"$project": {
"_id": 0
}
},
{
"$project": {
"userNewestPostedDate": 1
}
}
],
"as": "joined"
}
},
{
"$unwind": {
"path": "$joined"
}
},
{
"$replaceRoot": {
"newRoot": {
"$mergeObjects": [
"$joined",
"$$ROOT"
]
}
}
},
{
"$unset": [
"joined"
]
},
{
"$group": {
"_id": "$accountID",
"sum": {
"$sum": {
"$cond": [
{
"$lte": [
{
"$subtract": [
"$userNewestPostedDate",
"$insertedDate"
]
},
2592000000
]
},
1,
0
]
}
}
}
},
{
"$addFields": {
"accountID": "$_id"
}
},
{
"$project": {
"_id": 0
}
}
],
"cursor": {},
"maxTimeMS": 1200000
}
1 Like
Thanks @Takis for your efforts, If you closely look then you will see that accountID = 01 have 5 records in last 30 days.
Acount=01
ISODate(“2020-12-23T00:00:00Z”) = newest
ISODate(“2020-12-22T00:00:00Z”) 1 days before
ISODate(“2020-12-21T00:00:00Z”) 2 days
ISODate(“2020-12-20T00:00:00Z”) 3 days
ISODate(“2020-12-19T00:00:00Z”) 4 days
ISODate(“2020-12-18T00:00:00Z”) 5 days
ISODate(“2020-12-17T00:00:00Z”) 6 days
ISODate(“2020-12-16T00:00:00Z”) 7 days
All are <30 days from the newest so all pass,count=8
Acount=02
ISODate(“2020-11-19T00:00:00Z”) =newest
ISODate(“2020-11-18T00:00:00Z”) 1 day before
ISODate(“2020-11-10T00:00:00Z”) 9 days before
ISODate(“2020-11-02T00:00:00Z”) 17 days before
ISODate(“2020-07-19T00:00:00Z”) 4 months before
The first 4 are <30 days from the newest and pass ,count=4
The last one is rejected
The last query works this way,its possible that i dont know what you wanted
but why you say acount=01 has 5 ,can you give the dates like above,and explain it?
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.