MongoDB - Intersection of the range of multiple row values

Question

Suppose we have a mongodb collection with 6 columns:

• RoomFrom
• RoomTo
• PoolFrom
• PoolTo
• FloorFrom
• FloorTo

Now I would like to select rows where range *From / *To intersect with another ranges.

For example:

[
  {
    _id: 1,
    RoomFrom: 100,
    RoomTo: 200,
    PoolFrom: 150,
    PoolTo: 155,
    FloorFrom: 170,
    FloorTo: 180
  },
  {
    _id: 2,
    RoomFrom: 150,
    RoomTo: 300,
    PoolFrom: 170,
    PoolTo: 200,
    FloorFrom: 170,
    FloorTo: 180
  },
  {
    _id: 3,
    RoomFrom: 210,
    RoomTo: 230,
    PoolFrom: 100,
    PoolTo: 110,
    FloorFrom: 500,
    FloorTo: 505
  },
  {
    _id: 4,
    RoomFrom: 300,
    RoomTo: 350,
    PoolFrom: 400,
    PoolTo: 450,
    FloorFrom: 600,
    FloorTo: 650
  },
  {
    _id: 5,
    RoomFrom: 400,
    RoomTo: 401,
    PoolFrom: 500,
    PoolTo: 503,
    FloorFrom: 700,
    FloorTo: 711
  }
]

Now we have the ranges:

Range variant #1

• RoomFrom = 201
• RoomTo = 350

So in this range I have the objects in results:

• Object #2
• Object #3
• Object #4

What is the query in this case with "find"?

I have a good examples from Yong Shun (thanks a lot!!!):

• https://mongoplayground.net/p/Hx6XZwMvbb7
• https://mongoplayground.net/p/0vmG5yQbzyV
• https://mongoplayground.net/p/-5LcMtHxIn7
• https://mongoplayground.net/p/hd6Sz1iL1Zx

But it's only for one case ...

Range variant #2

• RoomFrom = 201
• RoomTo = 350
• PoolFrom = 100
• PoolTo = 350

So in this range I have the objects in results:

• Object #2
• Object #3

What is the query in this case with "find"?

Range variant #3

• RoomFrom = 201
• RoomTo = 350
• PoolFrom = 100
• PoolTo = 350
• FloorFrom = 180
• FloorTo = 185

So in this range I have the objects in results:

• Object #2

What is the query in this case with "find"?

This one https://mongoplayground.net/p/LDvAlyERpXD works fine but only for one pair RoomFrom / RoomTo and this one for 3 pairs: https://mongoplayground.net/p/81MKW9AkelA.

So I need queries when have ranges for each pair:

• from
• to

Thanks.

Answer 1

Using the algorithm of interval intersection in this post, we see that 2 intersections do not overlap when either 1 start is larger than the end of the other interval. We can use $not to check for the opposite, where an overlap happens.

In MongoDB, this would be like the below expression:

{
  $not: {
    "$or": [
      {
        $gt: [
          "$$roomFromInput",
          "$RoomTo"
        ]
      },
      {
        $gt: [
          "$RoomFrom",
          "$$roomToInput"
        ]
      }
    ]
  }
}

The remaining work is just repeating it for pool and floor, which we can chain them up using $and. Here, I used $let to organize the variables.

db.collection.find({
  $expr: {
    $let: {
      vars: {
        roomFromInput: 201,
        roomToInput: 350,
        poolFromInput: 100,
        poolToInput: 350,
        floorFromInput: 180,
        floorToInput: 185
      },
      in: {
        $and: [
          //room
          {
            $not: {
              "$or": [
                {
                  $gt: [
                    "$$roomFromInput",
                    "$RoomTo"
                  ]
                },
                {
                  $gt: [
                    "$RoomFrom",
                    "$$roomToInput"
                  ]
                }
              ]
            }
          },
          //pool
          {
            $not: {
              "$or": [
                {
                  $gt: [
                    "$$poolFromInput",
                    "$PoolTo"
                  ]
                },
                {
                  $gt: [
                    "$PoolFrom",
                    "$$poolToInput"
                  ]
                }
              ]
            }
          },
          //floor
          {
            $not: {
              "$or": [
                {
                  $gt: [
                    "$$floorFromInput",
                    "$FloorTo"
                  ]
                },
                {
                  $gt: [
                    "$FloorFrom",
                    "$$floorToInput"
                  ]
                }
              ]
            }
          }
        ]
      }
    }
  }
})

Mongo Playground

---

Note:

1. This assumes the input intervals are well-formed. (i.e. start is less than the end, the values are valid numbers...)
2. This assumes the fields in the documents are well-formed. (i.e. are numbers/not strings, no missing/null fields...)