Intersection of the range of values

Question

Suppose we have a mongodb collection with 2 columns: From, To. Now I would like to select rows where range From / To intersect with another range.

For example:

• Object #1: From = 100, To = 200
• Object #2: From = 150, To = 300
• Object #3: From = 210, To = 230
• Object #4: From = 290, To = 300
• Object #5: From = 347, To = 349
• Object #6: From = 400, To = 450

Now we have the range: 201 - 350. So in this range I have the objects in results:

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

and Object #1 / Object #6 not included into the selection result.

What is the query in this case?

Answer 1

Think using the aggregation query to achieve the result.

1. $range - With the $range operator generate an array of numbers starting from 201 to 350.

2. $filter - Filter the number within the From and To fields.

3. $ne - Filter the document with the array generated from 2 is not an empty array.

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $ne: [
          {
            $filter: {
              input: {
                $range: [
                  201,
                  350,
                  1
                ]
              },
              cond: {
                $and: [
                  {
                    $gte: [
                      "$$this",
                      "$From"
                    ]
                  },
                  {
                    $lte: [
                      "$$this",
                      "$To"
                    ]
                  }
                ]
              }
            }
          },
          []
        ]
      }
    }
  }
])

Demo @ Mongo Playground

---

Alternatively, works with $anyElementTrue and $map operators. You can apply in the .find() query.

db.collection.find({
  $expr: {
    $eq: [
      {
        $anyElementTrue: {
          $map: {
            input: {
              $range: [
                201,
                350,
                1
              ]
            },
            in: {
              $and: [
                {
                  $gte: [
                    "$$this",
                    "$From"
                  ]
                },
                {
                  $lte: [
                    "$$this",
                    "$To"
                  ]
                }
              ]
            }
          }
        }
      },
      true
    ]
  }
})

Demo 2 @ Mongo Playground