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
  }
]

Collection2 : 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'
  }
]

I need to compare these two collections and display the rooms collections with the booking details(Booked - If the rooms collections _id is matched with customer collections roomID Not booked - If the rooms collections _id is not matched with customer collections roomID )

CodePudding user response：

You can use $lookup , but you need to apply some fixes in advance before you do that , you best to convert roomID to ObjectId in your Customers collection with:

   db.Customers.find().forEach( function(obj) {
    obj.roomID= new ObjectId(obj.roomID);
    db.Cusotmers.save(obj);
   }); 

After the fix you can do something like:

db.rooms.aggregate([
 {
"$lookup": {
  "from": "customers",
  "localField": "_id",
  "foreignField": "roomID",
  "as": "customer"
  }
 },
 {
  $addFields: {
  reservation: {
    $cond: [
      {
        $eq: [
          "$customer",
          []
        ]
      },
      "Not booked",
      "Booked"
     ]
    }
   }
  }
 ])

Explained:

1. Lookup room collection against customers collection based on roomID
2. Add one more variable showing if the room is reserved or not

Playground

P.S. In general following the booking logic since most of the time there is expected searches for empty rooms it is best to add the reservation field in your rooms collection so when it is reserved to avoid doing lookup to understand it ( lookup is resource intensive operation that you must avoid in distributed databases at every cost )