mongodbmongodb-queryaggregation-frameworkintersection

MongoDB - Intersection of the range of multiple row values


Suppose we have a mongodb collection with 6 columns:

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

So in this range I have the objects in results:

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

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

But it's only for one case ...

Range variant #2

So in this range I have the objects in results:

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

Range variant #3

So in this range I have the objects in results:

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:

Thanks.


Solution

  • 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...)