How to get last 7 days records based on createdOn date field

[MERUGUPALA_RAMES]

Hi,
I am having a collection having fields with dId and createdOn fields, Where i am trying to get the last one week records based on the dId with createdOn field. Assume , the record existing with dId:869738067254263 inserted last record on 2022-11-21 and for dId:869738067258421 lastly inserted on 2022-10-13 and i want to get the dId’s and based on the last inserted date to previous 7 days records. I am expecting the output as

"dId":"869738067258421"
createdOn:2022-11-22T04:41:36.152+00:00
"dId":"869738067258421"
createdOn:2022-11-20T04:41:36.152+00:00
"dId":"869738067258421"
createdOn:2022-11-15T04:41:36.152+00:00


"dId":"869738067254263"
createdOn:2022-09-20T04:41:36.152+00:00
"dId":"869738067254263"
createdOn:2022-09-15T04:41:36.152+00:00

"dId":"869738067441613"
createdOn:2022-10-01T04:41:36.152+00:00

Hope you get my query,
report.csv (18.3 KB)

Following attachment is the data for your reference.

[Jason_Tran]

Hi @MERUGUPALA_RAMES,

Where i am trying to get the last one week records based on the dId with createdOn field

I assume the latest "createdOn" field’s value for a particular "dId" represents the last inserted time for said "dId". Please correct me if I am wrong here in my assumption.

Based off the data provided, I created a smaller sample test collection with documents shown below:

[
{"dId":"869738067258421",createdOn:ISODate("2022-11-22T04:41:36.152+00:00")}, /// <- Latest
{"dId":"869738067258421",createdOn:ISODate("2022-11-20T04:41:36.152+00:00")},
{"dId":"869738067258421",createdOn:ISODate("2022-11-15T04:41:36.152+00:00")},
{"dId":"869738067258421",createdOn:ISODate("2022-10-10T04:41:36.152+00:00")},
{"dId":"869738067258421",createdOn:ISODate("2021-11-15T04:41:36.152+00:00")},
{"dId":"869738067254263",createdOn:ISODate("2022-09-20T04:41:36.152+00:00")},
{"dId":"869738067254263",createdOn:ISODate("2022-09-15T04:41:36.152+00:00")},
{"dId":"869738067254263",createdOn:ISODate("2022-10-20T04:41:36.152+00:00")}, /// <- Latest
{"dId":"869738067254263",createdOn:ISODate("2022-10-12T04:41:36.152+00:00")},
{"dId":"869738067254263",createdOn:ISODate("2022-10-18T04:41:36.152+00:00")},
]

I have commented the latest creation date values for the distinct "dId" value.

One possible approach that may achieve what you are after is to utilise the aggregation pipeline stages below:

[
  {
    '$group': { _id: '$dId', dateArray: { '$addToSet': '$createdOn' } }
  },
  { '$addFields': { latestCreatedOnDate: { '$max': '$dateArray' } } },
  {
    '$addFields': {
      sevenDaysDate: {
        '$dateSubtract': { startDate: '$latestCreatedOnDate', unit: 'day', amount: 7 }
      }
    }
  },
  {
    '$addFields': {
      filteredArray: {
        '$filter': {
          input: '$dateArray',
          as: 'date',
          cond: {
            '$and': [
              { '$gte': [ '$$date', '$sevenDaysDate' ] },
              { '$lte': [ '$$date', '$latestCreatedOnDate' ] }
            ]
          }
        }
      }
    }
  }
]

Using the above pipeline stages, the output from my test environment is shown below (I presume the most important information you are after would be shown in the filteredArray field):

[
  {
    _id: '869738067258421',
    dateArray: [
      ISODate("2022-11-20T04:41:36.152Z"),
      ISODate("2021-11-15T04:41:36.152Z"),
      ISODate("2022-11-22T04:41:36.152Z"),
      ISODate("2022-11-15T04:41:36.152Z"),
      ISODate("2022-10-10T04:41:36.152Z")
    ],
    latestCreatedOnDate: ISODate("2022-11-22T04:41:36.152Z"),
    sevenDaysDate: ISODate("2022-11-15T04:41:36.152Z"),
    filteredArray: [
      ISODate("2022-11-20T04:41:36.152Z"),
      ISODate("2022-11-22T04:41:36.152Z"),
      ISODate("2022-11-15T04:41:36.152Z")
    ]
  },
  {
    _id: '869738067254263',
    dateArray: [
      ISODate("2022-09-20T04:41:36.152Z"),
      ISODate("2022-09-15T04:41:36.152Z"),
      ISODate("2022-10-20T04:41:36.152Z"),
      ISODate("2022-10-18T04:41:36.152Z"),
      ISODate("2022-10-12T04:41:36.152Z")
    ],
    latestCreatedOnDate: ISODate("2022-10-20T04:41:36.152Z"),
    sevenDaysDate: ISODate("2022-10-13T04:41:36.152Z"),
    filteredArray: [
      ISODate("2022-10-20T04:41:36.152Z"),
      ISODate("2022-10-18T04:41:36.152Z")
    ]
  }
]

You can alter the $addFields (or any other) stages accordingly but I have used them here to demonstrate the values at each stage of the pipeline where they are used.

Depending on the amount of documents, the dateArray may become quite large. You can try minimising the amount of input documents using a more selective query at the start if it suits your use case.

This pipeline was only briefly tested against a small dataset as shown at the top of my reply. If you believe this may work for you, please test thoroughly on a test environment to ensure it meets all you use case(s) and requirements.

Hope this helps.

Regards,
Jason

[MERUGUPALA_RAMES]

@Jason_Tran

$dateSubtract

Hello Jason,
Thanks for the query, that matches my requirement, and currently i am using 4.4.16 V, sorry to say this, i am really dont know how to alter with $dateSubtract in v4.4. it throwing an error. Definetly it willl work in v5+ but my bad, i forgoty to mention my version previously. Thats really my mistake. Is that possible to modify the query or is there any alternative way to use $dateSubtract instead. Kindly do needfull . I am tring from end to use any other alternate way. Kindly do needfull in this.

Regards,
Ramesh.

[Jason_Tran]

Perhaps using $subtract might work for you :

[
  {
    '$group': { _id: '$dId', dateArray: { '$addToSet': '$createdOn' } }
  },
  { '$addFields': { latestCreatedOnDate: { '$max': '$dateArray' } } },
  {
    '$addFields': {
      sevenDaysDate: {
        '$subtract': ['$latestCreatedOnDate', 7 * 24 * 60 * 60 * 1000 ]
      }
    }
  },
  {
    '$addFields': {
      filteredArray: {
        '$filter': {
          input: '$dateArray',
          as: 'date',
          cond: {
            '$and': [
              { '$gte': [ '$$date', '$sevenDaysDate' ] },
              { '$lte': [ '$$date', '$latestCreatedOnDate' ] }
            ]
          }
        }
      }
    }
  }
]

I used (7 * 24 * 60 * 60 * 1000) milliseconds to calculate the 7 day period for the field sevenDaysDate.

I only tested this briefly but it generated same output as the previous pipeline I had used in my prior reply.

Again, please test thoroughly to ensure you encounter no issues and that it suits all your use case and requirements.

Regards,
Jason

[MERUGUPALA_RAMES]

Hello Jason,
Thanks a lot to provide me the query which i am looking to get the results as per my requirement. It worked for my requirement. Once again thank a log for the support.