Mongo: Comparing two fields on different collection and set values

I have an issue with inconsistent data and would like to clean it properly.

Let’s say I have two collections bookings and vehicles, I can ‘join’ with a vehicle_id property from the bookings collection.

db.bookings.insertMany([
   { "_id" : 1, "vehicle_id" : "1", "vehicle_energy_type" : 'electric', "battery_level":10 },
   { "_id" : 2, "vehicle_id" : "1", "vehicle_energy_type" : 'electric' }
   { "_id" : 3, "vehicle_id" : "1", "vehicle_energy_type" : 'diesel' },
])

db.vehicles.insertMany([
   { "_id" : 1, "energy_type" : 'electric' }
])

On the bookings collection I can quickly get some info on my vehicle, but unfortunately, I have two issues here :

• The first booking is correct
• The second booking has a correct vehicle_energy_type but no battery_level
• The third booking hasn’t the correct vehicle_energy_type nor the battery_level

How would you perform to set the correct vehicle_energy_type coming from the vehicles collection? And set the battery_level to 10 if the vehicle is ‘electric’?

I started to use an aggregation

db.bookings.aggregate([
  {
    $lookup: {
      from: "vehicles",
      localField: "vehicle_id",
      foreignField: "_id",
      as: "vehicles",
    },
  },
  {
    $unwind:
      {
        path: "$vehicles",
        preserveNullAndEmptyArrays: false,
      },
  },
  {
    $match: {
      $expr: {
        $eq: [
          "$vehicle_energy_type",
          "$vehicles.energy_type",
        ],
      },
    },
  },
])

Your help will be much appreciated

>Solution :

Just use the values from the $lookup to $set the field. Note that I did not use preserveNullAndEmptyArrays like you do just to avoid accidental lookup and update. You can add it back if that’s what you want. Finally, apply some cosmetics and $merge back to the bookings collection.

db.bookings.aggregate([
  {
    "$lookup": {
      "from": "vehicles",
      "localField": "vehicle_id",
      "foreignField": "_id",
      "as": "vehicles"
    }
  },
  {
    "$unwind": "$vehicles"
  },
  {
    $set: {
      vehicle_energy_type: "$vehicles.energy_type",
      battery_level: {
        "$cond": {
          "if": {
            $eq: [
              "$vehicles.energy_type",
              "electric"
            ]
          },
          "then": 10,
          "else": "$$REMOVE"
        }
      },
      vehicles: "$$REMOVE"
    }
  },
  {
    "$merge": {
      "into": "bookings",
      "on": "_id",
      "whenMatched": "merge"
    }
  }
])

Mongo Playground