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?

StackOverflow is not a free coding service. You're expected to try to solve the problem first. Update your question to show what you have already tried in a minimal reproducible example. Take the tour, and see How to Ask. — aneroid
– aneroid, Commented Sep 20, 2024 at 9:08
And now you're asking follow-up questions with completely different requirements. This may be an XY Problem. — aneroid
– aneroid, Commented Sep 20, 2024 at 9:10

Yong Shun · Accepted Answer · 2024-09-20 08:13:14Z

1

Think using the aggregation query to achieve the result.

$range - With the $range operator generate an array of numbers starting from 201 to 350.
$filter - Filter the number within the From and To fields.
$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

edited Sep 20, 2024 at 8:13

answered Sep 20, 2024 at 7:55

Yong Shun

54.8k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Valentin Borisenko Over a year ago

Thanks a lot. And what mean "$$this" and can I change "aggregate" with "find"?

Yong Shun Over a year ago

Hi @ValentinBorisenko, $$this is the variable that represents the iterated element in the array. It's normally used in these $filter, $map and $reduce operators that iterate each element in the array.

Yong Shun Over a year ago

For .find() query, it should be achievable: Demo

Valentin Borisenko Over a year ago

How will the query change if we only have "from" or "to" at the range?

Yong Shun Over a year ago

Do you mean that your document may not have From/To fields? If yes, it depends on your search criteria. If you want to bypass when either of the field not exist, you can check their existence with $type. Demo. Would be great to create another question post if you have different questions/requirements. Thanks.

|

Collectives™ on Stack Overflow

Intersection of the range of values

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related