The code below returns me all data that are present in CollectionA but NOT in CollectionB. (Using the mainID as reference).

  return this.aggregate([
    {
      $lookup: {
        from: 'CollectionB',
        localField: 'mainId',
        foreignField: 'mainId',
        as: 'validd',
      },
    },
    {
      $match: {
        'validd.mainId': { $exists: false },
      },
    },
  ]);

But now I need to add another filter. I also need to get data where the field createdAt is greater than 90 days.
in other words: Records that have more than 90 days of life.

Tried using $gte and $lte inside $match but didn't work.

{
  $match: {
    'validd.createdAt': { $gte: moment.utc().add(-90, "days") },
  },
},

This returns nothing.

My database:

{
  _id: 227dd33c5c51c79f63743da3
  mainId: 5c306562-9c87-48dd-93ca-4a118be50490
  createdAt: 2022-05-07T02:28:12.537 00:00
},
{
  _id: f3ddd33c5c51c79f63743da3
  mainId: 5c306562-9c87-48dd-93ca-4a118be50490
  createdAt: 2022-05-10T02:28:12.537 00:00
},
{
  _id: 227dd33c5c51c79f63743da3
  mainId: 5c306562-9c87-48dd-93ca-4a118be50490
  createdAt: 2022-01-01T02:28:12.537 00:00
}

we can ignore all the data, just need to focus oon createdAt.
I want to keep the $lookup logic but also add the filter to return only rows where createdAt is greater than 90 days (considering the current day / Today).

So with this logic, only the last record should be returned, because all the rest of the rows were created in less than 90 days ago

EDIT

To clarify. I need to return data if:

1. row of collectionA does NOT exists in collectionB
2. row of collectonA exists in collectionB BUT createdAt is greater than X days.

Tried this based on the answer of @ray

  return this.aggregate([
    {
      $lookup: {
        from: 'CollectionB',
        localField: 'mainId',
        foreignField: 'mainId',
        as: 'validd',
      },
    },
    {
      $match: {
        $expr: {
          $or: [
            {
              $eq: ["$validd", []]
            },
            {
              $and: [
                {
                  $lt: [ "validd.createdAt", moment.utc().add(-interval, "days").format('YYYY-MM-DD') ]
                },
                {
                  $ne: ["validd", null]
                }
              ]
            }
          ]
        }
      },
    },
  ]);

CodePudding user response：

You can simply use $or in the $expr in $match

db.CollectionA.aggregate([
  {
    "$lookup": {
      "from": "CollectionB",
      "localField": "mainId",
      "foreignField": "mainId",
      "as": "validd"
    }
  },
  {
    "$match": {
      $expr: {
        $or: [
          {
            $eq: [
              "$validd",
              []
            ]
          },
          {
            $and: [
              {
                $ne: [
                  "$validd",
                  []
                ]
              },
              {
                $lt: [
                  "$createdAt",
                  {
                    "$dateSubtract": {
                      "startDate": "$$NOW",
                      "unit": "day",
                      "amount": 90
                    }
                  }
                ]
              }
            ]
          }
        ]
      }
    }
  }
])

Here is the Mongo playground for your reference.

For version prior to MongoDB v5.0, you can $subtract $$NOW for 90 days * 24 hours * 60 min * 60 sec * 1000 ms = 7776000000 and compare to the subtraction result as an alternative.

db.CollectionA.aggregate([
  {
    "$lookup": {
      "from": "CollectionB",
      "localField": "mainId",
      "foreignField": "mainId",
      "as": "validd"
    }
  },
  {
    "$match": {
      $expr: {
        $or: [
          {
            $eq: [
              "$validd",
              []
            ]
          },
          {
            $and: [
              {
                $ne: [
                  "$validd",
                  []
                ]
              },
              {
                $lt: [
                  "$createdAt",
                  {
                    "$subtract": [
                      "$$NOW",
                      7776000000
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    }
  }
])

Here is the Mongo playground for your reference.