MongoDB单聚合查询近5年每月可见Shop数量需求

我有如下Shop集合的Schema定义：

var schema = new Schema({
    name: {
        type: String,
    },
    // 其他字段...
    created_at: {
        type: Date,
    },
    lost_at: {
        type: Date,
    },
});
mongoose.model("Shop", schema);

定义Shop仅在created_at至lost_at的时间段内为“可见”，需要生成如下格式的统计结果：

[{ _id: null, year: '2023', month: '2', count: 0 },
{ _id: null, year: '2023', month: '3', count: 3},
{ _id: null, year: '2023', month: '4', count: 8},
{ _id: null, year: '2023', month: '5', count: 16}
...]

要求：

• 年份和月份需覆盖近5年的所有年月，即使对应年月无相关记录也要保留，count填0
• count为对应月份的可见Shop数量
• 之前通过Node.js任务用两次聚合实现，但现在需要单聚合查询方案用于MongoDB Charts展示

以下是兼容MongoDB 5.0+的单聚合查询代码，会自动生成近5年的年月序列并统计对应月份的可见店铺数：

db.Shop.aggregate([
  // 阶段1：生成近5年的所有年月序列
  {
    $documents: (function() {
      const result = [];
      const now = new Date();
      // 从5年前的当月开始，生成连续60个月的年月数据
      for (let i = 0; i < 60; i++) {
        const date = new Date(now.getFullYear() - 5, now.getMonth(), 1);
        date.setMonth(date.getMonth() + i);
        result.push({
          year: date.getFullYear().toString(),
          month: (date.getMonth() + 1).toString()
        });
      }
      return result;
    })()
  },
  // 阶段2：左连接Shop集合，匹配当月可见的店铺
  {
    $lookup: {
      from: "Shop",
      let: {
        targetYear: { $toInt: "$year" },
        targetMonth: { $toInt: "$month" }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                // 店铺创建时间不晚于当月第一天（确保当月已存在）
                { $lte: [ "$created_at", { $dateFromParts: { year: "$$targetYear", month: "$$targetMonth", day: 1 } } ] },
                // 店铺未失效（lost_at为空）或失效时间不早于当月第一天
                { $or: [
                  { $gte: [ "$lost_at", { $dateFromParts: { year: "$$targetYear", month: "$$targetMonth", day: 1 } } ] },
                  { $eq: [ "$lost_at", null ] }
                ] }
              ]
            }
          }
        },
        { $count: "matched" }
      ],
      as: "shopCounts"
    }
  },
  // 阶段3：格式化结果，无匹配则count设为0
  {
    $project: {
      _id: null,
      year: "$year",
      month: "$month",
      count: {
        $ifNull: [ { $arrayElemAt: [ "$shopCounts.matched", 0 ] }, 0 ]
      }
    }
  },
  // 阶段4：按年月升序排序
  {
    $sort: {
      year: 1,
      month: 1
    }
  }
])

低版本MongoDB兼容方案（替换阶段1） #

如果你的MongoDB版本低于5.0，不支持$documents，可以用以下代码替换阶段1，通过$range生成月份偏移量来生成年月序列：

// 替换原阶段1的代码
{
  $addFields: {
    monthOffsets: { $range: [0, 60] }
  }
},
{
  $unwind: "$monthOffsets"
},
{
  $project: {
    year: {
      $toString: {
        $year: {
          $dateAdd: {
            startDate: { $dateFromParts: { year: { $subtract: [ { $year: new Date() }, 5 ] }, month: { $month: new Date() }, day: 1 } },
            unit: "month",
            amount: "$monthOffsets"
          }
        }
      }
    },
    month: {
      $toString: {
        $month: {
          $dateAdd: {
            startDate: { $dateFromParts: { year: { $subtract: [ { $year: new Date() }, 5 ] }, month: { $month: new Date() }, day: 1 } },
            unit: "month",
            amount: "$monthOffsets"
          }
        }
      }
    }
  }
}

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