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

Working with Data
, ,
#1

I have a document like this:

{
  "_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"
  ]
}

I need to query by date but keep the previous index. For example:

match: {
  $gte: ISODate("2022-05-25T00:00:00.000+00:00")
  ...
}

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. How to do that with the aggregation framework?

#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