php mongodb的lookup,mongodb Aggregation聚合操作之$lookup

在上一篇mongodb Aggregation聚合操作之$out中详细介绍了mongodb聚合操作中的$out使用以及参数细节。本篇将开始介绍Aggregation聚合操作中的$lookup操作。

说明：

mongodb多表连接的操作，对同一数据库中的未分片集合执行左外连接，从“已联接”集合中筛选文档以进行处理。对于每个输入文档，$lookup阶段添加一个新的数组字段，该字段的元素是来自“已加入”集合的匹配文档。

1. 精确匹配连接

语法：

1.精确匹配：

{

$lookup:

{

from: ,

localField: ,

foreignField: ,

as:

}

}

参数讲解：

from：指定要与之执行连接的同一数据库中的集合。无法分片从集合。有关详细信息，请参见分片集合限制。

localField：指定从文档输入到$lookup阶段的字段。$lookup对from集合的文档中的localField与foreignField执行相等匹配。如果输入文档不包含localField，则$lookup将该字段视为具有null值，以便进行匹配。

foreignField：指定from集合中的文档中的字段。$lookup在输入文档中的foreignField与localField执行相等匹配。如果from集合中的文档不包含foreignField， $lookup将该值作为null进行匹配

as：指定要添加到输入文档的新数组字段的名称。新的数组字段包含来自from集合的匹配文档。如果指定的名称已经存在于输入文档中，则覆盖现有字段。

该操作将对应于以下伪sql语句：

SELECT *,

FROM collection

WHERE IN (SELECT *

FROM

WHERE = );

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

}