MongoDB按auctioncode分组筛选无RUNNING/SCHEDULED的FAILED状态记录

现有MongoDB存储的拍卖记录，auctioncode为拍卖活动唯一标识，同一auctioncode下可存在多条status不同的记录。

查询需求 #

筛选同时满足以下条件的记录：

• 记录本身status为FAILED
• 该记录对应的auctioncode分组下，不存在其他status为RUNNING或SCHEDULED的记录

示例数据集 #

[
    { 
    gemstone:"Champagne Glass",
    auctioncode:"RA0008343433",
    stockcode:"3273373",
    productname:"Champagne Beads Earrings and Faux Leather Lariat Necklace (24 in) in I...",
    quantity:1,
    createddate:2021-04-08T06:08:08.000+00:00,
    estimatedretailvalue:59.99,
    material:"Mix Metal",
    targetsellingprice:9.99,
    approved:true,
    startprice:1,
    incrementAmount:1,
    status:"FAILED",
    enddate:2021-08-13T14:10:15.000+00:00,
    startdate:2021-07-02T15:18:15.000+00:00,
    paid:false 
    },
    { 
    gemstone:"Champagne Glass",
    auctioncode:"RA0008343433",
    stockcode:"3273373",
    productname:"Champagne Beads Earrings and Faux Leather Lariat Necklace (24 in) in I...",
    quantity:1,
    createddate:2021-04-08T06:08:08.000+00:00,
    estimatedretailvalue:59.99,
    material:"Mix Metal",
    targetsellingprice:9.99,
    approved:true,
    startprice:1,
    incrementAmount:1,
    status:"RUNNING",
    enddate:2021-08-13T14:10:15.000+00:00,
    startdate:2021-07-02T15:18:15.000+00:00,
    paid:false 
    },
    { 
    gemstone:"Champagne Glass",
    auctioncode:"RA0008343433",
    stockcode:"3273373",
    productname:"Champagne Beads Earrings and Faux Leather Lariat Necklace (24 in) in I...",
    quantity:1,
    createddate:2021-04-08T06:08:08.000+00:00,
    estimatedretailvalue:59.99,
    material:"Mix Metal",
    targetsellingprice:9.99,
    approved:true,
    startprice:1,
    incrementAmount:1,
    status:"SCHEDULED",
    enddate:2021-08-13T14:10:15.000+00:00,
    startdate:2021-07-02T15:18:15.000+00:00,
    paid:false 
    }
  ]

期望返回结果 #

[
        { 
        gemstone:"Champagne Glass",
        auctioncode:"RA0008343433",
        stockcode:"3273373",
        productname:"Champagne Beads Earrings and Faux Leather Lariat Necklace (24 in) in I...",
        quantity:1,
        createddate:2021-04-08T06:08:08.000+00:00,
        estimatedretailvalue:59.99,
        material:"Mix Metal",
        targetsellingprice:9.99,
        approved:true,
        startprice:1,
        incrementAmount:1,
        status:"FAILED",
        enddate:2021-08-13T14:10:15.000+00:00,
        startdate:2021-07-02T15:18:15.000+00:00,
        paid:false 
        }
]

原查询问题说明 #

你提供的原有查询存在两个核心错误：

1. $lookup阶段关联逻辑错误，将_id作为关联键匹配auctioncode，完全不符合同拍卖码关联的需求
2. 仅过滤了当前记录的状态为FAILED，没有判断对应auctioncode分组下是否存在其他RUNNING/SCHEDULED状态的记录

以下聚合查询可直接满足需求，同时兼容你原有分页逻辑：

[
  // 第一步：按auctioncode分组，统计分组内状态
  {
    '$group': {
      '_id': '$auctioncode',
      // 存储分组下所有原始记录
      'records': { '$push': '$$ROOT' },
      // 标记分组内是否存在RUNNING或SCHEDULED状态的记录
      'hasActiveRecord': {
        '$max': {
          '$cond': [
            { '$in': [ '$status', ['RUNNING', 'SCHEDULED'] ] },
            true,
            false
          ]
        }
      }
    }
  },
  // 第二步：筛选出没有活跃记录的分组
  {
    '$match': { 'hasActiveRecord': false }
  },
  // 第三步：拆分分组内的单条记录
  { '$unwind': '$records' },
  // 第四步：仅保留状态为FAILED的记录
  {
    '$match': { 'records.status': 'FAILED' }
  },
  // 第五步：还原原始记录结构，去掉分组冗余字段
  {
    '$replaceRoot': { 'newRoot': '$records' }
  },
  // 保留你原有的分页逻辑
  {
    '$facet': {
      'products': [
        { '$skip': 0 },
        { '$limit': 150 }
      ]
    }
  }
]

逻辑说明 #

• 先按拍卖码分组统计状态，从根源上判断同一拍卖活动下是否存在活跃记录，避免了低效的关联查询
• 筛选掉存在活跃记录的分组后，仅保留分组内的FAILED状态记录即可完全匹配需求

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