mongodb aggregate实现一对多、一对一的查询

一对多

先创建一张表countryCode

db.countryCode.insert([{code: 1}, {code: 20}, {code: 30}])

[图片上传失败...(image-dae595-1512822343904)]

第二张表countryCodeLookup，这张表显示country的code和country的name 的对应关系是一对多

db.countryCodeLookup.insert([{code: 1, name: "United States"}, {code: 20, name: "Egypt"},
{code: 1, name: "Foobar"}, {code: 30, name: "Greece"}])

[图片上传失败...(image-fcefd8-1512822343904)]

$lookup、$project

下面通过 $lookup 操作符连接两个集合，其中 $project 会过滤属性

db.countryCode.aggregate([
{ $lookup: {from: "countryCodeLookup", localField: "code", foreignField: "code", as: "countryName"} },
{ $project: {"code":1, "countryName.name":1, "_id":0} }
])

[图片上传失败...(image-3ce4d3-1512822343904)]

$match

$match 会查询连接后的表，显示符合条件的。

db.countryCode.aggregate([
{ $lookup: {from: "countryCodeLookup", localField: "code", foreignField: "code", as: "countryName"} },
{ $project: {"code":1, "countryName.name":1, "_id":0} },
{ $match:{"code":1.0}}
])

[图片上传失败...(image-7499e7-1512822343904)]

一对一

$unwind

$unwind操作符会分解$lookup中的as数组，使之扁平化返回。

db.countryCode.aggregate([
{ $lookup: {from: "countryCodeLookup", localField: "code", foreignField: "code", as: "countryName"} },
{ $project: {"code":1, "countryName.name":1, "_id":0} },
{ $unwind: "$countryName"},
{ $match:{"code":1.0}}
])

[图片上传失败...(image-fcc105-1512822343904)]

现在countryName是一个子文档，在project中设置把子文档中的属性拿出来，这里$project要在$unwind属性后面。

db.countryCode.aggregate([
{ $lookup: {from: "countryCodeLookup", localField: "code", foreignField: "code", as: "countryName"} },
{ $unwind: "$countryName"},
{ $project: {"code":1, "name": "$countryName.name", "_id":0} }
])

[图片上传失败...(image-185c94-1512822343905)]

参考文档