python mongodb lookup关联查询

$lookup 是 Mongodb 3.2版本 新增的聚合框架中的一种查询方式； 主要用来实现多表链接查询； 相当关系型数据库中多表链接查询。

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

字段	描述
from	同一个database中用于连接查询的collection的名称
localField	源集合中的match值，如果输入的集合中，某文档没有 localField这个Key（Field），在处理的过程中，会默认为此文档含有 localField：null的键值对。
foreignField	存在于from那个集合的外键
as	别名
let	定义参数，在pipeline中使用
pipeline	对join进来的数据做筛选

2. 例子、插入orders数据

import pymongo
import random
import string
from pymongo.collation import Collation
random.seed(10)
letters = string.ascii_lowercase
upper = string.ascii_uppercase


class MongoDBServer():
    def __init__(self,database,collation) -> None:
        self.client = pymongo.MongoClient('mongodb://ellis:ellischen@192.168.214.133:32000/')
        self.database = self.client[database]
        
        self.colleceion = self.database[collation]
        
    def insert_many(self,documents):
        self.colleceion.insert_many(documents)


server = MongoDBServer('lookup','orders')

server.insert_many([
   { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
   { "_id" : 3  }
] )

插入inventory数据

import pymongo
import random
import string
from pymongo.collation import Collation
random.seed(10)
letters = string.ascii_lowercase
upper = string.ascii_uppercase


class MongoDBServer():
    def __init__(self,database,collation) -> None:
        self.client = pymongo.MongoClient('mongodb://ellis:ellischen@192.168.214.133:32000/')
        self.database = self.client[database]
        
        self.colleceion = self.database[collation]
        
    def insert_many(self,documents):
        self.colleceion.insert_many(documents)
        
    def lookup(self,destination,localField,foreignField,as_field):
        return self.colleceion.aggregate([{"$lookup":{"from":destination,"localField":localField,"foreignField":foreignField,"as":as_field}}])


server = MongoDBServer('lookup','inventory')

server.insert_many([
   { "_id" : 1, "sku" : "almonds", "description": "product 1", "instock" : 120 },
   { "_id" : 2, "sku" : "bread", "description": "product 2", "instock" : 80 },
   { "_id" : 3, "sku" : "cashews", "description": "product 3", "instock" : 60 },
   { "_id" : 4, "sku" : "pecans", "description": "product 4", "instock" : 70 },
   { "_id" : 5, "sku": None, "description": "Incomplete" },
   { "_id" : 6 }
] )

inventory 和orders通过item以及sku两个字段关联

3. 使用lookup查询

import pymongo
import random
import string
from pymongo.collation import Collation
random.seed(10)
letters = string.ascii_lowercase
upper = string.ascii_uppercase


class MongoDBServer():
    def __init__(self,database,collation) -> None:
        self.client = pymongo.MongoClient('mongodb://ellis:ellischen@192.168.214.133:32000/')
        self.database = self.client[database]
        
        self.colleceion = self.database[collation]
        
    def insert_many(self,documents):
        self.colleceion.insert_many(documents)
        
    def lookup(self,destination,localField,foreignField,as_field):
        return self.colleceion.aggregate([{"$lookup":{"from":destination,"localField":localField,"foreignField":foreignField,"as":as_field}}])


server = MongoDBServer('lookup','orders')

for item in server.lookup("inventory","item","sku","inventory_docs"):
    print(item)

4. lookup 与array类型一起使用

import pymongo
import random
import string
from pymongo.collation import Collation

random.seed(10)
letters = string.ascii_lowercase
upper = string.ascii_uppercase


class MongoDBServer():
    def __init__(self,database,collation) -> None:
        self.client = pymongo.MongoClient('mongodb://ellis:ellischen@192.168.214.133:32000/')
        self.database = self.client[database]
        
        self.colleceion = self.database[collation]
        
    def insert_many(self,documents):
        self.colleceion.insert_many(documents)
        
    def lookup(self,destination,localField,foreignField,as_field):
        return self.colleceion.aggregate([{"$lookup":{"from":destination,"localField":localField,"foreignField":foreignField,"as":as_field}}])


server = MongoDBServer('lookup','classes')

server.insert_many([
   { "_id": 1, "title": "Reading is ...", "enrollmentlist": [ "giraffe2", "pandabear", "artie" ], "days": ["M", "W", "F"] },
   { "_id": 2, "title": "But Writing ...", "enrollmentlist": [ "giraffe1", "artie" ], "days": ["T", "F"] }
] )


server = MongoDBServer('lookup','members')

server.insert_many([
   { "_id": 1, "name": "artie",  "status": "A" },
   { "_id": 2, "name": "giraffe", "status": "D" },
   { "_id": 3, "name": "giraffe1",  "status": "A" },
   { "_id": 4, "name": "panda", "status": "A" },
   { "_id": 5, "name": "pandabear",  "status": "A" },
   { "_id": 6, "name": "giraffe2",  "status": "D" }
]  )
for item in server.lookup('members','enrollmentlist','name',"haha"):
    print(item)

5. let以及pipeline的使用

import pymongo
import random
import string
from pymongo.collation import Collation

random.seed(10)
letters = string.ascii_lowercase
upper = string.ascii_uppercase


class MongoDBServer():
    def __init__(self,database,collation) -> None:
        self.client = pymongo.MongoClient('mongodb://ellis:ellischen@192.168.214.133:32000/')
        self.database = self.client[database]
        
        self.colleceion = self.database[collation]
        
    def insert_many(self,documents):
        self.colleceion.insert_many(documents)
        
    def lookup(self,destination,localField,foreignField,as_field):
        return self.colleceion.aggregate([{"$lookup":{"from":destination,"localField":localField,"foreignField":foreignField,"as":as_field}}])


server = MongoDBServer('lookup','orders')


for item in server.colleceion.aggregate([
    {
        "$lookup":{
            "from": "inventory",
            "localField": "item",
            "foreignField": "sku",
            "let": {
                "instock": 100
            },
            "pipeline": [
                { "$match":
                    { "$expr":
                        { "$gt": [ "$instock", "$$instock"] }
                    }
                }
            ],
            "as": "haha"
        }
    },
    # {"$project":{"item":1,"price":1,"number":{"$size":"$haha"}}},
    # {"$match":{"number":{"$gte":1}}}
    {
        "$match":{"$expr":{"$gte":[{"$size":"$haha"}, 1]}}
    }
    
    
]):
    print(item)

https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup/