MongoDB Aggregation with $or and $and

Question

I have the following MonogoDb collection

ShiftMgmt.shiftSchema = new mongoose.Schema({
    assignedUser: { type: mongoose.Schema.Types.ObjectId, required: true, ref: 'users' },
    shiftType: { type: mongoose.Schema.Types.ObjectId, required: true, ref: 'shiftTypes' },
    team: { type: mongoose.Schema.Types.ObjectId, required: true, ref: 'teams' },
    startDate: { type: Number, required: true },
    endDate: { type: Number, required: true }
});

I want to get all shifts with a given ShiftType that overlap with a given time span.

lets say from 2021-07-19 (1626652800000) to 2021-07-23 (1626998400000)

[
  {
    '$match': {
      'startDate': {
        '$gte': 1626652800000
      }, 
      'endDate': {
        '$lte': 1626998400000
      }, 
      'shiftType': new ObjectId('60a36ea2e2f5035b26f23430')
    }
  }
]

Above pipeline will return every shift that starts and ends in the given time span. But I also want to get all the shifts where the start and end date overlaps with the time span.

so for example I want it to also find shifts that started on 2021-07-16 but will end on 2021-07-21 and those that will start on, for example on 2021-07-20 and end on 2021-07-25.

I guess to achieve this, I need $and and $or in my pipeline, but I have issues finding the correct Syntax. Here is my non working pipeline so far:

let date1 = 1626652800000 // 2021-07-19
let date2 = 1627041600000 // 2021-07-23

[
  {
    '$match': {
        '$or': [ 
            '$and': [ // shift lies betweend dates
                'startDate': {
                    '$gte': date1
                },
                'endDate': {
                    '$lte': date2
                }
            ],
            '$and': [ // shift starts before timespan and ends in time span
                "startDate": {
                    '$lte': date1
                },
                "endDate" {
                    '$gte': date1
                }
            ],
            '$and': [ // shift starts in timespan and ends after time span
                "startDate": {
                    '$lte': date2
                },
                "endDate" {
                    '$gte': date2
                }
            ],
            '$and': [ // shift starts before timespand and ends after time span
                "startDate": {
                    '$lte': date1
                },
                "endDate" {
                    '$gte': date2
                }
            ]
        ],
        'shiftType': ObjectId('60a36ea2e2f5035b26f23430')
    }
  }
]

Solution Thanks to Koodies I was able to solve it like this:

[
  {
    '$match': {
      '$or': [
        {
          'startDate': {
            '$gte': 1625443200000, 
            '$lte': 1625788800000
          }
        }, {
          'endDate': {
            '$lte': 1625788800000, 
            '$gte': 1625443200000
          }
        }, {
          '$and': [
            {
              'startDate': {
                '$lte': 1625443200000
              }
            }, {
              'endDate': {
                '$gte': 1625788800000
              }
            }
          ]
        }
      ], 
      'shiftType': new ObjectId('60a278355c7462f42b3d7895')
    }
  }
]

user_13505073 · Answer

you are heading in the right direction but there's no need for you to use $and. Here's how's your logic could derive into, just have to replace the startTime and endTime.

db.collection.aggregate([
  {
    $match: {
      "$or": [
        {
          "startDate": {
            "$gte": startTime,
            "$lte": endTime
          }
        },
        {
          "endDate": {
            "$lte": endTime,
            "$gte": startTime
          }
        }
      ]
    }
  }
])

I have created a quick sample on mongo playground with 5 documents. The one with counter 4 & 5 is the outlier that you do not need.
Mongo Playground

MongoDB Aggregation with $or and $and

1 Answers