mongoDB 3.2 版本后增加聯(lián)表功能($lookup)
mongodb原生語法
{
$lookup:
{
from: <collection to join>,
localField: <field from the input documents>,
foreignField: <field from the documents of the "from" collection>,
as: <output array field>
}
}
| 語法值 | 解釋說明 |
|---|---|
| form | 同一個數(shù)據(jù)庫下需要連接的目標(biāo)集合 |
| localField | 原集合中的Key值，若某條文檔沒有Key瓢宦，處理過程中停士，會默認(rèn)此文檔存在Key：null的鍵值對 |
| foreignField | 目標(biāo)集合中的 Key值姚垃，若某條文檔沒有Key丛肢，處理過程中撩满，會默認(rèn)此文檔存在Key：null的鍵值對 |
| as | 為新輸入文檔命名习蓬。如果輸入的集合中已存在該值旺隙，則會覆蓋掉 |
SELECT *, <output array field>
FROM collection
WHERE <output array field> IN (
SELECT *
FROM <collection to join>
WHERE <foreignField> = <collection.localField>);
)
Examples
創(chuàng)建 orders 集合，數(shù)據(jù)如下：
db.orders.insert([
{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
{ "_id" : 3 }
])
創(chuàng)建 inventory 集合馅笙，數(shù)據(jù)如下：
db.inventory.insert([
{ "_id" : 1, "sku" : "almonds", description: "product 1", "instock" : 120 },
{ "_id" : 2, "sku" : "bread", description: "product 2", "instock" : 80 },
{ "_id" : 3, "sku" : "cashews", description: "product 3", "instock" : 60 },
{ "_id" : 4, "sku" : "pecans", description: "product 4", "instock" : 70 },
{ "_id" : 5, "sku": null, description: "Incomplete" },
{ "_id" : 6 }
])
現(xiàn)將 orders(訂單表)和inventory(庫存表) 連接起來：
db.orders.aggregate([
{
$lookup:
{
from: "inventory",
localField: "item",
foreignField: "sku",
as: "inventory_docs"
}
}
])
執(zhí)行結(jié)果：
{
"_id" : 1,
"item" : "almonds",
"price" : 12,
"quantity" : 2,
"inventory_docs" : [
{ "_id" : 1, "sku" : "almonds", "description" : "product 1", "instock" : 120 }
]
}
{
"_id" : 2,
"item" : "pecans",
"price" : 20,
"quantity" : 1,
"inventory_docs" : [
{ "_id" : 4, "sku" : "pecans", "description" : "product 4", "instock" : 70 }
]
}
{
"_id" : 3,
"inventory_docs" : [
{ "_id" : 5, "sku" : null, "description" : "Incomplete" },
{ "_id" : 6 }
]
}
偽SQL如下：
SELECT *, inventory_docs
FROM orders
WHERE inventory_docs IN (SELECT *
FROM inventory
WHERE sku= orders.item);
