CODEWITHSUNDEEP
mongodbmongodb-queryintersectionintersect
Valentin Borisenko
Valentin Borisenko

Reputation: 47

MongoDB v.3.2.11: Intersection of the range of multiple row values with object fields

This post is a continuation of the previous posts:

Suppose we have a mongodb collection with 6 columns in object field:

Now I would like to select rows where range** *From / *To intersect with another ranges.

For example:

[
  {
    _id: 1,
    "investmentParameters": {
      "capitalAppreciationFrom": 30,
      "capitalAppreciationTo": 60,
      "rentalYieldFrom": 30,
      "rentalYieldTo": 60,
      "occupancyRateFrom": 30,
      "occupancyRateTo": 60
    }
  },
  {
    _id: 2,
    "investmentParameters": {
      "capitalAppreciationFrom": 17,
      "capitalAppreciationTo": 80,
      "rentalYieldFrom": 17,
      "rentalYieldTo": 80,
      "occupancyRateFrom": 17,
      "occupancyRateTo": 80
    }
  },
  {
    _id: 3,
    "investmentParameters": {
      "capitalAppreciationFrom": 27,
      "capitalAppreciationTo": 87,
      "rentalYieldFrom": 27,
      "rentalYieldTo": 87,
      "occupancyRateFrom": 27,
      "occupancyRateTo": 87
    }
  },
  {
    _id: 4,
    "investmentParameters": {
      "capitalAppreciationFrom": 23,
      "capitalAppreciationTo": 57,
      "rentalYieldFrom": 23,
      "rentalYieldTo": 57,
      "occupancyRateFrom": 23,
      "occupancyRateTo": 57
    }
  }
]

** - range in this case is:

So lets call our filter fields like this:

  1. capitalAppreciationFilterFrom
  2. capitalAppreciationFilterFrom + capitalAppreciationFilterTo
  3. capitalAppreciationFilterTo
  1. rentalYieldFilterFrom
  2. rentalYieldFilterFrom + rentalYieldFilterTo
  3. rentalYieldFilterTo
  1. occupancyRateFilterFrom
  2. occupancyRateFilterFrom + occupancyRateFilterTo
  3. occupancyRateFilterTo

If we talk about fields in the DB, then we have the following correspondences with the filter:

We need to write queries that will select the intersections of ranges (not the range occurrences, but the intersection).

For example we take Object #1 and Condition #1:

occupancyRateFilterFrom - $gte: 30
rentalYieldFilterFrom - $gte: 20
rentalYieldFilterTo - $lte: 40

First range with from value - occupancyRateFilterFrom = $gte: 30 falls within the range of 30 to 60 (investmentParameters.capitalAppreciationFrom = 30 and investmentParameters.capitalAppreciationTo = 60). Next range with from + to values - rentalYieldFilterFrom = $gte: 20 / rentalYieldFilterTo = $lte: 40 intersects (is included in the range investmentParameters.rentalYieldFrom = 30 and investmentParameters.rentalYieldTo = 60) with the range from 30 to 60. Thus, the Object #1 gets into the selection. The same thing with the other objects - all get into the selection under current query conditions.

Also, if it may be important:

/# mongo --version                                                                                               
MongoDB version v3.2.11

We need to write queries that will select the intersections of ranges (not the range occurrences, but the intersection).

For example I will give you the conditions under which 4 objects should always be selected.

Condition #1

occupancyRateFilterFrom - $gte: 30
rentalYieldFilterFrom - $gte: 20
rentalYieldFilterTo - $lte: 40

Condition #2

occupancyRateFilterFrom - $gte: 27
rentalYieldFilterFrom - $gte: 20
rentalYieldFilterTo - $lte: 40

Condition #3

occupancyRateFilterFrom  - $gte: 37
rentalYieldFilterFrom - $gte: 20
rentalYieldFilterTo - $lte: 40

Condition #4

occupancyRateFilterFrom  - $gte: 40
rentalYieldFilterFrom - $gte: 20
rentalYieldFilterTo - $lte: 40

Under all these 4 conditions, 4 objects must always be selected.

Now I change occupancyRateFilterFrom - $lte: 16 so we have the condition:

occupancyRateFilterTo - $gte: 16
rentalYieldFilterFrom - $gte: 20
rentalYieldFilterTo - $lte: 40

then none of the 4 objects should be included in the query result.

Next we have the condition:

capitalAppreciationFilterFrom - $gte: 62
occupancyRateFilterFrom - $gte: 16
rentalYieldFilterFrom - $gte: 20
rentalYieldFilterTo - $lte: 40

As a result of the selection, we should see the following objects:

And of course, the sampling conditions can be combined:

occupancyRateFilterFrom - $gte: x
rentalYieldFilterFrom - $gte: x
rentalYieldFilterTo - $lte: x

occupancyRateFilterFrom - $gte: x
occupancyRateFilterTo - $lte: x
rentalYieldFilterFrom - $gte: x
rentalYieldFilterTo - $lte: x

capitalAppreciationFilterFrom - $gte: x
occupancyRateFilterFrom - $gte: x
occupancyRateFilterTo - $lte: x
rentalYieldFilterFrom - $gte: x
rentalYieldFilterTo - $lte: x

capitalAppreciationFilterFrom - $gte: x
capitalAppreciationFilterTo - $gte: x
occupancyRateFilterFrom - $gte: x
occupancyRateFilterTo - $lte: x
rentalYieldFilterFrom - $gte: x
rentalYieldFilterTo - $lte: x

