Mongodb聚合运算（三）

{
   $lookup:
     {
       from: ,
       localField: ,
       foreignField: ,
       as: 

     }
}

from：指定要与之连接的同一数据库中的集合，相当于从表

localField：指定从文档输入到$lookup阶段的字段，类似主表要关联的字段

foreignField：类似mysql从表要关联的字段

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

新增文档

db.java.insert(
[
  {
  "title": "db","name": "mongodb","type": "nosql"
},
  {
    "title": "db","name": "redis","type": "nosql"
  }
,
  {
    "title": "mq","name": "rocktmq","type": "master"
  }
])

db.data.insert(
[
  {"name":"mongodb","version": 5.0,"company": "mongodb","country":"USA"},
  {"name":"redis","version": 6.0,"company": "redis","country":"USA"}
])

连接查询：Java left join data on java.name = data.name

db.java.aggregate([
{
  $lookup:
  {
    from: "data",
    localField: "name",
    foreignField: "name",
    as: "dbname"
  }
}]).pretty()

结果如下：

{
        "_id" : ObjectId("616a6fa1a428b3665261cdcf"),
        "title" : "db",
        "name" : "mongodb",
        "type" : "nosql",
        "dbname" : [
                {
                        "_id" : ObjectId("616a6ff5a428b3665261cdd2"),
                        "name" : "mongodb",
                        "version" : 5,
                        "company" : "mongodb",
                        "country" : "USA"
                }
        ]
}
{
        "_id" : ObjectId("616a6fa1a428b3665261cdd0"),
        "title" : "db",
        "name" : "redis",
        "type" : "nosql",
        "dbname" : [
                {
                        "_id" : ObjectId("616a6ff5a428b3665261cdd3"),
                        "name" : "redis",
                        "version" : 6,
                        "company" : "redis",
                        "country" : "USA"
                }
        ]
}
{
        "_id" : ObjectId("616a6fa1a428b3665261cdd1"),
        "title" : "mq",
        "name" : "rocktmq",
        "type" : "master",
        "dbname" : [ ]
}

删除上面的java文档

db.java.drop()

新增java文档，name为数组形式

db.java.insert(
[
  {
  "title": "db","type": "dataSave","name": ["mysql","oracle","mongodb"]
},
  {
    "title": "mq","type": "translate", "name": ["rocketmq","kafka"]
  }
])

连接查询

db.java.aggregate([
{
  $lookup:
  {
    from: "data",
    localField: "name",
    foreignField: "name",
    as: "dbname"
  }
}]).pretty()

结果如下：

{
        "_id" : ObjectId("616a722ea428b3665261cdd4"),
        "title" : "db",
        "type" : "dataSave",
        "name" : [
                "mysql",
                "oracle",
                "mongodb"
        ],
        "dbname" : [
                {
                        "_id" : ObjectId("616a7234a428b3665261cdd6"),
                        "name" : "mongodb",
                        "version" : 5,
                        "company" : "mongodb",
                        "country" : "USA"
                }
        ]
}
{
        "_id" : ObjectId("616a722ea428b3665261cdd5"),
        "title" : "mq",
        "type" : "translate",
        "name" : [
                "rocketmq",
                "kafka"
        ],
        "dbname" : [ ]
}

删除之前的Java文档，新增Java文档

db.java.insert(
[
  {
  "title": "db","name": "mongodb","type": "nosql"
},
  {
    "title": "db","name": "redis","type": "nosql"
  }
,
  {
    "title": "mq","name": "rocktmq","type": "master"
  }
])

连接查询后执行merge操作

db.java.aggregate([
  {
    $lookup:
    {
      from: "data",
      localField: "name",
      foreignField: "name",
      as: "dbname"
    }
  },
  {
    $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$dbname", 0 ] }, "$$ROOT" ] } }
  },
  {
    $project: { dbname: 0 }
  }
]).pretty()

结果如下：

{
        "_id" : ObjectId("616a7383a428b3665261cdd8"),
        "name" : "mongodb",
        "version" : 5,
        "company" : "mongodb",
        "country" : "USA",
        "title" : "db",
        "type" : "nosql"
}
{
        "_id" : ObjectId("616a7383a428b3665261cdd9"),
        "name" : "redis",
        "version" : 6,
        "company" : "redis",
        "country" : "USA",
        "title" : "db",
        "type" : "nosql"
}
{
        "_id" : ObjectId("616a7383a428b3665261cdda"),
        "title" : "mq",
        "name" : "rocktmq",
        "type" : "master"
}