How to perform an aggregate, returning only documents where an array of subdocuments have a date that falls between 2 dates

[Tim_Horwood]

I have a collection of bookings, each booking has an array of days the booking covers, each day has a start and end date.

I want to return only the bookings that have atleast 1 day within a set of dates. The below feels like it should work but it returns nothing all the time.

// Booking
{
  title: "Booking 1",
  days: [
    { start: "2022-12-01T08:30:00.000+00:00", end: "2022-12-03T05:30:00.000+00:00" }
  ]
}

const start = "2022-12-01";
const end = "2022-12-07";

BookingsBooking.aggregate([
{
  $match: {
      $and: [
        {
          "days.start": {
            $gte: new Date(start),
	  },
          "days.start": {
            $lte: new Date(end),
          },
        },
      ],
    }
  }
]);

I have tried quite a few variations of the above but can’t seem to figure it out.

Thanks!

[steevej (Steeve Juneau)]

Are your start and end dates in Booking Date object or strings?

To be able to compare them the types must matches. You are using new Date() in your aggregation so start and end must be Date, not string. From here it looks like they are strings.

Your query is wrong because your are query days.start twice, once with new Date( start ) and the second time with new Date( end ).

Look at $elemMatch because I suspect you want your 2 conditions to be true for the same array element.

[Tim_Horwood]

Thanks for the reply!

$elemMatch is one of the variations I have attempted but you were correct about the date format being the issue.

The below gives me exactly what I want:

$match: {
					days: {
						$elemMatch: {
							start: {
								$gte: new Date(start).toISOString(),
								$lte: new Date(end).toISOString(),
							},
						},
					},
				},

Thanks!

[steevej (Steeve Juneau)]

Mark one of the post as the solution so that other forum’s users know.