Question 411476 in Mongodb Question posted in
The official documentation can be found here.

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:

  • investmentParameters.capitalAppreciationFrom
  • investmentParameters.capitalAppreciationTo
  • investmentParameters.rentalYieldFrom
  • investmentParameters.rentalYieldTo
  • investmentParameters.occupancyRateFrom
  • investmentParameters.occupancyRateTo

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:

  • value – from
  • values – from and to
  • value – to

So lets call our filter fields like this:

  • capitalAppreciationFilter which can be:
  1. capitalAppreciationFilterFrom
  2. capitalAppreciationFilterFrom + capitalAppreciationFilterTo
  3. capitalAppreciationFilterTo
  • rentalYieldFilter which can be:
  1. rentalYieldFilterFrom
  2. rentalYieldFilterFrom + rentalYieldFilterTo
  3. rentalYieldFilterTo
  • occupancyRateFilter which can be:
  1. occupancyRateFilterFrom
  2. occupancyRateFilterFrom + occupancyRateFilterTo
  3. occupancyRateFilterTo

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

  • investmentParameters.capitalAppreciationFrom – capitalAppreciationFilterFrom
  • investmentParameters.capitalAppreciationTo – capitalAppreciationFilterTo
  • investmentParameters.rentalYieldFrom – rentalYieldFilterFrom
  • investmentParameters.rentalYieldTo – rentalYieldFilterTo
  • investmentParameters.occupancyRateFrom – occupancyRateFilterFrom
  • investmentParameters.occupancyRateTo – occupancyRateFilterTo

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:

  • Object #2
  • Object #3

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search