MongoDB联表查询aggregate : $lookup

参考：MongoDB联表查询

建表插入数据：

db.createCollection("user")
db.user.insertMany([
    {
        _id: ObjectId("5af2b2c6b138c267e414c072"),
        uid: "uid000",
        name: "小红",
        age: 26
    },
    {
        _id: ObjectId("5af2b2c6b138c267e414c073"),
        uid: "uid001",
        name: "小芳",
        age: 27
    }
]);



db.createCollection("order")
db.order.insertMany([
    {
        _id: ObjectId("4af2b2c6b138c267e414c071"),
        uid: "uid000",
        product: "产品1",
        money: 100
    },
    {
        _id: ObjectId("4af2b2c6b138c267e414c072"),
        uid: "uid000",
        product: "产品2",
        money: 200
    },
    {
        _id: ObjectId("4af2b2c6b138c267e414c073"),
        uid: "uid001",
        product: "产品1",
        money: 100
    },
    {
        _id: ObjectId("4af2b2c6b138c267e414c074"),
        uid: "uid001",
        product: "产品2",
        money: 200
    }
]);

一、联表查询

db.user.aggregate([{
    $lookup: { 
      from: "order", 
      localField: "uid", 
      foreignField: "uid", 
      as: "orders"
    }
}]).pretty();

db.user.aggregate([{
$lookup: { // 左连接
  from: "order", // 关联到order表
  localField: "uid", // user 表关联的字段
  foreignField: "uid", // order 表关联的字段
  as: "orders"
}
}]);

结果

[{
	"_id" : ObjectId("5af2b2c6b138c267e414c072"),
	"uid" : "uid000",
	"name" : "小红",
	"age" : 26,
	"orders" : [
		{
			"_id" : ObjectId("4af2b2c6b138c267e414c071"),
			"uid" : "uid000",
			"product" : "产品1",
			"money" : 100
		},
		{
			"_id" : ObjectId("4af2b2c6b138c267e414c072"),
			"uid" : "uid000",
			"product" : "产品2",
			"money" : 200
		}
	]
},
{
	"_id" : ObjectId("5af2b2c6b138c267e414c073"),
	"uid" : "uid001",
	"name" : "小芳",
	"age" : 27,
	"orders" : [
		{
			"_id" : ObjectId("4af2b2c6b138c267e414c073"),
			"uid" : "uid001",
			"product" : "产品1",
			"money" : 100
		},
		{
			"_id" : ObjectId("4af2b2c6b138c267e414c074"),
			"uid" : "uid001",
			"product" : "产品2",
			"money" : 200
		}
	]
}]

二、拆分orders

db.user.aggregate([
{
    $lookup: { 
      from: "order", 
      localField: "uid", 
      foreignField: "uid", 
      as: "orders"
    }
},
{
    $unwind: { 
      path: "$orders",
      preserveNullAndEmptyArrays: true 
    }
}
]).pretty();

db.user.aggregate([
{
    $lookup: { // 左连接
      from: "order", // 关联到order表
      localField: "uid", // user 表关联的字段
      foreignField: "uid", // order 表关联的字段
      as: "orders"
    }
},
{
    $unwind: { // 拆分子数组
      path: "$orders",
      preserveNullAndEmptyArrays: true // 空的数组也拆分
    }
}
]);

结果

