我在 MongoDB 4.0 (pymongo) 中有三個集合
users: [
{name: "John", house_id: 1, car_id: 1},
{name: "Charles", house_id: 1},
{name: "Jessy", house_id: 2, car_id: 2},
{name: "Tim", house_id: 3}
]
houses: [
{_id: 1, name: "house1"},
{_id: 1, name: "house2"},
{_id: 1, name: "house3"}
]
cars: [
{_id: 1, name: "car1"},
{_id: 2, name: "car2"}
]
在用戶表中，house_id是必需的，但car_id不需要。我需要在用戶、房屋和汽車之間進行連接，以獲取具有房屋資訊和汽車資訊的用戶串列（如果他們有car_id 的話）。
這是我的腳本
db.users.aggregate([{
"$lookup": {
"from": "houses",
"localField": "house_id",
"foreignField": "_id",
"as": "house"
}
},
{"$unwind": "$house"},
{
"$lookup": {
"from": "cars",
"localField": "car_id",
"foreignField": "_id",
"as": "car"
}
},
{"$unwind": "$car"}]);
但是這個腳本只回傳帶有 car_id 的用戶。如果我添加一個沒有 car_id 的帶有 un user._id的$match，我就沒有結果。
我知道car_id需要在這里得到結果，但就我而言，我需要得到所有結果。
沒有car_id的用戶的預期輸出：
[
{
"_id": ObjectId("xxxxxxxxxx"),
"name": "Charles"
"house_id": 1,
"houses": {
"_id": 1,
"name": "house1"
},
}
]
uj5u.com熱心網友回復：
我不確定您希望您的輸出是什么樣子，但您可以通過在 $lookups 之前添加匹配來實作這一點。
您可以在此處查看此查詢的實時演示
資料庫
考慮以下資料庫。
db={
users: [
{
name: "John",
house_id: 1,
car_id: 1
},
{
name: "Charles",
house_id: 1
},
{
name: "Jessy",
house_id: 2,
car_id: 2
},
{
name: "Tim",
house_id: 3
}
],
houses: [
{
_id: 1,
name: "house1"
},
{
_id: 2,
name: "house2"
},
{
_id: 3,
name: "house3"
}
],
cars: [
{
_id: 1,
name: "car1"
},
{
_id: 2,
name: "car2"
}
]
}
詢問
我們可以使用此查詢進行嵌套查找：
db.users.aggregate([
{
$match: {
name: "Charles"
}
},
{
"$lookup": {
"from": "houses",
"as": "houses",
"localField": "house_id",
"foreignField": "_id"
}
},
{
"$lookup": {
"from": "cars",
"as": "cars",
"localField": "car_id",
"foreignField": "_id"
}
},
{
$unwind: {
path: "$cars",
preserveNullAndEmptyArrays: true
}
},
{
$unwind: {
path: "$houses",
preserveNullAndEmptyArrays: true
}
}
])
結果
這給了我們：
[
{
"_id": ObjectId("5a934e000102030405000006"),
"house_id": 1,
"houses": {
"_id": 1,
"name": "house1"
},
"name": "Charles"
}
]
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/329205.html