Mapping a field to another ObjectId of different collection in MongoDB-CodePudding

I am just wondering if its possible to use a $lookup aggregation operator inside of the in field of a $map operator. I am trying to map the VINs inside the carData objects to corresponding document ids in a VIN collection. Is there a way to accomplish this using $lookup inside of $map where I can match the vin field inside my carData objects with vin fields inside of my VIN collection to map to an id as opposed to the vin.

CarData Collection:

carData: [
 {vin: 123456789,
  make: "Dodge"},
 {vin: 987654321,
  make: "Honda"}
]


Vin Collection:
[
{
   _id: ObjectId("1dsf1234125")
   Vin: 123456789
},

{
   _id: ObjectId("1dsf1234124")
   Vin: 987654321
},

]

Expected result

carData: [
 {vin: ObjectId("1dsf1234125"),
  make: "Dodge"},
 {vin: ObjectId("1dsf1234124"),
  make: "Honda"}
]

CodePudding user response：

Method 1

db.carDatas.aggregate([
  {
    $lookup: {
      from: "vins",
      localField: "carData.vin",
      foreignField: "_id",
      as: "inventory_docs"
    }
  }
])

mongoplayground

Method 2

db.carDatas.aggregate([
  {
    $unwind: "$carData"
  },
  {
    $lookup: {
      from: "vins",
      localField: "carData.vin",
      foreignField: "_id",
      as: "inventory_docs"
    }
  }
])

mongoplayground

Method 3

db.carDatas.aggregate([
  {
    $unwind: "$carData"
  },
  {
    $lookup: {
      from: "vins",
      localField: "carData.vin",
      foreignField: "_id",
      as: "docs"
    }
  },
  {
    $set: {
      newField: {
        $mergeObjects: [
          "$carData",
          {
            $first: "$docs"
          }
        ]
      }
    }
  }
])

mongoplayground

CodePudding user response：

Using the data samples from the question post, this aggregation returns the expected result (change the collection names appropriately):

db.cars_collection.aggregate([
   {
      $lookup:
         {
            from: "vins_collection",
            localField: "vin",
            foreignField: "Vin",
            as: "car_vins"
        }
   },
   { 
      $project: {
          _id: 0,
          make: 1,
          vin: { 
              $arrayElemAt: [ 
                  { $map: { input: "$car_vins", in: "$$this._id" } }, 0 
              ] 
          }
      }
  },
])

The result will look like this:

{ "make" : "Dodge", "vin" : ObjectId("626a12a1ac1cc4f9d0bbd7e7") }
{ "make" : "Honda", "vin" : ObjectId("626a12a1ac1cc4f9d0bbd7e8") }