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"}
]
Method 1
db.carDatas.aggregate([
{
$unwind: "$carData"
},
{
$lookup: {
from: "Vins",
localField: "carData.vin",
foreignField: "Vin",
as: "docs"
}
},
{
$project: {
make: "$carData.make",
vin: { $first: "$docs._id" },
_id: 0
}
},
{
$group: {
_id: null,
carData: { $push: "$$ROOT" }
}
},
{
$unset: "_id"
}
])
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") }