I'm using $elemMatch in an aggregation $match step. My simpler $elemMatch conditions work just fine, such as equality or comparison, but I can't get it to work when I include a more complex date math condition.

I want to exclude documents where the time elapsed since a date value on the element (order.timestamp) is less than a given threshold (i.e. only return documents that have array elements with a date older than X seconds).

I've generally tried many variations of $dateDiff, $cond, $expr, and paths but I can't seem to get anything to work as desired. One attempt of many can be found below. Is there an issue that prevents using $dateDiff logic inside an $elemMatch field?

const match = {
  $match: {
    cardTokens: {
      $elemMatch: {
        owner: null,
        serialNumber: { $gt: 1 },
        releaseDate: { $ne: null, $lte: new Date() },
        order: {
          $gt: [
            {
              $dateDiff: {
                startDate: '$timestamp',
                endDate: new Date(),
                unit: 'seconds',
              },
            },
            60,
          ],
        },
      },
    },
  },
};

My data looks approximately like this. I am expecting to match the second document because the second array element with serialNumber 10 matches all the $elemMatch conditions...

[
  {
    "_id": "46f39c4e-ddeb-4ca1-8144-209450137727",
    "items": [
      {
        "serialNumber": 1,
        "owner": {
          "id": "abb02c11-8872-401f-a081-fa5ab97f1574"
        },
        "releaseDate": { "$date": "2021-05-18T13:12:28.794Z" },
        "order": null
      },
      {
        "serialNumber": 10,
        "owner": {
          "id": "2a3e4bb4-7dc0-43c4-bb9a-0e45af23cd49"
        },
        "releaseDate": { "$date": "2020-05-18T20:16:38.985Z" },
        "order": {
          "id": "fd2bf5b8-a3b2-4d29-9c07-c59596f7bc7a",
          "timestamp": { "$date": "2023-03-15T14:19:13.961Z" }
        }
      }
    ]
  },
  {
    "_id": "b6a53532-1b28-4c23-93a2-38d3bb902ead",
    "items": [
      {
        "serialNumber": 1,
        "owner": null,
        "releaseDate": null,
        "order": null
      },
      {
        "serialNumber": 10,
        "owner": null,
        "releaseDate": { "$date": "2021-12-02T03:23:36.520Z" },
        "order": {
          "id": "f2bb864d-cf92-4617-9789-a895748b4e6c",
          "timestamp": { "$date": "2021-04-27T10:18:03.190Z" }
        }
      }
    ]
  }
]

CodePudding user response：

There is a problem with your approach, it stems from these 3 facts:

1. $dateDiff is an aggregation operator, thus it cannot be used in a $match stage unless wrapped by the $expr operator.

2. $expr has to be at the top level of the $match stage

3. $elemMatch is not an aggregation operators, thus it cannot be used inside an $expr operator.

So basically combining these 3 operators is impossible. as $expr has to be on top, you need the $dateDiff to be within it but $elemMatch cannot be used within it.

So what you need to do is just use some other aggregation operators to achieve these, they might have some overhead compared to elemMatch but it's the best we've got. You can get this done in multiple ways, here is what I consider to be the most straight forward using $filter:

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $gt: [
          {
            $size: {
              $filter: {
                input: "$cardTokens",
                cond: {
                  $and: [
                    {
                      $eq: [
                        "$$this.owner",
                        null
                      ]
                    },
                    {
                      $gt: [
                        "$$this.serialNumber",
                        1
                      ]
                    },
                    {
                      $lte: [
                        "$$this.releaseDate",
                        "$$NOW"
                      ]
                    },
                    {
                      $gt: [
                        {
                          $dateDiff: {
                            startDate: "$$this.order.timestamp",
                            endDate: "$$NOW",
                            unit: "second"
                          }
                        },
                        60
                      ]
                    }
                  ]
                }
              }
            }
          },
          0
        ]
      }
    }
  }
])

Mongo Playground