How to get previous document of last 30 (custom) days using mongodb query

**Hello,

I have a collection and in that collection I want to get the accountID and it’s count of documents which are inserted in last 30 days. The date column is insertedDate.

For Example there is a accountID = 01 and it have 8 document from which 3 from more then 30 days of timeperiod and 5 are in 30 days so it show like that

{
accountID:01,
totalCount:5
}

there is another document and from last 30 days only 4 documents were inserted so it’s output should be

{
accountID:02
totalCount:4
}

here is the sample document

{
    "accountID" : "01",
    "insertedDate" : ISODate("2020-12-23T00:00:00Z"),
    "remarks" : "One",
    "typeKey" : "A",
}

{
    "accountID" : "01",
    "insertedDate" : ISODate("2020-12-22T00:00:00Z"),
    "remarks" : "One",
    "typeKey" : "A",
}

{
    "accountID" : "01",
    "insertedDate" : ISODate("2020-12-21T00:00:00Z"),
    "remarks" : "One",
    "typeKey" : "A",
}

{
    "accountID" : "01",
    "insertedDate" : ISODate("2020-12-20T00:00:00Z"),
    "remarks" : "One",
    "typeKey" : "A",
}

{
    "accountID" : "01",
    "insertedDate" : ISODate("2020-12-19T00:00:00Z"),
    "remarks" : "One",
    "typeKey" : "A",
}

{
    "accountID" : "01",
    "insertedDate" : ISODate("2020-12-18T00:00:00Z"),
    "remarks" : "One",
    "typeKey" : "A",
}

{
    "accountID" : "01",
    "insertedDate" : ISODate("2020-12-17T00:00:00Z"),
    "remarks" : "One",
    "typeKey" : "A",
}

{
    "accountID" : "01",
    "insertedDate" : ISODate("2020-12-16T00:00:00Z"),
    "remarks" : "One",
    "typeKey" : "A",
}

{
    "accountID" : "02",
    "insertedDate" : ISODate("2020-11-19T00:00:00Z"),
    "remarks" : "One",
    "typeKey" : "A",
}

{
    "accountID" : "02",
    "insertedDate" : ISODate("2020-11-18T00:00:00Z"),
    "remarks" : "One",
    "typeKey" : "A",
}

{
    "accountID" : "02",
    "insertedDate" : ISODate("2020-11-10T00:00:00Z"),
    "remarks" : "One",
    "typeKey" : "A",
}

{
    "accountID" : "02",
    "insertedDate" : ISODate("2020-11-02T00:00:00Z"),
    "remarks" : "One",
    "typeKey" : "A",
}

{
    "accountID" : "02",
    "insertedDate" : ISODate("2020-07-19T00:00:00Z"),
    "remarks" : "One",
    "typeKey" : "A",
}

Note that the Date field is insertedDate.
Thanks in advance.

Query:
db.TT.aggregate( [

{ $project: {   _id: 0,
                accountID:1,
                insertedDate:1,
                PreviousDate: { $subtract: [   "$insertedDate", (1000*60*60*24*30) ] },
               
                 }
   },
   {
    $group: {
      _id: {
      accountID : "$accountID" },
      FDate: { $first : "$insertedDate" },
      LDate: { $first : "$PreviousDate" },
      count: { $sum: 1 }       
    }
    
  }
    
    ])

getting output:

/* 1 */
{
    "_id" : {
        "accountID" : "02"
    },
    "FDate" : ISODate("2020-11-19T00:00:00.000Z"),
    "LDate" : ISODate("2020-10-20T00:00:00.000Z"),
    "count" : 5.0
}

/* 2 */
{
    "_id" : {
        "accountID" : "01"
    },
    "FDate" : ISODate("2020-12-23T00:00:00.000Z"),
    "LDate" : ISODate("2020-11-23T00:00:00.000Z"),
    "count" : 8.0
}

how can I achieve the target. Can you help

Hi @Nabeel_Raza, you can try this aggregation. Note the usage of the $cond within the group stage’s $sum operator:

db.test.aggregate([
  { 
      $addFields: { 
          PreviousDate: { $subtract: [  ISODate(), (1000*60*60*24*30) ] } 
      } 
  },
  { 
      $group: { 
          _id: "$accountID", 
          count: { 
              $sum: { $cond: [ { $gte: [ "$insertedDate", "$PreviousDate" ] }, 1, 0 ]  } 
          } 
      } 
  }
])

Thanks for the reply @Prasad_Saya But i am getting this output which isn’t a valid output

/* 1 */
{
    "_id" : "02",
    "count" : 0.0
}

/* 2 */
{
    "_id" : "01",
    "count" : 8.0
}

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.

No, this is not correct logic @Takis.

Date = inserted data(1st one) - 30 days

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