MongoDB中实现类似MySQL NOT IN子查询的分组日期过滤查询

需求核心：筛选日期≤指定参数fechaParam的记录，但只要某分组（按group字段）内存在任意一条日期>参数的记录，该分组所有符合日期条件的记录都需排除。

实现思路（对应MySQL NOT IN子查询逻辑） #

和MySQL的思路一致，先找出存在日期超标的分组，再在主查询中排除这些分组，同时保留日期符合条件的记录。

步骤1：查询需排除的分组

通过聚合分组计算每个分组的最大日期，筛选出最大日期>参数的分组：

// 替换为你的参数日期
const fechaParam = ISODate("2024-07-18T07:00:00.000Z");

const excludedGroups = db.yourCollectionName.aggregate([
  {
    $group: {
      _id: "$group",
      maxDate: { $max: "$date" }
    }
  },
  {
    $match: {
      maxDate: { $gt: fechaParam }
    }
  },
  {
    $project: {
      _id: 0,
      group: "$_id"
    }
  }
]).toArray().map(item => item.group);

步骤2：筛选符合条件的记录

查询日期≤参数，且分组不在排除列表中的记录：

db.yourCollectionName.find({
  date: { $lte: fechaParam },
  group: { $nin: excludedGroups }
});

单聚合管道一步实现 #

如果不想分两次查询，可通过聚合管道一次性完成所有逻辑：

const fechaParam = ISODate("2024-07-18T07:00:00.000Z");

db.yourCollectionName.aggregate([
  // 分组计算每个分组的最大日期，同时保留组内所有原始文档
  {
    $group: {
      _id: "$group",
      maxDate: { $max: "$date" },
      docs: { $push: "$$ROOT" }
    }
  },
  // 仅保留最大日期≤参数的合法分组
  {
    $match: {
      maxDate: { $lte: fechaParam }
    }
  },
  // 展开分组内的文档
  {
    $unwind: "$docs"
  },
  // 筛选文档自身日期≤参数的记录（可选，因合法分组的maxDate已≤参数，此步骤可省略）
  {
    $match: {
      "docs.date": { $lte: fechaParam }
    }
  },
  // 恢复原始文档结构
  {
    $replaceRoot: { newRoot: "$docs" }
  }
]);

示例验证 #

以你提供的集合数据为例：

• 若fechaParam设为ISODate("2024-07-18T07:00:00.000Z")：
  • group1的最大日期是2024-07-18T08:43:21.000Z（>参数），group2的最大日期是2024-07-18T09:43:21.000Z（>参数），因此无符合条件的记录。
• 若fechaParam设为ISODate("2024-07-18T10:00:00.000Z")：
  • 两个分组的最大日期均≤参数，因此所有日期≤参数的记录（即全部5条）都会被返回。

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