How to use $ifNull in $match stage in MongoDB

Question

I added a condition like below to an aggregation in mongodb.

"$match" : {                
$ifNull: ["$startDate", "$createdAt"] : {"$gte": ISODate("2018-06-01 10:03:46.000Z"), "$lte": ISODate("2018-06-29 10:03:46.000Z")}              
}

When I check startDate if it is undefined or null, i get createdAt but it shows me error when executing.

What is wrong in my query ?

Answer 1

The first problem with your query is that your $match object is not a valid js object. You can not replace the object key with something like this. Also, there are other limitations with $match.

As stated in the mongodb match documentation

The $match query syntax is identical to the read operation query syntax; i.e. $match does not accept raw aggregation expressions. To include aggregation expression in $match, use a $expr query expression:

{ $match: { $expr: { <aggregation expression> } } }

$ifNull can be used in $match like this;

{ 
  $match: { 
    $expr: { 
      $gte: [
          {$ifNull: ["$startDate", "$createdAt"]},
          ISODate("2018-06-01 10:03:46.000Z")
        ] 
    } 
  } 
}

Possible versions of your query:##

Using only $match:

{ 
  $match: { 
    $expr: { 
      $and: [
        { $gte: [
          {$ifNull: ["$startDate", "$createdAt"]},
          ISODate("2018-06-01 10:03:46.000Z")
        ]}, 
        { $lte: [
          {$ifNull: ["$startDate", "$createdAt"]},
          ISODate("2018-06-29 10:03:46.000Z")
        ]}
      ]
    } 
  } 
}

Alternatively, you can use $project to compute the value and use in later steps

NOTE: This will affect performance, as the Mongo won't be able to use index while matching data.

{
  $project: {
    'computedStartDate' : {$ifNull: ["$startDate", "$createdAt"]}
  }
},
{ 
  $match: { 
    "computedStartDate": 
      {"$gte": ISODate("2018-06-01 10:03:46.000Z"), "$lte": ISODate("2018-06-29 10:03:46.000Z")
    } 
  } 
}