$match for filtering backtracking of dates-CodePudding

I am trying to create a $match that will use one date parameter and it can do the backtracking of dates. What I am trying to achieve is to get all rooms that are occupied based on the filter that will be used. Please see the scenario below.

Raw Data

{
    "_id" : ObjectId("60d9681765077a71ae158625"),
    "room": "301",
    "startaccomodation":ISODate("2021-08-05T06:11:36.007Z"),
    "endaccomodation":ISODate("2021-08-10T06:11:36.007Z")
},
{
    "_id" : ObjectId("60dbf391e2759909d52d1917"),
    "room": "302",
    "startaccomodation":ISODate("2021-08-07T06:11:36.007Z"),
    "endaccomodation":ISODate("2021-08-09T06:11:36.007Z")
},
{
    "_id" : ObjectId("60dbf61d54b7c46bfa1b7954"),
    "room": "303",
    "startaccomodation":ISODate("2021-08-02T06:11:36.007Z"),
    "endaccomodation":ISODate("2021-08-05T06:11:36.007Z")
},
{
    "_id" : ObjectId("60dbf6ef6a9e0e09f9a4caa6"),
    "room": "304",
    "startaccomodation":ISODate("2021-08-06T06:11:36.007Z"),
    "endaccomodation":ISODate("2021-08-08T06:11:36.007Z")
},
{
    "_id" : ObjectId("60dbf6ef5805b16bf96286cc"),
    "room": "305",
    "startaccomodation":ISODate("2021-08-01T06:11:36.007Z"),
    "endaccomodation":ISODate("2021-08-05T06:11:36.007Z")
},
{
    "_id" : ObjectId("60dd0d7b1410931155f0bdd0"),
    "room": "306",
    "startaccomodation":ISODate("2021-08-02T06:11:36.007Z"),
    "endaccomodation":null
},
{
    "_id" : ObjectId("60dd0e04c02ff023ab091cd3"),
    "room": "307",
    "startaccomodation":ISODate("2021-08-06T06:11:36.007Z"),
    "endaccomodation":null
}

If I want to only get all rooms that are occupied on August 3, 2021. The output will be:

{
    "_id" : ObjectId("60dbf61d54b7c46bfa1b7954"),
    "room": "303",
    "startaccomodation":ISODate("2021-08-02T06:11:36.007Z"),
    "endaccomodation":ISODate("2021-08-05T06:11:36.007Z")
},
{
    "_id" : ObjectId("60dbf6ef5805b16bf96286cc"),
    "room": "305",
    "startaccomodation":ISODate("2021-08-01T06:11:36.007Z"),
    "endaccomodation":ISODate("2021-08-05T06:11:36.007Z")
},
{
    "_id" : ObjectId("60dd0d7b1410931155f0bdd0"),
    "room": "306",
    "startaccomodation":ISODate("2021-08-02T06:11:36.007Z"),
    "enddate": null
},

If I will put the filter TODAY, this will give the data of all rooms occupied as of now. The output will be:

{
    "_id" : ObjectId("60dd0d7b1410931155f0bdd0"),
    "room": "306",
    "startaccomodation":ISODate("2021-08-02T06:11:36.007Z"),
    "endaccomodation":null
},
{
    "_id" : ObjectId("60dd0e04c02ff023ab091cd3"),
    "room": "307",
    "startaccomodation":ISODate("2021-08-06T06:11:36.007Z"),
    "endaccomodation":null
}

My match is incomplete and no data output yet.

{
  $match: {
    $and: [
      {
        "startdate": {
          "$gte": ISODate("2021-08-03T00:00:00.000Z"),
          
        }
      },
      {
        "enddate": {
          "$lte": ISODate("2021-08-03T00:00:00.000Z"),
           
        }
      },
        
    ]
  }
}

Thank you in advance.

CodePudding user response：

I think you have reversed the date range logic.

It should be:

startaccomodation <= x <= endaccomodation

While there is endaccomodation with null, the second condition should be started with $or that fulfill any of these conditions:

endaccomodation is null
endaccomodation must be $gte the input date.

db.collection.aggregate([
  {
    $match: {
      $and: [
        {
          "startaccomodation": {
            "$lte": ISODate("2021-08-03T00:00:00.000Z")
          }
        },
        {
          $or: [
            {
              "endaccomodation": {
                $eq: null
              }
            },
            {
              "endaccomodation": {
                "$gte": ISODate("2021-08-03T00:00:00.000Z")
              }
            }
          ]
        }
      ]
    }
  }
])

Sample Mongo Playground