如何使用MongoDB聚合查询按过滤状态获取visible_id连续区间？

需求说明 #

根据指定的status过滤文档，提取该状态下visible_id的连续数值区间；若中间出现其他状态的文档，则从下一个符合条件的文档开始生成新的区间。

示例数据 #

[
  {
    "visible_id": 1,
    "status": "INITIAL",
    "name": "ab"
  },
  {
    "visible_id": 2,
    "status": "INITIAL",
    "name": "aab"
  },
  {
    "visible_id": 3,
    "status": "SCANNED",
    "name": "bba"
  },
  {
    "visible_id": 4,
    "status": "SCANNED",
    "name": "cca"
  },
  {
    "visible_id": 5,
    "status": "INITIAL",
    "name": "dds"
  },
  {
    "visible_id": 6,
    "status": "INITIAL",
    "name": "aax"
  },
  {
    "visible_id": 7,
    "status": "INITIAL",
    "name": "bsd"
  },
  {
    "visible_id": 8,
    "status": "PRINTED",
    "name": "ads"
  }
]

示例结果 #

• 过滤状态为"INITIAL"时：

[
    {"status": "INITIAL", "range": "1-2"},
    {"status": "INITIAL", "range": "5-7"}
]

• 过滤状态为"SCANNED"时：

[
    {"status": "SCANNED", "range": "3-4"}
]

• 过滤状态为"PRINTED"时：

[
    {"status": "PRINTED", "range": "8-8"}
]

聚合查询方案 #

以下是通用的聚合管道，只需替换$match阶段的status值即可适配不同过滤需求：

db.collection.aggregate([
  // 过滤目标状态的文档
  {
    $match: { status: "INITIAL" } // 替换为需要的状态值
  },
  // 按visible_id升序排序，保证区间判断的正确性
  {
    $sort: { visible_id: 1 }
  },
  // 计算分组标识，区分连续区间
  {
    $setWindowFields: {
      sortBy: { visible_id: 1 },
      output: {
        groupId: {
          $sum: {
            $cond: [
              { $eq: [{ $subtract: ["$visible_id", { $lag: "$visible_id" }] }, 1] },
              0,
              1
            ]
          },
          window: { documents: ["unbounded", "current"] }
        }
      }
    }
  },
  // 按分组标识和状态分组，统计区间的最小、最大id
  {
    $group: {
      _id: { groupId: "$groupId", status: "$status" },
      minId: { $min: "$visible_id" },
      maxId: { $max: "$visible_id" }
    }
  },
  // 生成range字符串，整理输出格式
  {
    $project: {
      _id: 0,
      status: "$_id.status",
      range: {
        $cond: [
          { $eq: ["$minId", "$maxId"] },
          { $toString: "$minId" },
          { $concat: [{ $toString: "$minId" }, "-", { $toString: "$maxId" }] }
        ]
      }
    }
  },
  // 可选：按区间起始id排序结果
  {
    $sort: { range: 1 }
  }
])

各阶段解释 #

1. $match：精准过滤目标状态文档，减少后续计算量。
2. $sort：必须按visible_id升序排序，确保窗口函数能正确判断连续关系。
3. $setWindowFields：用$lag获取前一个文档的visible_id，计算当前id与前一个的差值；差值为1则属于同一区间（groupId不变），否则开启新区间（groupId加1）。
4. $group：以groupId和status为分组键，统计每个区间的最小、最大visible_id。
5. $project：将最小、最大id拼接成range字符串，若两者相等则直接输出单个id；同时整理输出字段，去掉冗余的_id。
6. $sort：可选步骤，让结果按区间起始id升序排列，更符合阅读习惯。

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