capitalAppreciationFilterFrom - $gte: x

capitalAppreciationFilterTo - $gte: x

and so on ...

Each subsequent filter reduces the current selection, thereby narrowing the selection result.

Upvotes: -1

Views: 127

Answers (2)

Valentin Borisenko
Valentin Borisenko

Reputation: 47

Maybe this will be useful for someone. At the moment I have such solutions.

Filter - From: capitalAppreciationFromFilter = 100

db.collection.find(
{
    $and: [
        {
            $or: [
                {
                    "investmentParameters.capitalAppreciationFrom": {$exists: true, $lte: 100},
                    "investmentParameters.capitalAppreciationTo": {$exists: false}
                },
                {
                    "investmentParameters.capitalAppreciationFrom": {$exists: true, $lte: 100},
                    "investmentParameters.capitalAppreciationTo": {$exists: true, $gte: 100}
                },
                {
                    "investmentParameters.capitalAppreciationFrom": {$exists: false},
                    "investmentParameters.capitalAppreciationTo": {$exists: true, $gte: 100}
                },
                {
                    "investmentParameters.capitalAppreciationFrom": {$exists: true, $gte: 100},
                    "investmentParameters.capitalAppreciationTo": {$exists: true, $gte: 100}
                }
            ]
        }
    ]
}
);

Example.

Filter - From / To: capitalAppreciationFromFilter = 100 / capitalAppreciationToFilter = 300

db.collection.find(
{
    $and: [
        {
            $or: [
                {
                    "investmentParameters.capitalAppreciationFrom": {$exists: true, $lte: 300},
                     "investmentParameters.capitalAppreciationTo": {$exists: true, $gte: 100}
                },
                {
                    "investmentParameters.capitalAppreciationFrom": {$exists: true, $lte: 300},
                    "investmentParameters.capitalAppreciationTo": {$exists: false}
                },
                {
                    "investmentParameters.capitalAppreciationTo": {$exists: true, $gte: 100},
                    "investmentParameters.capitalAppreciationFrom": {$exists: false}
                }
            ]
        }
    ]
}
);

Example.

Filter - To: capitalAppreciationToFilter = 300

db.collection.find(
{
    $and: [
        {
            $or: [
                {
                     "investmentParameters.capitalAppreciationTo": {$lte: 300}
                },
                {
                     "investmentParameters.capitalAppreciationFrom": {$lte: 300}
                },
                {
                     "investmentParameters.capitalAppreciationTo": {$exists: true, $lte: 300},
                     "investmentParameters.capitalAppreciationFrom": {$exists: false}
                },
                {
                     "investmentParameters.capitalAppreciationTo": {$gte: 300},
                     "investmentParameters.capitalAppreciationFrom": {$exists: false}
                }
            ]
        }
    ]
}
);

Example.

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59563

This is not a valid answer, however it may help you to formalize a better question and enables other people to give you a solution.

Maybe as a starting point, you should make a small drawing to visualize your condition of intersecting ranges. Could be like this:

                     <- Input-Range  -> 
         ————————————|————————————————|———————————— Intersects?
Range 1:  |—————|                                      NO
Range 2:          |—————|                              YES
Range 3:                   |—————|                     YES
Range 4:                           |—————|             YES
Range 5:                                    |—————|    NO
Range 6:          |——————————————————————|             YES

Or for a single value, it could be

                Input-Value 
         ————————————|—————————————— Intersects?
Range 1:  |—————|                       NO
Range 2:          |—————|               YES
Range 3:                   |—————|      NO

Then it is easier to compose the condition by code.

You mention these three cases:

** - range in this case is:

  • value - from
  • values - from and to
  • value - to

You explained the first and second one, but nobody has a clue what "value - to" means.

Condition #1

occupancyRateFilterFrom - $gte: 30 
rentalYieldFilterFrom - $gte: 20 
rentalYieldFilterTo - $lte: 40

First range with from value - occupancyRateFilterFrom = $gte: 30 falls within the range of 30 to 60 (investmentParameters.capitalAppreciationFrom = 30 and investmentParameters.capitalAppreciationTo = 60).

Why does a occupancyRate filter has an effect on any capitalAppreciation range?

Condition #2

occupancyRateFilterFrom - $gte: 27 
rentalYieldFilterFrom - $gte: 20 
rentalYieldFilterTo - $lte: 40

Why do you expect all 4 documents should be selected? Document #1 is

{
   _id: 1,
   investmentParameters: {
      capitalAppreciationFrom: 30,
      capitalAppreciationTo: 60,
      rentalYieldFrom: 30,
      rentalYieldTo: 60,
      occupancyRateFrom: 30,
      occupancyRateTo: 60
   }
}

{ occupancyRateFrom: 30, occupancyRateTo: 60 } does not fall into 27, thus Condition #2 should not select document #1.

Next we have the condition:

capitalAppreciationFilterFrom - $gte: 62
occupancyRateFilterFrom - $gte: 16
rentalYieldFilterFrom - $gte: 20
rentalYieldFilterTo - $lte: 40

As a result of the selection, we should see the following objects:

  • Object #2
  • Object #3

Why?

Document #2 has { occupancyRateFrom: 17, occupancyRateTo: 80} and document #3 has { occupancyRateFrom: 27, occupancyRateTo: 87} which both do not fall into 16.

Note, I am not going to start again a discussion with comments. Take this answer, review your requirements, ask a new proper question with valid and consistent input data, expected result and what you have tried. Otherwise leave it!

Upvotes: 2

Related Questions