MongoDB 3.2.11：带对象字段的多值范围交集查询实现

系列文章背景 #

本文是以下系列文章的续篇：

• 数值范围的交集
• MongoDB - 多行数值范围的交集
• MongoDB：带对象字段的多行数值范围交集

集合结构说明 #

假设MongoDB集合中的文档包含嵌套对象字段investmentParameters，该对象包含6个范围字段：

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

示例集合数据 #

[
  {
    _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
    }
  }
]

过滤范围的三种形式 #

• 仅指定from值
• 同时指定from和to值
• 仅指定to值

过滤字段定义 #

每个指标对应三种可选过滤形式：

• capitalAppreciationFilter：
  1. 仅capitalAppreciationFilterFrom
  2. capitalAppreciationFilterFrom + capitalAppreciationFilterTo
  3. 仅capitalAppreciationFilterTo
• rentalYieldFilter：
  1. 仅rentalYieldFilterFrom
  2. rentalYieldFilterFrom + rentalYieldFilterTo
  3. 仅rentalYieldFilterTo
• occupancyRateFilter：
  1. 仅occupancyRateFilterFrom
  2. occupancyRateFilterFrom + occupancyRateFilterTo
  3. 仅occupancyRateFilterTo

字段对应关系 #

数据库字段与过滤字段的映射：

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

查询需求：范围交集匹配 #

需编写MongoDB查询语句，实现范围交集匹配（而非范围包含），即文档的目标范围与过滤范围存在重叠部分即符合条件。以下是具体条件示例及预期结果：

示例1：返回全部4条文档的条件 #

以下4种过滤条件均需选中所有4条文档：

• 条件1：

  occupancyRateFilterFrom: { $gte: 30 },
  rentalYieldFilterFrom: { $gte: 20 },
  rentalYieldFilterTo: { $lte: 40 }
  

• 条件2：

  occupancyRateFilterFrom: { $gte: 27 },
  rentalYieldFilterFrom: { $gte: 20 },
  rentalYieldFilterTo: { $lte: 40 }
  

• 条件3：

  occupancyRateFilterFrom: { $gte: 37 },
  rentalYieldFilterFrom: { $gte: 20 },
  rentalYieldFilterTo: { $lte: 40 }
  

• 条件4：

  occupancyRateFilterFrom: { $gte: 40 },
  rentalYieldFilterFrom: { $gte: 20 },
  rentalYieldFilterTo: { $lte: 40 }
  

示例2：返回空结果的条件 #

当过滤条件为以下内容时，需返回空结果：

occupancyRateFilterTo: { $gte: 16 },
rentalYieldFilterFrom: { $gte: 20 },
rentalYieldFilterTo: { $lte: 40 }

示例3：返回指定文档的条件 #

当过滤条件为以下内容时，需返回_id为2、3的文档：

capitalAppreciationFilterFrom: { $gte: 62 },
occupancyRateFilterFrom: { $gte: 16 },
rentalYieldFilterFrom: { $gte: 20 },
rentalYieldFilterTo: { $lte: 40 }

过滤条件组合规则 #

过滤条件支持多种组合形式，新增的每个过滤条件需逐步缩小查询结果范围，示例组合如下：

// 组合1
occupancyRateFilterFrom: { $gte: x },
rentalYieldFilterFrom: { $gte: x },
rentalYieldFilterTo: { $lte: x }

// 组合2
occupancyRateFilterFrom: { $gte: x },
occupancyRateFilterTo: { $lte: x },
rentalYieldFilterFrom: { $gte: x },
rentalYieldFilterTo: { $lte: x }

// 组合3
capitalAppreciationFilterFrom: { $gte: x },
occupancyRateFilterFrom: { $gte: x },
occupancyRateFilterTo: { $lte: x },
rentalYieldFilterFrom: { $gte: x },
rentalYieldFilterTo: { $lte: x }

// 组合4
capitalAppreciationFilterFrom: { $gte: x },
capitalAppreciationFilterTo: { $gte: x },
occupancyRateFilterFrom: { $gte: x },
occupancyRateFilterTo: { $lte: x },
rentalYieldFilterFrom: { $gte: x },
rentalYieldFilterTo: { $lte: x }

// 组合5
capitalAppreciationFilterFrom: { $gte: x }

// 组合6
capitalAppreciationFilterTo: { $gte: x }

当前MongoDB版本 #

使用的MongoDB版本为v3.2.11：

/# mongo --version                                                                                               
MongoDB version v3.2.11

内容的提问来源于stack exchange，提问作者Valentin Borisenko