Mongodb Question posted in
The official documentation can be found here.

MongoDB – Intersection of the range of multiple row values

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!!!):

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Maybe this will be useful for someone. At the moment I have such solutions.

    Filter - From: RoomFromFilterFrom = 100

    db.collection.find(
{
    $and: [
        {
            $or: [
                {
                    "RoomFrom": {$exists: true, $lte: 100},
                    "RoomTo": {$exists: false}
                },
                {
                    "RoomFrom": {$exists: true, $lte: 100},
                    "RoomTo": {$exists: true, $gte: 100}
                },
                {
                    "RoomFrom": {$exists: false},
                    "RoomTo": {$exists: true, $gte: 100}
                },
                {
                    "RoomFrom": {$exists: true, $gte: 100},
                    "RoomTo": {$exists: true, $gte: 100}
                }
            ]
        }
    ]
}
);

    Filter - From / To: RoomFromFilterFrom = 100 / RoomFromFilterTo = 300

    db.collection.find(
{
    $and: [
        {
            $or: [
                {
                    "RoomFrom": {$exists: true, $lte: 300},
                     "RoomTo": {$exists: true, $gte: 100}
                },
                {
                    "RoomFrom": {$exists: true, $lte: 300},
                    "RoomTo": {$exists: false}
                },
                {
                    "RoomTo": {$exists: true, $gte: 100},
                    "RoomFrom": {$exists: false}
                }
            ]
        }
    ]
}
);

    Filter - To: RoomFromFilterTo = 300

    db.collection.find(
{
    $and: [
        {
            $or: [
                {
                     "RoomTo": {$lte: 300}
                },
                {
                     "RoomFrom": {$lte: 300}
                },
                {
                     "RoomTo": {$exists: true, $lte: 300},
                     "RoomFrom": {$exists: false}
                },
                {
                     "RoomTo": {$gte: 300},
                     "RoomFrom": {$exists: false}
                }
            ]
        }
    ]
}
);

  2. 0

    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…)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search