Intersections of the ranges of multiple row values

Working with Data
1

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.

I will give an example of collections that can be.

Collection #1:

[
  {
    _id: 1,
    RoomFrom: 100,
    RoomTo: 200,
    PoolFrom: 150,
    PoolTo: 160,
    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: 430,
    PoolFrom: 500,
    PoolTo: 530,
    FloorFrom: 700,
    FloorTo: 711
  }
]

Collection #2:

[
  {
    _id: 1,
    RoomFrom: 100,
    PoolFrom: 150,
    PoolTo: 180,
    FloorFrom: 170,
    FloorTo: 180
  },
  {
    _id: 2,
    RoomFrom: 150,
    RoomTo: 300,
    PoolFrom: 170,
    FloorFrom: 170,
    FloorTo: 180
  },
  {
    _id: 3,
    RoomFrom: 210,
    RoomTo: 230,
    PoolFrom: 100,
    PoolTo: 290,
    FloorFrom: 500,
  },
  {
    _id: 4,
    RoomFrom: 300,
    PoolFrom: 170,
    FloorFrom: 600,
  },
  {
    _id: 5,
    RoomFrom: 400,
    RoomTo: 401,
    PoolFrom: 350,
    PoolTo: 470,
    FloorFrom: 700,
    FloorTo: 711
  }
]

Collection #3:

[
  {
    _id: 1,
    RoomFrom: 100,
    PoolFrom: 150,
    PoolTo: 180,
    FloorFrom: 170,
    FloorTo: 180
  },
  {
    _id: 2,
    RoomFrom: 150,
    RoomTo: 300,
    PoolFrom: 200,
    FloorFrom: 170,
    FloorTo: 180
  },
  {
    _id: 3,
    RoomFrom: 210,
    RoomTo: 230,
    PoolFrom: 100,
    PoolTo: 130,
    FloorFrom: 500,
  },
  {
    _id: 4,
    RoomFrom: 300,
    PoolFrom: 300,
    FloorFrom: 600,
  },
  {
    _id: 5,
    RoomFrom: 400,
    RoomTo: 401,
    PoolFrom: 270,
    PoolTo: 300,
    FloorFrom: 700,
    FloorTo: 711
  }
]

Now we have the ranges like this:

                     <- Input-Range  -> 
         โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€” Intersects
Range 1:  |โ€”โ€”โ€”โ€”โ€”|                                      No
Range 2:          |โ€”โ€”โ€”โ€”โ€”|                              Yes
Range 3:                   |โ€”โ€”โ€”โ€”โ€”|                     Yes
Range 4:                           |โ€”โ€”โ€”โ€”โ€”|             Yes
Range 5:                                    |โ€”โ€”โ€”โ€”โ€”|    No
Range 6:          |โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|             Yes

Now letโ€™s look at the filtering options.

RoomFrom[To]: Filter input range from = 350, to = 550. Now letโ€™s look at the collections.

Collection #1:

                                  350 <-Input-Range -> 550
โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€” Intersects
Object #1:|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|                                                    No
Object #2:     |โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|                                          No
Object #3:           |โ€”โ€”|                                                 No
Object #4:                    |โ€”โ€”โ€”โ€”|                                      Yes
Object #5:                              |โ€”โ€”โ€”|                             Yes

As a result of the query we should receive the following objects: #4, #5.

Collection #2:

                                  350 <-Input-Range -> 550
โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€” Intersects
Object #1:|----------------------------------------------------------     Yes
Object #2:     |-----------------------------------------------------     Yes
Object #3:           |โ€”โ€”|                                                 No
Object #4:                    |--------------------------------------     Yes
Object #5:                              |----------------------------     Yes

As a result of the query we should receive the following objects: #1, #2, #4, #5.

Collection #3:

                                  350 <-Input-Range -> 550
โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€” Intersects
Object #1:|----------------------------------------------------------     Yes
Object #2:     |โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|                                          No
Object #3:           |โ€”โ€”|                                                 No
Object #4:                    |--------------------------------------     Yes
Object #5:                              |โ€”โ€”โ€”|                             Yes

As a result of the query we should receive the following objects: #1, #4, #5.

Now letโ€™s complicate the filter and add one more parameter. Let me clarify that the sequence of filters is important. First comes the filter with the range RoomFrom[To], and after it comes the filter - PoolFrom[To].

  • RoomFrom[To]: Filter input range from = 350, to = 550.
  • PoolFrom[To]: Filter input range from = 50, to = 250.

Now letโ€™s look at the collections.

Collection #1:

                                  350 <-Input-Range -> 550 RoomFrom[To]
โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€” Intersects
Object #1:|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|                                                    No
Object #2:     |โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|                                          No
Object #3:           |โ€”โ€”|                                                 No
Object #4:                    |โ€”โ€”โ€”โ€”|                                      Yes
Object #5:                              |โ€”โ€”โ€”|                             Yes

    50 <-Input-Range ->  250 PoolFrom[To]
โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€” Intersects
Object #1:     |โ€”|                                                        Yes
Object #2:        |โ€”โ€”โ€”|                                                   Yes
Object #3:|โ€”|                                                             Yes
Object #4:                              |โ€”โ€”โ€”โ€”โ€”|                           No
Object #5:                                        |โ€”โ€”โ€”|                   No

The query should return no objects.

Collection #2:

                                  350 <-Input-Range -> 550
โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€” Intersects
Object #1:|----------------------------------------------------------     Yes
Object #2:     |-----------------------------------------------------     Yes
Object #3:           |โ€”โ€”|                                                 No
Object #4:                    |--------------------------------------     Yes
Object #5:                              |----------------------------     Yes

    50 <-Input-Range ->  250 PoolFrom[To]
โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€” Intersects
Object #1:     |โ€”โ€”โ€”|                                                      Yes
Object #2:       |---------------------------------------------------     Yes
Object #3:|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|                                           Yes
Object #4:       |---------------------------------------------------     Yes
Object #5:                         |โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|                         No

As a result of the query we should receive the following objects: #1, #2, #4.

Collection #3:

                                  350 <-Input-Range -> 550
โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€” Intersects
Object #1:|----------------------------------------------------------     Yes
Object #2:     |-----------------------------------------------------     Yes
Object #3:           |โ€”โ€”|                                                 No
Object #4:                    |--------------------------------------     Yes
Object #5:                              |----------------------------     Yes

    50 <-Input-Range ->  250 PoolFrom[To]
โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”|โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€” Intersects
Object #1:     |โ€”โ€”โ€”|                                                      Yes
Object #2:           |-----------------------------------------------     Yes
Object #3:|โ€”โ€”โ€”|                                                           Yes
Object #4:                    |--------------------------------------     No
Object #5:                 |โ€”โ€”โ€”|                                          No

As a result of the query we should receive the following objects: #1, #2.

If we add another filter FloorFrom[To], then the existing result should also be filtered - like the previous 2 examples.
As a result, we should get a universal query that will take into account the following:

  • only the filter value can be filled in - from
  • both filter values โ€‹โ€‹are filled - from and to
  • only the filter value can be filled in - to

User @ray (thanks a lot for this) gave a great working example, which only works for the filtering condition:

  • both filter values โ€‹โ€‹are filled - from and to

It remains to change the current query so that it takes into account the remaining 2 conditions:

  • only the filter value can be filled in - from
  • only the filter value can be filled in - to

It is also worth considering in the query that the objects may not be completely filled in.

Thanks.

2

I forgot about another important point: objects for which both fields are not filled in:

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

should not be included in the selection.

3

I started having a play about with this and it does not seem that bad unless Iโ€™m missing something.

Assuming that for a filter (from/to) you have both filled in then you have three conditions where a match occurs:

  • Ranges are wrapping the data points (i.e. filterFrom =< roomFrom and filterTo >= roomTo)
  • From is within the range for a row (filterFrom >= roomFrom AND filterFrom <= roomTo)
  • To is within the range for a row (filterTo >= roomFrom AND filterTo <= roomTo)

If you have a missing field then you can add that in to the logic above to check if itโ€™s not set.

You can do the same for all conditions, so for one example youโ€™d have something like this:

db.getCollection('Col1').aggregate([
{
    $addFields:{
        isRoomMatch:{
            $or:[
                {
                    //Check if filter wraps the room dimensions completely
                    $and:[
                        {$lte:[roomFrom, '$RoomFrom']},
                        {$gte:[roomTo, '$RoomTo']},
                    ]
                },
                {
                    //Check if we have one dimension within the room range
                    $or:[
                        {
                            //Check if the from filter with the room range
                            $and:[
                                {$lte:['$RoomFrom', roomFrom]},
                                {$gte:['$RoomTo', roomFrom]},
                            ]
                        },
                        {
                            //Check if the to filter with the room range
                            $and:[
                                {$lte:['$RoomFrom', roomTo]},
                                {$gte:['$RoomTo', roomTo]},
                            ]
                        }
                    ]
                }
            ]
        }
    }
}
])

Iโ€™ve added a field to the data for now for debugging, but you can just re-write it as a $match condition.
Youโ€™ll also want to check the indexes in use and that if youโ€™re using an $expr then the index is made use of, same with the above, test on your dataset and verify performance with your data.

You can also probably simplify the above using boolean logic but I have not done this to keep it simple and have other work I need to get on with this morning.

John

1 Like
4

I should also add, top marks for the formatting of the examples!

5

I second the following

6

Thanks for your example but I need use only find i.e db.collection.find(). I added my results.

7

Why do you need to only use find? You could re-format the above as a find filter condition as itโ€™s not doing anything that complex but you have a lot more options available to you if you have the option of using the aggregation pipeline.

8

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}
                }
			]
		}
	]
}
);
9

Firstly, such requirements. Secondly, I am new to this business, so it is a bit difficult for me to write queries.

Thank you for your time.

10

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.