MongoDB聚合查询优化：让Shift Manager记录在设备组内优先返回

现有聚合查询可正常运行，但未满足需求：需要让reports集合中department字段为'Shift Manager'的记录，在每个设备组内优先返回。

现有聚合查询代码 #

db.collection('reports').aggregate([
{ $match: { date: { $gte: yesterday } } },
{
  $lookup: {
    from: 'equipment',
    localField: 'equipment',
    foreignField: 'equipment',
    as: 'equipmentInfo'
  }
},
{ $unwind: '$equipmentInfo' },
{ $sort: { 'equipmentInfo._id': 1 } },
{ $sort: { department: -1 } },
{
  $group: {
    _id: {
        equipmentId: '$equipmentInfo._id',
        name: '$name',
        shift: '$shift',
        date: '$date',
        department: '$department',
        equipment: '$equipmentInfo.equipment'
    },
    report: { $first: '$report' }
  }
},
{
    $sort: { '_id.equipmentId': 1 }
}
])

集合结构 #

equipment集合 #

[
  {
    _id: ObjectId("611c058a1556ba22f0ec0f6d"),
    equipment: "DCM 1"
  },
  {
    _id: ObjectId("611c058a1556ba22f0ec0f6e"),
    equipment: "DCM 2"
  },
]

reports集合 #

[{
  "_id": {
    "$oid": "6284e804a96c8bb76a648e68"
  },
  "date": 1514851200000,
  "department": "Maintenance",
  "equipment": "DCM 1",
  "name": "Simo",
  "report": "machine shop start up . All cnc machine warmed up .",
  "shift": "Days"
},{
  "_id": {
    "$oid": "6284e804a96c8bb76a648e69"
  },
  "date": 1514851200000,
  "department": "Shift Manager",
  "equipment": "DCM 7",
  "name": "Jim",
  "report": "bkw chiller motor had tripped . Reset all ok",
  "shift": "Days"
},{
  "_id": {
    "$oid": "6284e804a96c8bb76a648e6a"
  },
  "date": 1514851200000,
  "department": "Process",
  "equipment": "DCM 14",
  "name": "Jack",
  "report": "bkw chiller had tripped . Reset all ok #\nlow on coolant. not topped up",
  "shift": "Days"
}]

修改后的解决方案 #

核心是通过自定义优先级字段，确保同一设备组内'Shift Manager'记录排序靠前，再通过$first优先选取该记录。修改后的聚合查询如下：

db.collection('reports').aggregate([
{ $match: { date: { $gte: yesterday } } },
{
  $lookup: {
    from: 'equipment',
    localField: 'equipment',
    foreignField: 'equipment',
    as: 'equipmentInfo'
  }
},
{ $unwind: '$equipmentInfo' },
// 新增步骤：添加优先级字段，Shift Manager标记为1，其他为0
{
  $addFields: {
    isShiftManager: {
      $cond: { if: { $eq: ['$department', 'Shift Manager'] }, then: 1, else: 0 }
    }
  }
},
// 调整排序：先按设备组分组，再按优先级降序，确保Shift Manager在组内排最前
{ $sort: { 'equipmentInfo._id': 1, isShiftManager: -1 } },
{
  $group: {
    _id: {
        equipmentId: '$equipmentInfo._id',
        name: '$name',
        shift: '$shift',
        date: '$date',
        department: '$department',
        equipment: '$equipmentInfo.equipment'
    },
    report: { $first: '$report' }
  }
},
{
    $sort: { '_id.equipmentId': 1 }
}
])

关键修改说明 #

1. 添加优先级字段：用$addFields新增isShiftManager字段，通过$cond判断当前记录是否为Shift Manager，是则赋值1，否则0。
2. 调整排序逻辑：将排序改为先按equipmentInfo._id（设备组）升序，再按isShiftManager降序。这样同一设备组内，Shift Manager的记录会排在最前面。
3. 保留原有分组逻辑：$group阶段依旧用$first取第一条记录，此时同一设备组内优先取到的就是Shift Manager的记录。

这种方式比直接按department字符串排序更可靠，避免因字符串字典序导致的排序不符合预期问题。

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