如何对比MongoDB中Rooms与Customers集合并标记房间预订状态？

需求说明 #

展示Rooms集合的所有内容，同时添加预订状态字段：当Rooms的_id与Customers的roomID匹配时标记为Booked，不匹配则标记为Not booked。

集合结构 #

Collection 1: Rooms #

[
  {
    _id: ObjectId("62db88affeb2d64c1b818d8b"),
    seats: 54,
    amenities: [ 'AC', 'Water' ],
    price: 5000
  },
  {
    _id: ObjectId("62db8927feb2d64c1b818d8c"),
    seats: 52,
    amenities: [ 'Water' ],
    price: 52000
  },
  {
    _id: ObjectId("62db893afeb2d64c1b818d8d"),
    seats: 520,
    amenities: [ 'AC', 'Water' ],
    price: 52000
  },
  {
    _id: ObjectId("62db894efeb2d64c1b818d8e"),
    seats: 529,
    amenities: [ 'AC', 'Water' ],
    price: 9000
  }
]

Collection 2: Customers #

[
  {
    _id: ObjectId("62db8c69feb2d64c1b818d91"),
    customerName: 'John',
    date: '20-04-2020',
    startTime: '7PM',
    endTime: '10PM',
    roomID: '62db88affeb2d64c1b818d8b'
  },
  {
    _id: ObjectId("62db8c92feb2d64c1b818d92"),
    customerName: 'Harry',
    date: '18-04-2020',
    startTime: '7PM',
    endTime: '10PM',
    roomID: '62db88affeb2d64c1b818d8e'
  }
]

解决方案 #

使用MongoDB聚合框架，通过$lookup关联两个集合，再用$addFields生成预订状态字段：

db.Rooms.aggregate([
  {
    $lookup: {
      from: "Customers",
      localField: "_id",
      foreignField: "roomID",
      as: "bookingDetails"
    }
  },
  {
    $addFields: {
      bookingStatus: {
        $cond: {
          if: { $gt: [ { $size: "$bookingDetails" }, 0 ] },
          then: "Booked",
          else: "Not booked"
        }
      }
    }
  }
])

结果说明 #

执行上述聚合后，每个Room文档会新增两个字段：

• bookingDetails：存储所有匹配的客户预订记录（无匹配时为空数组）
• bookingStatus：根据匹配结果显示Booked或Not booked

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