I have this simple document setup:

• dateAdded: string with date format 'YYYY-MM-DD'
• lastFetched: Date object (ISODate in mongodb language)

{
  lastFetched: 2021-03-01T09:27:07.406 00:00
  dateAdded:   "2020-12-19"
}

How can I find documents where lastFetched is smaller than dateAdded? Something like:

db.collection.find( { lastFetched: {$lt: ISODate('$dateAdded')} } )

To do this I first need to convert either lastFetched to a date string, or dateAdded to an ISODate object.

But more importantly: how can I use the value of a field as input for a condition check? A simpler version would be:

{
  lastFetched: "2021-03-01"
  dateAdded:   "2020-12-19"
}

With the following pseudo-query:

db.collection.find( { lastFetched: {$lt: '$dateAdded'} } )

I already tried using $addFields (aggregate) as a first step:

{
  date: ISODate('$dateAdded')
}

but that just gave me a date of 1970-01-01T00:00:00.000 00:00.

Any help would be very welcome!

Thanks

CodePudding user response：

Thanks @Joe for the $expr tip. Here is the solution for future versions of myself:

Create the following aggregate stages. The first stage replaces the ISODate field lastFetched to a string version that matches the dateAdded format (YYYY-MM-DD). And then use $expr to check against that field.

[
  {
    '$addFields': {
      'lastFetched': {
        '$dateToString': {
          'date': '$lastFetched', 
          'format': '%Y-%m-%d'
        }
      }
    }
  }, {
    '$match': {
      '$expr': {
        '$lt': [
          '$lastFetched', '$dateAdded'
        ]
      }
    }
  }
]

CodePudding user response：

It is not necessary to add an extra $addFields stage and in most cases it is best to minimize the number of stages. You can convert to an ISODate in-line in the $match stage:

db.foo.aggregate([
    {'$match': {'$expr': {
        '$lt': [{'$dateToString':{'date':'$lastFetched','format':'%Y-%m-%d'}},
                '$dateAdded'
               ]
    }}}
]);