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:
Now we have the range: 201 - 350. So in this range I have the objects in results:
and Object #1 / Object #6 not included into the selection result.
What is the query in this case?
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"
]
}
]
}
}
},
[]
]
}
}
}
])
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
]
}
})