在上一篇mongodb Aggregation聚合操作之$out中详细介绍了mongodb聚合操作中的$out使用以及参数细节。本篇将开始介绍Aggregation聚合操作中的$lookup操作。
说明：
mongodb多表连接的操作，对同一数据库中的未分片集合执行左外连接，从“已联接”集合中筛选文档以进行处理。对于每个输入文档，$lookup阶段添加一个新的数组字段，该字段的元素是来自“已加入”集合的匹配文档。
1. 精确匹配连接
语法：
1.精确匹配：
{
$lookup:
{
from: <collection to join>,
localField: <field from the input documents>,
foreignField: <field from the documents of the "from" collection>,
as: <output array field>
}
}
参数讲解：
from：指定要与之执行连接的同一数据库中的集合。无法分片从集合。有关详细信息，请参见分片集合限制。
localField：指定从文档输入到$lookup阶段的字段。$lookup对from集合的文档中的localField与foreignField执行相等匹配。如果输入文档不包含localField，则$lookup将该字段视为具有null值，以便进行匹配。
foreignField：指定from集合中的文档中的字段。$lookup在输入文档中的foreignField与localField执行相等匹配。如果from集合中的文档不包含foreignField， $lookup将该值作为null进行匹配
as：指定要添加到输入文档的新数组字段的名称。新的数组字段包含来自from集合的匹配文档。如果指定的名称已经存在于输入文档中，则覆盖现有字段。
该操作将对应于以下伪sql语句：
SELECT *, <output array field>
FROM collection
WHERE <output array field> IN (SELECT *
FROM <collection to join>
WHERE <foreignField>= <collection.localField>);
1.1. 示例
1.1.1. 使用$lookup执行一个等式联接
初始化数据：
db.orders.insert([
{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
{ "_id" : 3 }
])
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 }
])
示例：
db.orders.aggregate([
{
$lookup:
{
from: "inventory",
localField: "item",
foreignField: "sku",
as: "inventory_docs"
}
}
])
结果：
{
"_id" : 1.0,
"item" : "almonds",
"price" : 12.0,
"quantity" : 2.0,
"inventory_docs" : [
{
"_id" : 1.0,
"sku" : "almonds",
"description" : "product 1",
"instock" : 120.0
}
]
}
{
"_id" : 2.0,
"item" : "pecans",
"price" : 20.0,
"quantity" : 1.0,
"inventory_docs" : [
{
"_id" : 4.0,
"sku" : "pecans",
"description" : "product 4",
"instock" : 70.0
}
]
}
{
"_id" : 3.0,
"inventory_docs" : [
{
"_id" : 5.0,
"sku" : null,
"description" : "Incomplete"
},
{
"_id" : 6.0
}
]
}
1.1.2. 对数组使用$lookup
初始化数据：
db.classes.insert( [
{ _id: 1, title: "Reading is ...", enrollmentlist: [ "giraffe2", "pandabear", "artie" ], days: ["M", "W", "F"] },
{ _id: 2, title: "But Writing ...", enrollmentlist: [ "giraffe1", "artie" ], days: ["T", "F"] }
])
db.members.insert( [
{ _id: 1, name: "artie", joined: new Date("2016-05-01"), status: "A" },
{ _id: 2, name: "giraffe", joined: new Date("2017-05-01"), status: "D" },
{ _id: 3, name: "giraffe1", joined: new Date("2017-10-01"), status: "A" },
{ _id: 4, name: "panda", joined: new Date("2018-10-11"), status: "A" },
{ _id: 5, name: "pandabear", joined: new Date("2018-12-01"), status: "A" },
{ _id: 6, name: "giraffe2", joined: new Date("2018-12-01"), status: "D" }
])
示例:
db.classes.aggregate([
{
$lookup:
{
from: "members",
localField: "enrollmentlist",
foreignField: "name",
as: "enrollee_info"
}
}
])
结果：
{
"_id" : 1.0,
"title" : "Reading is ...",
"enrollmentlist" : [
"giraffe2",
"pandabear",
"artie"
],
"days" : [
"M",
"W",
"F"
],
"enrollee_info" : [
{
"_id" : 1.0,
"name" : "artie",
"joined" : ISODate("2016-05-01T00:00:00.000Z"),
"status" : "A"
},
{
"_id" : 5.0,
"name" : "pandabear",
"joined" : ISODate("2018-12-01T00:00:00.000Z"),
"status" : "A"
},
{
"_id" : 6.0,
"name" : "giraffe2",
"joined" : ISODate("2018-12-01T00:00:00.000Z"),
"status" : "D"
}
]
}
{
"_id" : 2.0,
"title" : "But Writing ...",
"enrollmentlist" : [
"giraffe1",
"artie"
],
"days" : [
"T",
"F"
],
"enrollee_info" : [
{
"_id" : 1.0,
"name" : "artie",
"joined" : ISODate("2016-05-01T00:00:00.000Z"),
"status" : "A"
},
{
"_id" : 3.0,
"name" : "giraffe1",
"joined" : ISODate("2017-10-01T00:00:00.000Z"),
"status" : "A"
}
]
}
1.1.3. 使用$lookup 和 $mergeObjects
初始化数据：
db.orders.insert([
{ "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
{ "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 }
])
db.items.insert([
{ "_id" : 1, "item" : "almonds", description: "almond clusters", "instock" : 120 },
{ "_id" : 2, "item" : "bread", description: "raisin and nut bread", "instock" : 80 },
{ "_id" : 3, "item" : "pecans", description: "candied pecans", "instock" : 60 }
])
示例：
下面的操作首先使用$lookup阶段按下面的操作首先使用$lookup阶段按item字段连接两个集合，然后使用$replaceRoot中的$mergeObjects从项和订单中合并已连接的文档:字段连接两个集合，然后使用$replaceRoot中的$mergeObjects从项和订单中合并已连接的文档:
db.orders.aggregate([
{
$lookup: {
from: "items",
localField: "item", // field in the orders collection
foreignField: "item", // field in the items collection
as: "fromItems"
}
},
{
$replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$fromItems", 0 ] }, "$$ROOT" ] } }
},
{ $project: { fromItems: 0 } }
])
结果：
{
"_id" : 1.0,
"item" : "almonds",
"description" : "almond clusters",
"instock" : 120.0,
"price" : 12.0,
"quantity" : 2.0
}
{
"_id" : 2.0,
"item" : "pecans",
"description" : "candied pecans",
"instock" : 60.0,
"price" : 20.0,
"quantity" : 1.0
}
