How to lookup and filter by date?

[Alan]

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 X days.
in other words: Records that have more than X days of life.

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

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

Here is 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
}

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.

Also tried this, but didnt work. It returns data only when the first condition is meet.

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]
                }
              ]
            }
          ]
        }
      },
    },
  ]);

Any helpp?

[steevej (Steeve Juneau)]

At first sight I see a few things.

You correctly use the dollar sign in

@Alan

$eq: ["$validd", []]

but you completely forgo it in the other part of the query.

Even with dollar sign, the following will not do what you want.

@Alan

$ne: ["validd", null]

I think it should be:

$ne : [ "$validd" : [] ]

because an empty array will match the expression “$ne : [ “$validd” , null ]”, see:

mongosh> c.find()
{ _id: 0, validd: [] }
{ _id: 1, validd: [ 10 ] }

// the empty array is also matched
mongosh> c.aggregate( { "$match" : { "$expr" : { "$ne" : [ "$validd" , null ] }}})
{ _id: 0, validd: [] }
{ _id: 1, validd: [ 10 ] }

// but with you only get the non-empty array, which is inline with your intent
mongosh> c.aggregate( { "$match" : { "$expr" : { "$ne" : [ "$validd" , [] ] }}})
{ _id: 1, validd: [ 10 ] }

This being said about $ne:[validd,null], I do not think you really need it since you are querying validd.createdAt. That last part of the query should only be true if validd contains at least one element with the field createdAt matching your $lt.

I do not see anything in your query that requires $expr, so I would try to simplify and do:

Untested:

match_stage = { "$match" : {
  "$or" :
  [
    { "validd" : [] } ,
    {
      "validd.createdAt" : { "$lt" : moment.utc().add(-interval, "days").format('YYYY-MM-DD')  }
    }
  ]
} }

[Alan]

Hi, Steeve. Thank you very much for your explanation.

I tried your snippet, and I still get the same results.

Collection A:

name: "first"
mainId: "03d36f3e-535e-4074-aefa-f4b09fa5eba2"
createdAt: 2022-05-10T11:38:08.284+00:00

name: "second"
mainId: "13d36f3e-535e-4074-aefa-f4b09fa5eba2"
createdAt: 2022-05-10T11:38:13.284+00:00

name: "third"
mainId: "23d36f3e-535e-4074-aefa-f4b09fa5eba2"
createdAt: 2022-05-10T11:38:46.284+00:00

Collection B:

name: second
mainId: "13d36f3e-535e-4074-aefa-f4b09fa5eba2"
createdAt: 2022-05-10T11:38:13.284+00:00

name: third
mainId: "23d36f3e-535e-4074-aefa-f4b09fa5eba2"
createdAt: 2022-05-04T11:38:13.284+00:00

Then I run the search using an interval of 5 days
We can see that:

• first doesn’t exists on CollectionB, so it should be returned.
• third has a CreatedAt = 2022-05-04, so it should also be returned.
• second doesn’t fullfil the second filter (longer than 5 days) and should NOT be returned.

But the output of the query only returns me the first condition. Only First because it doesn’t exists on CollectionB. But I also need to return those that exists but have a createdAt longer than my interval.

[Alan]

Sorry for adding another reply (can’t edit previous one).

Our solution worked with an extra.

Not sure WHY momentJS wasn’t able to do the job.
I had to add a new Date:

$match: {
  $or: [
    { "eligible": [] },
    { "eligible.createdAt": { $lt: new Date(interval) } }
  ]
}