$$NOW vs ISODate() in aggregations

[Ben_Giddins]

Doing aggregations on MongoDB Atlas 6.0.4

Why doesn’t $$NOW work in greater than, less than etc comparion matches when ISODate() does?

If I have a workflow stage like this:

{
 $addFields:
  {
    dolNow: '$$NOW',
    isoNow: ISODate()
  }
}

I get the expected values added to pipeline documents (note the actual milliseconds differ slightly):

dolNow : 2023-02-22T17:56:34.358+00:00
isoNow : 2023-02-22T17:56:34.318+00:00

But if I use $$NOW and ISODate() in comparisons, only ISODate() results in a valid comparison occuring.

{
 $match:
  {
    endDate: {$gt: '$$NOW'}
  }
}

vs

{
 $match:
  {
    endDate: {$gt: ISODate()}
  }
}

Against exactly the same documents, only the $match using ISODate() returns any documents where the endDate is in the future. I thought that we could use $$NOW here as it also returns the current time as an ISODate?

[steevej (Steeve Juneau)]

Very good question.

The devil is in the details and most of the times the details are documented.

In $addFields we have

$addFields has the following form:

{ $addFields: { <newField>: <expression>, ... } }

However, in $match we have

The $match stage has the following prototype form:

{ $match: { <query> } }

For query we have

To specify equality conditions, use <field>:<value> expressions in the query filter document:

{ <field1>: <value1>, ... }

As an expression $$NOW gives you

A variable that returns the current datetime value. NOW returns the same value for all members of the deployment and remains the same throughout all stages of the aggregation pipeline.

As a value $$NOW gives you the string “$$NOW”. Now WOW

Luckily,

Instead, use a $expr query expression to include aggregation expression in $match.

And $now $why ISODate works? It does because ISODate is a function call on the client, so it is always sent as a value to the server so it works as well in isoNow:<value> and in isoNow:<expression> since a value is a valid expression.

[Tarun_Gaur]

Hello @Ben_Giddins ,

@steevej’s answer is very well explained and I just want to add a point to same.

When I ran your first query with .explain(“executionStats”)

db.deals.aggregate(
  {
 $match:
  {
    dt: {$gt: ISODate()}
  }
}
  ).explain("executionStats")

The query got parsed like this in the explain and you can see it took ISODate() and converted it to current date value

 parsedQuery: {
      dt: {
        '$gt': 2023-02-23T03:39:50.017Z
      }
    }

and returned a result.

On the other hand, when I ran the same with $$NOW

db.deals.aggregate(
  {
 $match:
  {
    dt: {$gt: '$$NOW'}
  }
}
  ).explain("executionStats")

you can see the query parser did not convert the value of $$NOW,the reason was explained well in the earlier post by @steevej

 parsedQuery: {
      dt: {
        '$gt': '$$NOW'
      }
    }

Hence, no result is returned now.

Regards,
Tarun