How to use variables with MongoDB $ lookup
Let's say I have 3 collections, cars
, bikes
, vehicles
.
cars
as:
{
{
"_id": "car1",
"carBrand": "Audi",
"color": "blue"
},
{
"_id": "car2",
"carBrand": "BMW",
"color": "white"
}
}
bikes
as:
{
{
"_id": "bike1",
"bikeBrand": "Audi",
"color": "red"
},
{
"_id": "bike2",
"carBrand": "BMW",
"color": "white"
}
}
and the collection vehicles
actually has references to collections cars
and bikes
how
{
{
"_id": "vehicle1",
"vehicleType": "cars",
"vehicleId": "car1"
},
{
"_id": "vehicle2",
"vehicleType": "cars",
"vehicleId": "car2"
},
{
"_id": "vehicle3",
"vehicleType": "bikes",
"vehicleId": "bike1"
},
{
"_id": "vehicle4",
"vehicleType": "bikes",
"vehicleId": "bike2"
},
}
I would like to join a collection vehicles
with collections cars
and bikes
. I tried to set "$vehicleType"
as a variable in a field $lookup
from
. However, it doesn't work as I expected. It just doesn't join tables. No mistakes.
db.collection.aggregate([{
$lookup: {
from: "$vehicleType",
localField: "vehicleId",
foreignField: "_id",
as: "vehicleDetails"
}
}]);
I was expecting to get something like this
{
{
"_id": "vehicle1",
"vehicleType": "cars",
"vehicleId": "car1",
"vehicleDetails": {
"_id": "car1",
"carBrand": "Audi",
"color": ""
}
},
{
"_id": "vehicle2",
"vehicleType": "cars",
"vehicleId": "car2",
"vehicleDetails": {
"_id": "car2",
"carBrand": "BMW",
"color": "white"
},
{
"_id": "vehicle3",
"vehicleType": "bikes",
"vehicleId": "bike1",
"vehicleDetails": {
"_id": "bike1",
"bikeBrand": "Audi",
"color": "red"
}
},
{
"_id": "vehicle4",
"vehicleType": "bikes",
"vehicleId": "bike2",
"vehicleDetails": {
"_id": "bike2",
"carBrand": "BMW",
"color": "white"
}
},
}
source to share
If cars and bicycles don't have common identifiers, you can search sequentially in separate arrays and then concatenate them with $ setUnion :
db.vehicles.aggregate([
{$lookup: {
from: "cars",
localField: "vehicleId",
foreignField: "_id",
as: "carDetails"
}},
{$lookup: {
from: "bikes",
localField: "vehicleId",
foreignField: "_id",
as: "bikeDetails"
}},
{$project: {
vehicleType: 1,
vehicleId: 1,
vehicleDetails:{$setUnion: [ "$carDetails", "$bikeDetails" ]}
}},
{$project: {
carDetails:0,
bikeDetails:0,
}}
]);
Otherwise, you will need to use $ facet to filter vehicles by type before searching:
db.vehicles.aggregate([
{
$facet: {
"cars": [
{$match: {"vehicleType": "cars"}},
{$lookup: {
from: "cars",
localField: "vehicleId",
foreignField: "_id",
as: "vehicleDetails"
}},
],
"bikes": [
{$match: {"vehicleType": "bikes"}},
{$lookup: {
from: "bikes",
localField: "vehicleId",
foreignField: "_id",
as: "vehicleDetails"
}}
]
}
},
{$project: {all: {$setUnion: ["$cars", "$bikes"]}}},
{$unwind: "$all"},
{$replaceRoot: { newRoot: "$all" }}
])
source to share