[
  {
    "_id" : ObjectId("5af2b2c6b138c267e414c072"),
    "uid" : "uid000",
    "name" : "小红",
    "age" : 26,
    "orders" : {
      "_id" : ObjectId("4af2b2c6b138c267e414c071"),
      "uid" : "uid000",
      "product" : "产品1",
      "money" : 100
    }
  }
  {
    "_id" : ObjectId("5af2b2c6b138c267e414c072"),
    "uid" : "uid000",
    "name" : "小红",
    "age" : 26,
    "orders" : {
      "_id" : ObjectId("4af2b2c6b138c267e414c072"),
      "uid" : "uid000",
      "product" : "产品2",
      "money" : 200
    }
  }
  {
    "_id" : ObjectId("5af2b2c6b138c267e414c073"),
    "uid" : "uid001",
    "name" : "小芳",
    "age" : 27,
    "orders" : {
      "_id" : ObjectId("4af2b2c6b138c267e414c073"),
      "uid" : "uid001",
      "product" : "产品1",
      "money" : 100
    }
  }
  {
    "_id" : ObjectId("5af2b2c6b138c267e414c073"),
    "uid" : "uid001",
    "name" : "小芳",
    "age" : 27,
    "orders" : {
      "_id" : ObjectId("4af2b2c6b138c267e414c074"),
      "uid" : "uid001",
      "product" : "产品2",
      "money" : 200
    }
  }
]

三、分组求和并返回字段数据

db.user.aggregate([
{
    $lookup: { 
      from: "order", 
      localField: "uid", 
      foreignField: "uid", 
      as: "orders"
    }
}, 
{
    $unwind: { 
      path: "$orders",
      preserveNullAndEmptyArrays: true 
    }
}, 
{ 
    $group: { 
      _id: "$_id",
      name: { $first: "$name" },
      age: { $first: "$age" },
      money: {$sum: "$orders.money"}
    }
}]).pretty();

db.user.aggregate([{
$lookup: { // 左连接
  from: "order", // 关联到order表
  localField: "uid", // user 表关联的字段
  foreignField: "uid", // order 表关联的字段
  as: "orders"
}
}, {
$unwind: { // 拆分子数组
  path: "$orders",
  preserveNullAndEmptyArrays: true // 空的数组也拆分
}
}, { // 分组求和并返回
$group: { // 分组查询
  _id: "$_id",
  name: { $first: "$name" },
  age: { $first: "$age" },
  money: {$sum: "$orders.money"}
}
}]);

[

  {
    "_id" : ObjectId("5af2b2c6b138c267e414c072"),
    "name" : "小红",
    "age" : 26,
    "money" : 300
  }
  {
    "_id" : ObjectId("5af2b2c6b138c267e414c073"),
    "name" : "小芳",
    "age" : 27,
    "money" : 300
  }

]

四、查询用户的订单信息(订单id、产品、价格、用户名)

db.order.aggregate([
{
    $lookup: {
      from: "user",
      localField: "openid",
      foreignField: "openid",
      as: "u"
    }
}, 
{
    $unwind: "$u"
}, 
{
    $addFields: {  name: "$u.name" }
}, 
{
    $project: {
      _id: 1,
      product: 1,
      money: 1,
      name: 1
    }
}]).pretty();

[
  {
    "_id" : ObjectId("4af2b2c6b138c267e414c071"),
    "product" : "产品1",
    "money" : 100,
    "name" : "小红"
  }
  {
    "_id" : ObjectId("4af2b2c6b138c267e414c071"),
    "product" : "产品1",
    "money" : 100,
    "name" : "小芳"
  }
  {
    "_id" : ObjectId("4af2b2c6b138c267e414c072"),
    "product" : "产品2",
    "money" : 200,
    "name" : "小红"
  }
  {
    "_id" : ObjectId("4af2b2c6b138c267e414c072"),
    "product" : "产品2",
    "money" : 200,
    "name" : "小芳"
  }
  {
    "_id" : ObjectId("4af2b2c6b138c267e414c073"),
    "product" : "产品1",
    "money" : 100,
    "name" : "小红"
  }
  {
    "_id" : ObjectId("4af2b2c6b138c267e414c073"),
    "product" : "产品1",
    "money" : 100,
    "name" : "小芳"
  }
  {
    "_id" : ObjectId("4af2b2c6b138c267e414c074"),
    "product" : "产品2",
    "money" : 200,
    "name" : "小红"
  }
  {
    "_id" : ObjectId("4af2b2c6b138c267e414c074"),
    "product" : "产品2",
    "money" : 200,
    "name" : "小芳"
  }

]