I'm trying to create an API in NestJS with MongoDB that sends emails to registered users who haven't used my system in the last 7 days.

Every time a user logs into the system I create a document in session collection that has this structure in resume: (users can have multiples session in a day)

user_email: "[email protected]";
_created: "2019-05-13T13:09:39.000 00:00"

I have a list of emails that i want to verify, for example:

['[email protected]', '[email protected]', '[email protected]', '[email protected]']

Is there a way to do that without iterating user by user to find out if he/she has documents in the session collection or not in the last 7 days?

Appreciate any help or suggestion

I thought about using aggregation and compare with the list of emails thats is not present in the result. I don't have much experience with aggregation so I don't know if I'm doing right way. This is the way I tried

[
  {
    '$match': {
      'user_email': {
        '$in': ['[email protected]', '[email protected]', '[email protected]', '[email protected]']
      }, 
      '_created': {
        '$gte': new Date('Thu, 10 Nov 2022 00:00:00 GMT') // 7 days ago since today is 2022-11-17
      }
    }
  }, {
    '$sort': {
      '_created': -1
    }
  }, {
    '$group': {
      '_id': '$user_email', 
      'last_session': {
        '$first': '$_created'
      }
    }
  }
]

CodePudding user response：

You can use $dateDiff to compute the date difference between _created and $$NOW. Use the computed result to compare with 7 to get the records you want.

db.collection.aggregate([
  {
    "$match": {
      $expr: {
        $and: [
          {
            "$in": [
              "$user_email",
              [
                "[email protected]",
                "[email protected]",
                "[email protected]",
                "[email protected]"
              ]
            ]
          },
          {
            $gt: [
              {
                "$dateDiff": {
                  "startDate": "$_created",
                  "endDate": "$$NOW",
                  "unit": "day"
                }
              },
              7
            ]
          }
        ]
      }
    }
  }
])

Mongo Playground