How to compare two dates in the mongo aggregation framework? [duplicate]

Question

We're currently using metabase to analyze our data. I've run into a problem with querying with an aggregation query. I wanted all the documents where date1 is before date2. (I used 3 filters to debug my results, it should be possible to put it in 1 match filter later on)

[{
  "$match": {
    "date1": {
      "$exists": true
     }
  }
}, {
  "$project": {
    "_id": 0,
    "date1": 1,
    "date2": 1
  }
}, {
  "$match": {
    "date1": {
        "$lt": "$date2"
    }
  }
}]

I'm always getting no results. Anybody who could help me with the query?

EDIT:

The documents looks like this:

 { 
   _id: ObjectID,
   date1: ISODate("2016-10-04T08:34:15.041Z"),
   date2: ISODate("2016-10-05T08:34:15.041Z")
   ... some more fields that are not interesting for this query
 }

There are around 50k documents in the collection.

Answer 1

You can use $where to compare the two date fields.

db.collection.find({
    $where : "this.date1 < this.date2"    
});

The documents which satisfy the above condition will be present on the result. If any document which doesn't have attributes either date1 or date2 will not be present on the result.

Using aggregate function:-

$cmp can be used to compare the values. -1 represents the first is less than the second one.

First pipeline is required to eliminate the documents which doesn't contain both date1 and date2

db.collection.aggregate([
{
  "$match": {
    "date1": {
      "$exists": true
     },
     "date2": {
      "$exists": true
     }
  }
},
{"$project": {
      "date1":1,
      "date2":1,
      "dateComp": {"$cmp":["$date1","$date2"]}
    }
  },
  {"$match":{"dateComp": -1}}

]);