Mongodb aggrgration with indexed date field not working

[Lior_Alon]

Hello,
I checked all over but I cant seem to find a solution.

I want to run a simple $gte operator on a date field. The field is indexed. For some reason, It looks like the IXSCAN phase is returning of all the results to the parent node.

mongodb version 4.4.13

the query is:

    const pipeline = [
      {
        "$match": {
          "$expr": {
            "$and": [
              { $gte: ["$timestamp", new Date("2022-04-23T00:00:00.000Z")] }
            ]
          }
        }
      }

 const res =  await this._demandSupplyTransactionModel.aggregate(pipeline,
      {
        hint: `timestamp`
      }
    ).allowDiskUse(true).exec();

and the run results:

{
  "executionSuccess": true,
  "nReturned": 168940,
  "executionTimeMillis": 16335,
  "totalKeysExamined": 6366938,
  "totalDocsExamined": 6366938,
  "executionStages": {
    "stage": "FETCH",
    "filter": {
      "$expr": {
        "$and": [
          {
            "$gte": [
              "$timestamp",
              {
                "$const": "2022-04-23T00:00:00.000Z"
              }
            ]
          }
        ]
      }
    },
    "nReturned": 168940,
    "executionTimeMillisEstimate": 13576,
    "works": 6366939,
    "advanced": 168940,
    "needTime": 6197998,
    "needYield": 0,
    "saveState": 6430,
    "restoreState": 6430,
    "isEOF": 1,
    "docsExamined": 6366938,
    "alreadyHasObj": 0,
    "inputStage": {
      "stage": "IXSCAN",
      "nReturned": 6366938,
      "executionTimeMillisEstimate": 4838,
      "works": 6366939,
      "advanced": 6366938,
      "needTime": 0,
      "needYield": 0,
      "saveState": 6430,
      "restoreState": 6430,
      "isEOF": 1,
      "keyPattern": {
        "timestamp": 1
      },
      "indexName": "timestamp",
      "isMultiKey": false,
      "multiKeyPaths": {
        "timestamp": []
      },
      "isUnique": false,
      "isSparse": false,
      "isPartial": false,
      "indexVersion": 2,
      "direction": "forward",
      "indexBounds": {
        "timestamp": [
          "[MinKey, MaxKey]"
        ]
      },
      "keysExamined": 6366938,
      "seeks": 1,
      "dupsTested": 0,
      "dupsDropped": 0
    }
  },
  "allPlansExecution": []
}

[steevej (Steeve Juneau)]

Please post sample documents that were returned that were not supposed to be returned.

I can think of 2 reasons why all documents are returned.

1. all documents have timestamp $lt 2022-04-23
2. there is a type mismatch between the timestamp in the documents and the value of new Date().

Your $match can be simplified to

{ "$match" :
  {
    "timestamp" : { "$gte" , new Date( ... ) }
  }
}

[Lior_Alon]

Hey,

Just emphasizing, the end query returns the correct documents, the IXSCAN returns all.

The type inside mongodb is date

more noted - I am using nestjs. Could this be a moongose/nestjs issue? Using moongose versions 6.0.12

If this should work as is, then I will try different versions, or another collection and see what I am getting

[Lior_Alon]

Please notice this is the aggregation framework.

Just doubled check and when using the $expr operator the query works as expected.

[steevej (Steeve Juneau)]

Can you provide some sample documents from your collection?

Some that should be selected by the query and some that should not.

[Asya_Kamsky]

$match and find basically do the same thing. Without $expr they both use find expressions/semantics, to use aggregation semantics you need to use $expr. Is there a reason you are using $expr here? The index isn’t usable for inequality with $expr pre-5.0 and you say you are on 4.4…

Asya

[Lior_Alon]

Thank you for the kind response. I solved the issue by removing the $expr operator from the query, and writing it as Steeve suggested.

I was not aware that using $expr with $gte or $le (they are considered inequality operators?) will cause this issue. We will upgrade to mongo 5 asap but for now this bypasses the issue