十、 $lookup 表关联
db.order.aggregate([
{
$lookup:
{
from: "order_item",
localField: "order_id",
foreignField: "order_id",
as: "items"
}
}
]){
"_id": ObjectId("5b743d8c2c327f8d1b360540"),
"order_id": "1",
"uid": 10,
"trade_no": "111",
"all_price": 100,
"all_num": 2,
"items": [{
"_id": ObjectId("5b743d9c2c327f8d1b360543"),
"order_id": "1",
"title": "商品鼠标 1",
"price": 50,
"num": 1
}, {
"_id": ObjectId("5b743da12c327f8d1b360544"),
"order_id": "1",
"title": "商品键盘 2",
"price": 50,
"num": 1
}, {
"_id": ObjectId("5b74f457089f78dc8f0a4f3b"),
"order_id": "1",
"title": "商品键盘 3",
"price": 0,
"num": 1
}]
} {
"_id": ObjectId("5b743d902c327f8d1b360541"),
"order_id": "2",
"uid": 7,
"trade_no": "222",
"all_price": 90,
"all_num": 2,
"items": [{
"_id": ObjectId("5b743da52c327f8d1b360545"),
"order_id": "2",
"title": "牛奶",
"price": 50,
"num": 1
}, {
"_id": ObjectId("5b743da92c327f8d1b360546"),
"order_id": "2","title": "酸奶",
"price": 40,
"num": 1
}]
} {
"_id": ObjectId("5b743d962c327f8d1b360542"),
"order_id": "3",
"uid": 9,
"trade_no": "333",
"all_price": 20,
"all_num": 6,
"items": [{
"_id": ObjectId("5b743dad2c327f8d1b360547"),
"order_id": "3",
"title": "矿泉水",
"price": 2,
"num": 5
}, {
"_id": ObjectId("5b743dff2c327f8d1b360548"),
"order_id": "3",
"title": "毛巾",
"price": 10,
"num": 1
}]
}
db.order.aggregate([
{
$lookup:{
from: "order_item",
localField: "order_id",
foreignField: "order_id",
as: "items"
}
},
{
$match:{"all_price":{$gte:90}}
}
])
{
"_id": ObjectId("5b743d8c2c327f8d1b360540"),
"order_id": "1",
"uid": 10,
"trade_no": "111",
"all_price": 100,
"all_num": 2,
"items": [{
"_id": ObjectId("5b743d9c2c327f8d1b360543"),
"order_id": "1",
"title": "商品鼠标 1",
"price": 50,
"num": 1
}, {
"_id": ObjectId("5b743da12c327f8d1b360544"),
"order_id": "1",
"title": "商品键盘 2",
"price": 50,
"num": 1
}, {
"_id": ObjectId("5b74f457089f78dc8f0a4f3b"),
"order_id": "1",
"title": "商品键盘 3",
"price": 0,
"num": 1
}]} {
"_id": ObjectId("5b743d902c327f8d1b360541"),
"order_id": "2",
"uid": 7,
"trade_no": "222",
"all_price": 90,
"all_num": 2,
"items": [{
"_id": ObjectId("5b743da52c327f8d1b360545"),
"order_id": "2",
"title": "牛奶",
"price": 50,
"num": 1
}, {
"_id": ObjectId("5b743da92c327f8d1b360546"),
"order_id": "2",
"title": "酸奶",
"price": 40,
"num": 1
}]
}
db.order.aggregate([
{
$lookup:
{
from: "order_item",
localField: "order_id",
foreignField: "order_id",
as: "items"
}
},
{
$project:{ trade_no:1, all_price:1,items:1 }
},
{
$match:{"all_price":{$gte:90}}
},
{
$sort:{"all_price":-1}},
])
{
"_id": ObjectId("5b743d8c2c327f8d1b360540"),
"trade_no": "111",
"all_price": 100,
"items": [{
"_id": ObjectId("5b743d9c2c327f8d1b360543"),
"order_id": "1",
"title": "商品鼠标 1",
"price": 50,
"num": 1
}, {
"_id": ObjectId("5b743da12c327f8d1b360544"),
"order_id": "1",
"title": "商品键盘 2",
"price": 50,
"num": 1
}, {
"_id": ObjectId("5b74f457089f78dc8f0a4f3b"),
"order_id": "1",
"title": "商 品键盘 3",
"price": 0,
"num": 1
}]
} {
"_id": ObjectId("5b743d902c327f8d1b360541"),
"trade_no": "222",
"all_price": 90,
"items": [{
"_id": ObjectId("5b743da52c327f8d1b360545"),
"order_id": "2",
"title": "牛奶",
"price": 50,
"num": 1
}, {
"_id": ObjectId("5b743da92c327f8d1b360546"),
"order_id": "2",
"title": "酸奶",
"price": 40,
"num": 1}]
}