Question

Given a startQuery and endQuery, what Mongo filter will find documents with dates that overlap both query values?

By 'overlap,' I mean that the matching documents have a:

1. start before the startQuery AND
2. end after the endQuery AND
3. Have dates in between the startQuery and endQuery

Expected Behavior

startQuery and endQuery: 2022-01-10, 2022-01-20

Documents:

[
// 01-01 is before 01-10 and 02-02 is after 01-10
{title: "match1", start: "2022-01-01T00:00:00Z", end: "2022-02-02T00:00:00Z"},

// 01-15 and 01-16 are between 01-10 and 01-15
{title: "notMatch1", start: "2022-01-15T00:00:00Z", end: "2022-01-16T00:00:00Z"},

// 01-16 is not after 01-20
{title: "notMatch2", start: "2022-01-01T00:00:00Z", end: "2022-01-16T00:00:00Z"},
]

Return:

[{title: "match1", start: "2022-01-01T00:00:00Z", end: "2022-12-31T00:00:00Z"}]

Context

This is for finding events to display in a week view of a calendar. Filtering by startOfWeek to endOfWeek misses events that span 3 weeks and overlap the second week.

My best guess

Is this the best way? If so, how can I translate it to a Mongo filter?

queryStart = dayjs(start).dayOfYear() //10
queryEnd = dayjs(end).dayOfYear() //20 
queryArray = [10,11,12...18,19,20]    

for each document
   // this document's start and end:
   // 2022-01-01T00:00:00Z, 2022-02-02T00:00:00Z
   eventStart = dayjs(start).dayOfYear() // 1 (01-01)
   eventEnd = dayjs(end).dayOfYear()    //  33 (02-02)
   eventArray = [1,2,3, .... 31,32,33] 

   // true
   isMatch = (
               eventStart not between queryStart and queryEnd 
                &&
               eventEnd not between queryStart and queryEnd 
                &&
               any(eventArray) in (queryArray)
             ) 

CodePudding user response：

db.collection.aggregate([
  {
    "$match": {
      start: {
        "$lte": "2022-01-10"
      },
      end: {
        "$gte": "2022-01-20"
      }
    }
  }
])

mongoplayground

db.collection.aggregate([
  {
    "$match": {
      $expr: {
        "$and": [
          {
            "$lte": [
              { $week: { "$toDate": "$start" } },
              { $week: { "$toDate": "2022-01-10" } }
            ]
          },
          {
            "$gte": [
              { $week: { "$toDate": "$end" } },
              { $week: { "$toDate": "2022-01-20" } }
            ]
          }
        ]
      }
    }
  }
])

mongoplayground