MongoDB使用aggregate联合查询

一、 lookup的功能及语法
主要功能 是将每个输入待处理的文档，经过$lookup 阶段的处理，输出的新文档中会包含一个新生成的数组列（户名可根据需要命名新key的名字 ）。数组列存放的数据 是 来自 被Join 集合的适配文档，如果没有，集合为空（即 为[ ]）
1、基本语法
from需要join的表， localField相当于主表的键，foreignFiel相当于join的键

db.getCollection.aggregate{
   $lookup:
     {
       from: <collection to join>,#关联的表
       localField: <field from the input documents>,#主表字段
       foreignField: <field from the documents of the "from" collection>,#join的表字段
       as: <output array field>#重命名
     }
}

表一

db.cs.insert([{
    "crumb_s": "摄像机",
    "crumb_url": "cat=670,716,726",
},
{
    "crumb_s": "投影机",
    "crumb_url": "cat=670,716,728",
},
{
    "crumb_s": "照相机",
    "crumb_url": "cat=670,716,729",
}
])

表二

db.cs.insert([{
    "crumb_s": "摄像机",
    "crumb_url": "cat=670,716,726",
},
{
    "crumb_s": "投影机",
    "crumb_url": "cat=670,716,728",
},
{
    "crumb_s": "照相机",
    "crumb_url": "cat=670,716,729",
}
])

表二：

db.sc.insert([{
    "crumb_s": "摄像机",
     "data_time": "2020/08/03",
},
{
    "crumb_s": "投影机",
    "data_time": "2020/08/07",
},
{
    "crumb_s": "照相机",
    "data_time": "2020/08/04",
}
])

查询

db.cs.aggregate({
	"$lookup":{
         "from": "sc",#次表
         "localField": "crumb_s",#主表的
         "foreignField": "crumb_s", #次表的
         "as": "inventory_docs"
       }

})

由于MongoDB的键不唯一多以join查询出来的数据是一个list
查询结果：

// 1
{
    "_id": ObjectId("5f28fe08e43a000024000eb5"),
    "crumb_s": "投影机",
    "crumb_url": "cat=670,716,72",
    "inventory_docs": [
        {
            "_id": ObjectId("5f28fec5e43a000024000eba"),
            "crumb_s": "投影机",
            "data_time": "2020/08/07"
        }
    ]
}

// 2
{
    "_id": ObjectId("5f28fe54e43a000024000eb6"),
    "crumb_s": "摄像机",
    "crumb_url": "cat=670,716,726",
    "inventory_docs": [
        {
            "_id": ObjectId("5f28fec5e43a000024000eb9"),
            "crumb_s": "摄像机",
            "data_time": "2020/08/03"
        }
    ]
}

// 3
{
    "_id": ObjectId("5f28fe54e43a000024000eb7"),
    "crumb_s": "投影机",
    "crumb_url": "cat=670,716,728",
    "inventory_docs": [
        {
            "_id": ObjectId("5f28fec5e43a000024000eba"),
            "crumb_s": "投影机",
            "data_time": "2020/08/07"
        }
    ]
}

// 4
{
    "_id": ObjectId("5f28fe54e43a000024000eb8"),
    "crumb_s": "照相机",
    "crumb_url": "cat=670,716,729",
    "inventory_docs": [
        {
            "_id": ObjectId("5f28fec5e43a000024000ebb"),
            "crumb_s": "照相机",
            "data_time": "2020/08/04"
        }
    ]
}