Array of dates, match date range, including the previous index

Working with Data
, ,
5

Hi @pseudo.charles,

match: {
$gte: ISODate(“2022-05-25T00:00:00.000+00:00”)
…
}

The above indicates greater than or equal to "2022-05-25T00:00:00.000+00" (25th of May 2022). However, below you wrote:

Should return "2022-05-09T00:00:00.000+00:00", "2022-05-31T00:00:00.000+00:00" .
Because the range matches "2022-05-31T00:00:00.000+00:00" and "2022-05-09T00:00:00.000+00:00" is the previous one

The first date mentioned is "2022-05-09T00:00:00.000+00:00" (9th of May 2022). This is not greater than or equal to the initial date mentioned in your $match example.

Additionally, the “dates” mentioned inside the date array are string values. Is this expected? In the example below, without converting these values to dates using $toDate no documents are returned in the filteredDates array.

To clarify, can you advise on the expected / desired output documents?

In the meantime, please see the below example aggregation that uses $filter to retrieve the “dates” within the "dates" array that are greater than or equal to ISODate("2022-05-25T00:00:00.000Z":

DB> db.dates.aggregate({
  '$addFields': {
    filteredDates: {
      '$filter': {
        input: '$dates',
        as: 'date',
        cond: {
          '$gte': [
            { '$toDate': '$$date' },
            ISODate("2022-05-25T00:00:00.000Z")
          ]
        }
      }
    }
  }
})
/// Output:
[
  {
    _id: 17995,
    dates: [
      '2022-05-05T00:00:00.000+00:00',
      '2022-05-09T00:00:00.000+00:00',
      '2022-05-31T00:00:00.000+00:00'
    ],
    filteredDates: [ '2022-05-31T00:00:00.000+00:00' ] /// <--- Filtered dates
  }
]

For your reference for the above example aggregation:

Please test this thoroughly in a test environment to ensure it meets your requirements / suits your use cases as I have only used the sample document you provided.

Hope this helps.

Regards,
Jason

1 Like