title: MongoDB Aggregation with $or and $and
body: <p>I have the following MonogoDb collection</p>
  <pre><code>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 }
  });
  </code></pre>
  <p>I want to get all shifts with a given ShiftType that overlap with a given time span.</p>
  <p>lets say from 2021-07-19 (1626652800000) to 2021-07-23 (1626998400000)</p>
  <pre><code>[
    {
      '$match': {
        'startDate': {
          '$gte': 1626652800000
        }, 
        'endDate': {
          '$lte': 1626998400000
        }, 
        'shiftType': new ObjectId('60a36ea2e2f5035b26f23430')
      }
    }
  ]
  </code></pre>
  <p>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.</p>
  <p>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.</p>
  <p>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:</p>
  <pre><code>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
                  &quot;startDate&quot;: {
                      '$lte': date1
                  },
                  &quot;endDate&quot; {
                      '$gte': date1
                  }
              ],
              '$and': [ // shift starts in timespan and ends after time span
                  &quot;startDate&quot;: {
                      '$lte': date2
                  },
                  &quot;endDate&quot; {
                      '$gte': date2
                  }
              ],
              '$and': [ // shift starts before timespand and ends after time span
                  &quot;startDate&quot;: {
                      '$lte': date1
                  },
                  &quot;endDate&quot; {
                      '$gte': date2
                  }
              ]
          ],
          'shiftType': ObjectId('60a36ea2e2f5035b26f23430')
      }
    }
  ]
  </code></pre>
  <p><strong>Solution</strong>
  Thanks to Koodies I was able to solve it like this:</p>
  <pre><code>[
    {
      '$match': {
        '$or': [
          {
            'startDate': {
              '$gte': 1625443200000, 
              '$lte': 1625788800000
            }
          }, {
            'endDate': {
              '$lte': 1625788800000, 
              '$gte': 1625443200000
            }
          }, {
            '$and': [
              {
                'startDate': {
                  '$lte': 1625443200000
                }
              }, {
                'endDate': {
                  '$gte': 1625788800000
                }
              }
            ]
          }
        ], 
        'shiftType': new ObjectId('60a278355c7462f42b3d7895')
      }
    }
  ]
  </code></pre>