Intersections of the ranges of multiple row values

Working with Data
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