mongodbmongodb-queryintersectionintersect

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.

Solution

  • 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.