MongoDB联表查询

创建表user、order

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

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", // 关联到order表
  localField: "uid", // user 表关联的字段
  foreignField: "uid", // order 表关联的字段
  as: "orders"
}
}]);

返回结果:
// 1 
{
  "_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
      }
  ]
}

// 2
{
  "_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", // 关联到order表
  localField: "uid", // user 表关联的字段
  foreignField: "uid", // order 表关联的字段
  as: "orders"
}
},
{
$unwind: { // 拆分子数组
  path: "$orders",
  preserveNullAndEmptyArrays: true // 空的数组也拆分
}
}
]);

返回结果：
// 1
{
  "_id": ObjectId("5af2b2c6b138c267e414c072"),
  "uid": "uid000",
  "name": "小红",
  "age": 26,
  "orders": {
      "_id": ObjectId("4af2b2c6b138c267e414c071"),
      "uid": "uid000",
      "product": "产品1",
      "money": 100
  }
}

// 2
{
  "_id": ObjectId("5af2b2c6b138c267e414c072"),
  "uid": "uid000",
  "name": "小红",
  "age": 26,
  "orders": {
      "_id": ObjectId("4af2b2c6b138c267e414c072"),
      "uid": "uid000",
      "product": "产品2",
      "money": 200
  }
}

// 3
{
  "_id": ObjectId("5af2b2c6b138c267e414c073"),
  "uid": "uid001",
  "name": "小芳",
  "age": 27,
  "orders": {
      "_id": ObjectId("4af2b2c6b138c267e414c073"),
      "uid": "uid001",
      "product": "产品1",
      "money": 100
  }
}

// 4
{
  "_id": ObjectId("5af2b2c6b138c267e414c073"),
  "uid": "uid001",
  "name": "小芳",
  "age": 27,
  "orders": {
      "_id": ObjectId("4af2b2c6b138c267e414c074"),
      "uid": "uid001",
      "product": "产品2",
      "money": 200
  }
}

• 分组求和并返回字段数据

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"}
}
}]);

返回结果：
// 1
{
  "_id": ObjectId("5af2b2c6b138c267e414c073"),
  "name": "小芳",
  "age": 27,
  "money": 300
}

// 2
{
  "_id": ObjectId("5af2b2c6b138c267e414c072"),
  "name": "小红",
  "age": 26,
  "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
}
}]);

返回结果：
// 1
{
  "_id": ObjectId("4af2b2c6b138c267e414c071"),
  "product": "产品1",
  "money": 100,
  "name": "小红"
}

// 2
{
  "_id": ObjectId("4af2b2c6b138c267e414c071"),
  "product": "产品1",
  "money": 100,
  "name": "小芳"
}

// 3
{
  "_id": ObjectId("4af2b2c6b138c267e414c072"),
  "product": "产品2",
  "money": 200,
  "name": "小红"
}

// 4
{
  "_id": ObjectId("4af2b2c6b138c267e414c072"),
  "product": "产品2",
  "money": 200,
  "name": "小芳"
}

// 5
{
  "_id": ObjectId("4af2b2c6b138c267e414c073"),
  "product": "产品1",
  "money": 100,
  "name": "小红"
}

// 6
{
  "_id": ObjectId("4af2b2c6b138c267e414c073"),
  "product": "产品1",
  "money": 100,
  "name": "小芳"
}

// 7
{
  "_id": ObjectId("4af2b2c6b138c267e414c074"),
  "product": "产品2",
  "money": 200,
  "name": "小红"
}

// 8
{
  "_id": ObjectId("4af2b2c6b138c267e414c074"),
  "product": "产品2",
  "money": 200,
  "name": "小芳"
}