pymongo的聚合操作
数据类型样式
/* 1 */ { "_id" : ObjectId("5e5a32fe2a89d7c2fc05b9fc"), "user_id" : "1", "amount" : 500, "status" : "A" } /* 2 */ { "_id" : ObjectId("5e5a33092a89d7c2fc05ba07"), "user_id" : "1", "amount" : 250, "status" : "A" } /* 3 */ { "_id" : ObjectId("5e5a33152a89d7c2fc05ba13"), "user_id" : "2", "amount" : 200, "status" : "A" } /* 4 */ { "_id" : ObjectId("5e5a33262a89d7c2fc05ba1c"), "user_id" : "1", "amount" : 300, "status" : "B" }
$match：过滤数据，返回符合条件的数据
def aggregate(self): match_dict = {"$match":{"status":"A"}} result = self.db["test_info"].aggregate([match_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000002FF1648> {'_id': ObjectId('5e5a32fe2a89d7c2fc05b9fc'), 'user_id': '1', 'amount': 500, 'status': 'A'} {'_id': ObjectId('5e5a33092a89d7c2fc05ba07'), 'user_id': '1', 'amount': 250, 'status': 'A'} {'_id': ObjectId('5e5a33152a89d7c2fc05ba13'), 'user_id': '2', 'amount': 200, 'status': 'A'}
$group：将过滤后的数据进行分组
def aggregate_match_group(self): match_dict = {"$match": {"status": "A"}} group_dict = {"$group":{"_id":"$user_id"}} result = self.db["test_info"].aggregate([match_dict,group_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000002FEF708> {'_id': '2'} {'_id': '1'}
# 注意： {"$group":{"_id":"$user_id"}} 分组的名称必须是_id才行换成其他key或者自己重新命名key报错：pymongo.errors.OperationFailure: The field 'user_id' must be an accumulator object
分组后，我们要求，每组的amount的总和是多少？
def aggregate_match_group(self): match_dict = {"$match": {"status": "A"}} group_dict = {"$group":{"_id":"$user_id","amount_total":{"$sum":"$amount"}}} result = self.db["test_info"].aggregate([match_dict,group_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000002FECD48> {'_id': '2', 'amount_total': 200} {'_id': '1', 'amount_total': 750}
# 注意：虽然分了两组，但是其实第二组，包含了两个内容
怎么才能显示，每个里面成员的数量呢？
def aggregate_match_group(self): match_dict = {"$match": {"status": "A"}} group_dict = {"$group":{"_id":"$user_id","part_quantity":{"$sum":1}}} result = self.db["test_info"].aggregate([match_dict,group_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000002FF0E08> {'_id': '2', 'part_quantity': 1} {'_id': '1', 'part_quantity': 2}
# 注意： {"$sum":1} 表示组内有一个，按照1递增， {"$sum":2} 就变成了 {'_id': '1', 'part_quantity': 4} 也就是按照2递增！
如果我们想知道整个文档里面符合$match过滤条件的文档有多少个呢？
def aggregate_match_group(self): match_dict = {"$match": {"status": "A"}} group_dict = {"$group":{"_id":None,"part_quantity":{"$sum":1}}} result = self.db["test_info"].aggregate([match_dict,group_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000002FEBFC8> {'_id': None, 'part_quantity': 3}
如果想知道整个collection里面有多少个文档呢？
def aggregate_match_group(self): match_dict = {"$match": {}} group_dict = {"$group":{"_id":None,"part_quantity":{"$sum":1}}} result = self.db["test_info"].aggregate([match_dict,group_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000002FF1D48> {'_id': None, 'part_quantity': 4}
将$match过滤条件设置为{ }，就可以作用于整个collection，$group分组条件"_id"：None，表示文档不分组，也就是整个文档是一组！
/* 1 */ { "_id" : ObjectId("5e5a41b22a89d7c2fc05c1c5"), "user_id" : "1", "name" : "科比", "hometown" : "费城", "age" : "100", "gender" : "男" } /* 2 */ { "_id" : ObjectId("5e5a41db2a89d7c2fc05c1dc"), "user_id" : "2", "name" : "纳什", "hometown" : "加拿大", "age" : "100", "gender" : "男" } /* 3 */ { "_id" : ObjectId("5e5a42022a89d7c2fc05c1f1"), "user_id" : "3", "name" : "蔡徐坤", "hometown" : "不详", "age" : "100", "gender" : "女" } /* 4 */ { "_id" : ObjectId("5e5a42252a89d7c2fc05c204"), "user_id" : "4", "name" : "gigi", "hometown" : "洛杉矶", "age" : "100", "gender" : "女" }
怎么获取不同性别的人的所有user_id呢？
def aggregate_match_group(self): match_dict = {"$match": {}} group_dict = {"$group":{"_id":"$gender","user_id":{"$push":"$user_id"}}} result = self.db["test_info"].aggregate([match_dict,group_dict]) print(type(result)) print(result) for i in result: print(i) {'_id': '女', 'user_id': ['3', '4']} {'_id': '男', 'user_id': ['1', '2']}
# 注意：$push：将结果追加到列表中
def aggregate_match_group(self): match_dict = {"$match": {}} group_dict = {"$group":{"_id":"$gender","user_id":{"$push":"$$ROOT"}}} result = self.db["test_info"].aggregate([match_dict,group_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000002FF0DC8> {'_id': '女', 'user_id': [{'_id': ObjectId('5e5a42022a89d7c2fc05c1f1'), 'user_id': '3', 'name': '蔡徐坤', 'hometown': '不详', 'age': '100', 'gender': '女'}, {'_id': ObjectId('5e5a42252a89d7c2fc05c204'), 'user_id': '4', 'name': 'gigi', 'hometown': '洛杉矶', 'age': '100', 'gender': '女'}]} {'_id': '男', 'user_id': [{'_id': ObjectId('5e5a41b22a89d7c2fc05c1c5'), 'user_id': '1', 'name': '科比', 'hometown': '费城', 'age': '100', 'gender': '男'}, {'_id': ObjectId('5e5a41db2a89d7c2fc05c1dc'), 'user_id': '2', 'name': '纳什', 'hometown': '加拿大', 'age': '100', 'gender': '男'}]}
# $$sort将整个文档放入列表中
$gorup分组条件的 "_id" 多条件分组
def aggregate_match_group(self): match_dict = {"$match": {}} group_dict = {"$group":{"_id":{"user_id":"$user_id","name":"$name","hometown":"$hometown","age":"$age","gender":"$gender"}}} result = self.db["test_info"].aggregate([match_dict,group_dict]) print(type(result)) print(result) for i in result: print(i) {'_id': {'user_id': '4', 'name': 'gigi', 'hometown': '洛杉矶', 'age': '100', 'gender': '女'}} {'_id': {'user_id': '3', 'name': '蔡徐坤', 'hometown': '不详', 'age': '100', 'gender': '女'}} {'_id': {'user_id': '2', 'name': '纳什', 'hometown': '加拿大', 'age': '100', 'gender': '男'}} {'_id': {'user_id': '1', 'name': '科比', 'hometown': '费城', 'age': '100', 'gender': '男'}}
def aggregate_match_group(self): match_dict = {"$match": {}} group_dict = {"$group":{"_id":{"name":"$name","age":"$age","gender":"$gender"}}} result = self.db["test_info"].aggregate([match_dict,group_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000002D4EE48> {'_id': {'name': 'gigi', 'age': '100', 'gender': '女'}} {'_id': {'name': '蔡徐坤', 'age': '100', 'gender': '女'}} {'_id': {'name': '纳什', 'age': '100', 'gender': '男'}} {'_id': {'name': '科比', 'age': '100', 'gender': '男'}}
多条件分组，并统计数量
def aggregate_match_group(self): match_dict = {"$match": {}} group_dict = {"$group":{"_id":{"年龄":"$age","性别":"$gender"},"人数":{"$sum":1}}} result = self.db["test_info"].aggregate([match_dict,group_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000002FECD88> {'_id': {'年龄': '100', '性别': '女'}, '人数': 2} {'_id': {'年龄': '100', '性别': '男'}, '人数': 2}
对查询数据进行修改
/* 1 */ { "_id" : ObjectId("5e5a41b22a89d7c2fc05c1c5"), "user_id" : "1", "name" : "科比", "hometown" : "费城", "age" : "42", "gender" : "男" } /* 2 */ { "_id" : ObjectId("5e5a41db2a89d7c2fc05c1dc"), "user_id" : "2", "name" : "纳什", "hometown" : "加拿大", "age" : "40", "gender" : "男" } /* 3 */ { "_id" : ObjectId("5e5a42022a89d7c2fc05c1f1"), "user_id" : "3", "name" : "蔡徐坤", "hometown" : "不详", "age" : "3", "gender" : "女" } /* 4 */ { "_id" : ObjectId("5e5a42252a89d7c2fc05c204"), "user_id" : "4", "name" : "gigi", "hometown" : "洛杉矶", "age" : "14", "gender" : "女" }
获取年龄年龄大于3岁的信息
$match
def aggregate_match_group(self): match_dict = {"$match":{"age":{"$gt":"3"}}} result = self.db["test_info"].aggregate([match_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000002FF1C48> {'_id': ObjectId('5e5a41b22a89d7c2fc05c1c5'), 'user_id': '1', 'name': '科比', 'hometown': '费城', 'age': '42', 'gender': '男'} {'_id': ObjectId('5e5a41db2a89d7c2fc05c1dc'), 'user_id': '2', 'name': '纳什', 'hometown': '加拿大', 'age': '40', 'gender': '男'}
# 查询错误：gigi的年龄也是大于3，不显示，我们将数据里面的年龄类型从str换成int类型，继续查看
def aggregate_match_group(self): match_dict = {"$match":{"age":{"$gt":3}}} result = self.db["test_info"].aggregate([match_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000002FF1C88> {'_id': ObjectId('5e5a41b22a89d7c2fc05c1c5'), 'user_id': '1', 'name': '科比', 'hometown': '费城', 'age': 42, 'gender': '男'} {'_id': ObjectId('5e5a41db2a89d7c2fc05c1dc'), 'user_id': '2', 'name': '纳什', 'hometown': '加拿大', 'age': 40, 'gender': '男'} {'_id': ObjectId('5e5a42252a89d7c2fc05c204'), 'user_id': '4', 'name': 'gigi', 'hometown': '洛杉矶', 'age': 14, 'gender': '女'}
# 查询正确：因此当进行比较值的操作，注意字段类型必须是int类型
获取年龄大于3岁，不同性别的人数
def aggregate_match_group(self): match_dict = {"$match":{"age":{"$gt":3}}} group_dict = {"$group":{"_id":"$gender","数量":{"$sum":1}}} result = self.db["test_info"].aggregate([match_dict,group_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000002FF1C88> {'_id': '女', '数量': 1} {'_id': '男', '数量': 2}
$preject类型与find里面的limit，需要显示的设置为1，不显示的设置为0
def aggregate_project(self): project_dict = {"$project":{"_id":0,"name":1,"hometown":1}} result = self.db["test_info"].aggregate([project_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000002FE9F88> {'name': '科比', 'hometown': '费城'} {'name': '纳什', 'hometown': '加拿大'} {'name': '蔡徐坤', 'hometown': '不详'} {'name': 'gigi', 'hometown': '洛杉矶'}
# 注意：其他字段没有赋值1就不显示，但是_id字段除外，不设置，默认显示
def aggregate_project(self): group_dict = {"$group":{"_id":"$gender","quantity":{"$sum":1}}} project_dict = {"$project":{"_id":1,"quantity":1}} result = self.db["test_info"].aggregate([group_dict,project_dict]) print(type(result)) print(result) for i in result: print(i) {'_id': '女', 'quantity': 2} {'_id': '男', 'quantity': 2}
$sort：排序命令
年龄从小到大返回排序好的数据
def aggregate_sort(self): sort_dict = {"$sort":{"age":1}} result = self.db["test_info"].aggregate([sort_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000003012148> {'_id': ObjectId('5e5a42022a89d7c2fc05c1f1'), 'user_id': '3', 'name': '蔡徐坤', 'hometown': '不详', 'age': 3, 'gender': '女'} {'_id': ObjectId('5e5a42252a89d7c2fc05c204'), 'user_id': '4', 'name': 'gigi', 'hometown': '洛杉矶', 'age': 14, 'gender': '女'} {'_id': ObjectId('5e5a41db2a89d7c2fc05c1dc'), 'user_id': '2', 'name': '纳什', 'hometown': '加拿大', 'age': 40, 'gender': '男'} {'_id': ObjectId('5e5a41b22a89d7c2fc05c1c5'), 'user_id': '1', 'name': '科比', 'hometown': '费城', 'age': 42, 'gender': '男'}
年龄从大到小返回排序好的数据
def aggregate_sort(self): sort_dict = {"$sort":{"age":-1}} result = self.db["test_info"].aggregate([sort_dict]) print(type(result)) print(result) for i in result: print(i) <class 'pymongo.command_cursor.CommandCursor'> <pymongo.command_cursor.CommandCursor object at 0x0000000002FE5F88> {'_id': ObjectId('5e5a41b22a89d7c2fc05c1c5'), 'user_id': '1', 'name': '科比', 'hometown': '费城', 'age': 42, 'gender': '男'} {'_id': ObjectId('5e5a41db2a89d7c2fc05c1dc'), 'user_id': '2', 'name': '纳什', 'hometown': '加拿大', 'age': 40, 'gender': '男'} {'_id': ObjectId('5e5a42252a89d7c2fc05c204'), 'user_id': '4', 'name': 'gigi', 'hometown': '洛杉矶', 'age': 14, 'gender': '女'} {'_id': ObjectId('5e5a42022a89d7c2fc05c1f1'), 'user_id': '3', 'name': '蔡徐坤', 'hometown': '不详', 'age': 3, 'gender': '女'}
数据类型
/* 10 */ { "_id" : ObjectId("5e58c4102a89d7c2fc051ba4"), "vaccine_name" : "破伤风", "vaccine_id" : "2", "user_id" : "110", "farm_id" : "110", "fold_id" : "110", "farm_name" : "110牧场", "fold_name" : "110圈舍", "animal_number" : "133", "equipment_number" : "133", "type" : "goat", "inject_quantity" : "100", "vaccine_time" : ISODate("2020-06-15T15:45:22.000Z"), "is_delete" : "0" } /* 11 */ { "_id" : ObjectId("5e5a510d2a89d7c2fc05cac7"), "vaccine_name" : "破伤风", "vaccine_id" : "2", "user_id" : "110", "farm_id" : "110", "fold_id" : "110", "farm_name" : "110牧场", "fold_name" : "110圈舍", "animal_number" : "133", "equipment_number" : "133", "type" : "goat", "inject_quantity" : "100", "vaccine_time" : ISODate("2020-07-15T15:45:22.000Z"), "is_delete" : "0" } /* 12 */ { "_id" : ObjectId("5e5a511b2a89d7c2fc05cad2"), "vaccine_name" : "破伤风", "vaccine_id" : "2", "user_id" : "110", "farm_id" : "110", "fold_id" : "110", "farm_name" : "110牧场", "fold_name" : "110圈舍", "animal_number" : "133", "equipment_number" : "133", "type" : "goat", "inject_quantity" : "100", "vaccine_time" : ISODate("2020-08-15T15:45:22.000Z"), "is_delete" : "0" } /* 13 */ { "_id" : ObjectId("5e5a51282a89d7c2fc05cada"), "vaccine_name" : "破伤风", "vaccine_id" : "2", "user_id" : "110", "farm_id" : "110", "fold_id" : "110", "farm_name" : "110牧场", "fold_name" : "110圈舍", "animal_number" : "133", "equipment_number" : "133", "type" : "goat", "inject_quantity" : "100", "vaccine_time" : ISODate("2020-10-15T15:45:22.000Z"), "is_delete" : "0" } /* 14 */ { "_id" : ObjectId("5e5a51422a89d7c2fc05caec"), "vaccine_name" : "破伤风", "vaccine_id" : "2", "user_id" : "110", "farm_id" : "110", "fold_id" : "110", "farm_name" : "110牧场", "fold_name" : "110圈舍", "animal_number" : "133", "equipment_number" : "133", "type" : "goat", "inject_quantity" : "100", "vaccine_time" : ISODate("2020-11-15T15:45:22.000Z"), "is_delete" : "0" } /* 15 */ { "_id" : ObjectId("5e5a514d2a89d7c2fc05caf5"), "vaccine_name" : "破伤风", "vaccine_id" : "2", "user_id" : "110", "farm_id" : "110", "fold_id" : "110", "farm_name" : "110牧场", "fold_name" : "110圈舍", "animal_number" : "133", "equipment_number" : "133", "type" : "goat", "inject_quantity" : "100", "vaccine_time" : ISODate("2020-12-15T15:45:22.000Z"), "is_delete" : "0" }
需求：获取equipment_number=13，vaccine_time按照时间倒叙排列，返回数据
def get_all_by_time_object(self,collection): """按照时间类型排序 vaccine_time的类型是 ISODate("2020-12-15T15:45:22.000Z")类型""" if self.connect_result: match_dict = {"$match":{"equipment_number":"133","type":"goat"}} sort_dict = {"$sort":{"vaccine_time":-1}} result = self.db[collection].aggregate([match_dict,sort_dict]) for i in result: print(i) {'_id': ObjectId('5e5a514d2a89d7c2fc05caf5'), 'vaccine_name': '破伤风', 'vaccine_id': '2', 'user_id': '110', 'farm_id': '110', 'fold_id': '110', 'farm_name': '110牧场', 'fold_name': '110圈舍', 'animal_number': '133', 'equipment_number': '133', 'type': 'goat', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 12, 15, 15, 45, 22), 'is_delete': '0'} {'_id': ObjectId('5e5a51422a89d7c2fc05caec'), 'vaccine_name': '破伤风', 'vaccine_id': '2', 'user_id': '110', 'farm_id': '110', 'fold_id': '110', 'farm_name': '110牧场', 'fold_name': '110圈舍', 'animal_number': '133', 'equipment_number': '133', 'type': 'goat', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 11, 15, 15, 45, 22), 'is_delete': '0'} {'_id': ObjectId('5e5a51282a89d7c2fc05cada'), 'vaccine_name': '破伤风', 'vaccine_id': '2', 'user_id': '110', 'farm_id': '110', 'fold_id': '110', 'farm_name': '110牧场', 'fold_name': '110圈舍', 'animal_number': '133', 'equipment_number': '133', 'type': 'goat', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 10, 15, 15, 45, 22), 'is_delete': '0'} {'_id': ObjectId('5e5a511b2a89d7c2fc05cad2'), 'vaccine_name': '破伤风', 'vaccine_id': '2', 'user_id': '110', 'farm_id': '110', 'fold_id': '110', 'farm_name': '110牧场', 'fold_name': '110圈舍', 'animal_number': '133', 'equipment_number': '133', 'type': 'goat', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 8, 15, 15, 45, 22), 'is_delete': '0'} {'_id': ObjectId('5e5a510d2a89d7c2fc05cac7'), 'vaccine_name': '破伤风', 'vaccine_id': '2', 'user_id': '110', 'farm_id': '110', 'fold_id': '110', 'farm_name': '110牧场', 'fold_name': '110圈舍', 'animal_number': '133', 'equipment_number': '133', 'type': 'goat', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 7, 15, 15, 45, 22), 'is_delete': '0'} {'_id': ObjectId('5e58c4102a89d7c2fc051ba4'), 'vaccine_name': '破伤风', 'vaccine_id': '2', 'user_id': '110', 'farm_id': '110', 'fold_id': '110', 'farm_name': '110牧场', 'fold_name': '110圈舍', 'animal_number': '133', 'equipment_number': '133', 'type': 'goat', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 6, 15, 15, 45, 22), 'is_delete': '0'}
过滤掉一些字段，选择性显示需要的字段
def get_all_by_time_object(self,collection): """按照时间类型排序 vaccine_time的类型是 ISODate("2020-12-15T15:45:22.000Z")类型""" if self.connect_result: match_dict = {"$match":{"equipment_number":"133","type":"goat"}} sort_dict = {"$sort":{"vaccine_time":-1}} project_dict = {"$project":{"_id":0,"animal_number":1,"inject_quantity":1,"vaccine_time":1,"vaccine_name":1}} result = self.db[collection].aggregate([match_dict,sort_dict,project_dict]) for i in result: print(i) {'vaccine_name': '破伤风', 'animal_number': '133', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 12, 15, 15, 45, 22)} {'vaccine_name': '破伤风', 'animal_number': '133', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 11, 15, 15, 45, 22)} {'vaccine_name': '破伤风', 'animal_number': '133', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 10, 15, 15, 45, 22)} {'vaccine_name': '破伤风', 'animal_number': '133', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 8, 15, 15, 45, 22)} {'vaccine_name': '破伤风', 'animal_number': '133', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 7, 15, 15, 45, 22)} {'vaccine_name': '破伤风', 'animal_number': '133', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 6, 15, 15, 45, 22)}
$limit ：限制返回的条数
def get_all_by_limit(self,collection): if self.connect_result: match_dict = {"$match": {"equipment_number": "133", "type": "goat"}} sort_dict = {"$sort": {"vaccine_time": -1}} project_dict = { "$project": {"_id": 0, "animal_number": 1, "inject_quantity": 1, "vaccine_time": 1, "vaccine_name": 1}} limit_dict = {"$limit":2} result = self.db[collection].aggregate([match_dict, sort_dict, project_dict,limit_dict]) for i in result: print(i) {'vaccine_name': '破伤风', 'animal_number': '133', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 12, 15, 15, 45, 22)} {'vaccine_name': '破伤风', 'animal_number': '133', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 11, 15, 15, 45, 22)}
$skip：跳过指定数量，返回剩余数量的内容
def get_all_by_skip(self,collection): if self.connect_result: match_dict = {"$match": {"equipment_number": "133", "type": "goat"}} sort_dict = {"$sort": {"vaccine_time": -1}} project_dict = { "$project": {"_id": 0, "animal_number": 1, "inject_quantity": 1, "vaccine_time": 1, "vaccine_name": 1}} skip_dict = {"$skip":2} result = self.db[collection].aggregate([match_dict, sort_dict, project_dict,skip_dict]) for i in result: print(i) {'vaccine_name': '破伤风', 'animal_number': '133', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 10, 15, 15, 45, 22)} {'vaccine_name': '破伤风', 'animal_number': '133', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 8, 15, 15, 45, 22)} {'vaccine_name': '破伤风', 'animal_number': '133', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 7, 15, 15, 45, 22)} {'vaccine_name': '破伤风', 'animal_number': '133', 'inject_quantity': '100', 'vaccine_time': datetime.datetime(2020, 6, 15, 15, 45, 22)}
$ match过滤条件的或查询
数据结构如下
/* 1 */ { "_id" : ObjectId("5e5a41b22a89d7c2fc05c1c5"), "user_id" : "1", "name" : "科比", "hometown" : "费城", "age" : 42, "gender" : "男" } /* 2 */ { "_id" : ObjectId("5e5a41db2a89d7c2fc05c1dc"), "user_id" : "2", "name" : "纳什", "hometown" : "加拿大", "age" : 40, "gender" : "男" } /* 3 */ { "_id" : ObjectId("5e5a42022a89d7c2fc05c1f1"), "user_id" : "3", "name" : "蔡徐坤", "hometown" : "不详", "age" : 3, "gender" : "女" } /* 4 */ { "_id" : ObjectId("5e5a42252a89d7c2fc05c204"), "user_id" : "4", "name" : "gigi", "hometown" : "洛杉矶", "age" : 14, "gender" : "女" }
查询年龄大于小于14岁或者大于40岁的人的信息
def get_all_by_or_match(self,collection): if self.connect_result: match_dict = {"$match": {"$or":[{"age":{"$gt":40}},{"age":{"$lt":14}}]}} result = self.db[collection].aggregate([match_dict]) for i in result: print(i) {'_id': ObjectId('5e5a41b22a89d7c2fc05c1c5'), 'user_id': '1', 'name': '科比', 'hometown': '费城', 'age': 42, 'gender': '男'} {'_id': ObjectId('5e5a42022a89d7c2fc05c1f1'), 'user_id': '3', 'name': '蔡徐坤', 'hometown': '不详', 'age': 3, 'gender': '女'}
$ match过滤条件的范围查询
$gt和$lt判断的范围都是int类型，那么我们要查找hometown 在列表中 ["加拿大","洛杉矶 ","费城 "]的数据，应该怎么办呢？
def get_all_by_in_match(self,collection): if self.connect_result: match_dict = {"$match": {"hometown":{"$in":["加拿大","洛杉矶","费城"]}}} result = self.db[collection].aggregate([match_dict]) for i in result: print(i) {'_id': ObjectId('5e5a41b22a89d7c2fc05c1c5'), 'user_id': '1', 'name': '科比', 'hometown': '费城', 'age': 42, 'gender': '男'} {'_id': ObjectId('5e5a41db2a89d7c2fc05c1dc'), 'user_id': '2', 'name': '纳什', 'hometown': '加拿大', 'age': 40, 'gender': '男'} {'_id': ObjectId('5e5a42252a89d7c2fc05c204'), 'user_id': '4', 'name': 'gigi', 'hometown': '洛杉矶', 'age': 14, 'gender': '女'}
查询年龄在[14,4,3]内的人的信息
def get_all_by_in_match(self,collection): if self.connect_result: match_dict = {"$match":{"age":{"$in":[14,40,3]}}} result = self.db[collection].aggregate([match_dict]) for i in result: print(i) {'_id': ObjectId('5e5a41db2a89d7c2fc05c1dc'), 'user_id': '2', 'name': '纳什', 'hometown': '加拿大', 'age': 40, 'gender': '男'} {'_id': ObjectId('5e5a42022a89d7c2fc05c1f1'), 'user_id': '3', 'name': '蔡徐坤', 'hometown': '不详', 'age': 3, 'gender': '女'} {'_id': ObjectId('5e5a42252a89d7c2fc05c204'), 'user_id': '4', 'name': 'gigi', 'hometown': '洛杉矶', 'age': 14, 'gender': '女'}
数据结构如下
/* 1 */ { "_id" : ObjectId("5e5b99052a89d7c2fc0653a0"), "farm_id" : "1", "animal_number" : "1", "milking_time" : ISODate("2020-02-01T15:45:22.000Z"), "milking_quantity" : 100 } /* 2 */ { "_id" : ObjectId("5e5b993d2a89d7c2fc0653cf"), "farm_id" : "1", "animal_number" : "2", "milking_time" : ISODate("2020-02-01T18:46:33.000Z"), "milking_quantity" : 120 } /* 3 */ { "_id" : ObjectId("5e5b996f2a89d7c2fc0653eb"), "farm_id" : "1", "animal_number" : "1", "milking_time" : ISODate("2020-02-02T08:45:22.000Z"), "milking_quantity" : 150 } /* 4 */ { "_id" : ObjectId("5e5b9a042a89d7c2fc06543e"), "farm_id" : "1", "animal_number" : "2", "milking_time" : ISODate("2020-02-02T09:33:22.000Z"), "milking_quantity" : 90 } /* 5 */ { "_id" : ObjectId("5e5b9a2b2a89d7c2fc065455"), "farm_id" : "1", "animal_number" : "1", "milking_time" : ISODate("2020-02-03T10:30:30.000Z"), "milking_quantity" : 98 } /* 6 */ { "_id" : ObjectId("5e5b9a452a89d7c2fc065464"), "farm_id" : "1", "animal_number" : "2", "milking_time" : ISODate("2020-02-03T11:45:22.000Z"), "milking_quantity" : 110 }
需求：牧场1下的所有羊，每天的产奶量平均值是多少，每三天的产奶量平均值是多少？
def get_all_by_avg_milk(self,collection): if self.connect_result: s_time = datetime(2020,2,1,00,00,00) e_time = datetime(2020,2,1,23,59,59) match_dict = {"$match": {"farm_id":"1","milking_time":{"$gte":s_time,"$lte":e_time}}} group_dict = {"$group":{"_id":None,"2020-2-1日产奶量平均值为":{"$avg":"$milking_quantity"}}} result = self.db[collection].aggregate([match_dict,group_dict]) for i in result: print(i) {'_id': None, '2020-2-1日产奶量平均值为': 110.0}
def get_all_by_avg_milk(self,collection): if self.connect_result: s_time = datetime(2020,2,1,00,00,00) e_time = datetime(2020,2,3,23,59,59) match_dict = {"$match": {"farm_id":"1","milking_time":{"$gte":s_time,"$lte":e_time}}} group_dict = {"$group":{"_id":None,"三天产奶量平均值为":{"$avg":"$milking_quantity"}}} result = self.db[collection].aggregate([match_dict,group_dict]) for i in result: print(i) {'_id': None, '三天产奶量平均值为': 111.33333333333333}
(100 + 120 + 150 + 90 + 98 + 110 )/3 = 222.6666
mogno给的结果是 222.666/2 = 111.3333 分组后一共取出六条数据，除以6了，造成结果错误，怎么解决呢？
先求总产量，然后分布计算结果
def get_all_by_avg_milk(self,collection): if self.connect_result: s_time = datetime(2020,2,1,00,00,00) e_time = datetime(2020,2,3,23,59,59) match_dict = {"$match": {"farm_id":"1","milking_time":{"$gte":s_time,"$lte":e_time}}} group_dict = {"$group":{"_id":None,"三天产奶量总和为":{"$sum":"$milking_quantity"}}} result = self.db[collection].aggregate([match_dict,group_dict]) for i in result: print(i) print("三天产奶量的平均值是%s"%(str(i.get("三天产奶量总和为")/3))) {'_id': None, '三天产奶量总和为': 668} 三天产奶量的平均值是222.66666666666666
需求：输出2020-2-1号产奶量最低的羊的编号和最高的羊的编号
def get_all_by_avg_milk(self,collection): if self.connect_result: s_time = datetime(2020,2,1,00,00,00) e_time = datetime(2020,2,1,23,59,59) match_dict = {"$match": {"farm_id":"1","milking_time":{"$gte":s_time,"$lte":e_time}}} group_dict = {"$group":{"_id":None,"max_quantity":{"$max":"$milking_quantity"}}} result = self.db[collection].aggregate([match_dict,group_dict]) for i in result: print(i) {'_id': None, 'max_quantity': 120}
max_quantity查库获得animal_nubmer
$unwind：针对文档里面的数组进行操作
数据类型
{ "_id" : ObjectId("5e5ccf222a89d7c2fc06e9d0"), "user_id" : "A", "data" : [ { "city" : "beijing", "income" : 100000 }, { "city" : "shanghai", "income" : 150000 }, { "city" : "shanghai", "income" : 150000 } ] }
结果是：将列表中的每一个内容和外面的键重新组合形成一条数据
def find_list(self,collection): unwind_dict = {"$unwind":"$data"} result = self.db[collection].aggregate([unwind_dict]) print(result) print(type(result)) for i in result: print(i) <pymongo.command_cursor.CommandCursor object at 0x0000000002D58488> <class 'pymongo.command_cursor.CommandCursor'> {'_id': ObjectId('5e5ccf222a89d7c2fc06e9d0'), 'user_id': 'A', 'data': {'city': 'beijing', 'income': 100000}} {'_id': ObjectId('5e5ccf222a89d7c2fc06e9d0'), 'user_id': 'A', 'data': {'city': 'shanghai', 'income': 150000}} {'_id': ObjectId('5e5ccf222a89d7c2fc06e9d0'), 'user_id': 'A', 'data': {'city': 'shanghai', 'income': 150000}}
需求，计算A在上海收入的总和是多少？
def find_list_for_sum(self,collection): match_dict1 = {"$match":{"user_id":"A"}} unwind_dict = {"$unwind":"$data"} match_dict2 = {"$match":{"data.city":"shanghai"}} group_dict = {"$group":{"_id":"$data.city","收入总和":{"$sum":"$data.income"}}} result = self.db[collection].aggregate([match_dict1,unwind_dict,match_dict2,group_dict]) print(result) print(type(result)) for i in result: print(i) <pymongo.command_cursor.CommandCursor object at 0x0000000002FE9FC8> <class 'pymongo.command_cursor.CommandCursor'> {'_id': 'shanghai', '收入总和': 300000}
# 补充一下，如果是列表，怎么给列表里面添加数据，怎么给从列表里面删除数据呢？ $addToSet 和 $pull
需求：给上面的数据的data列表中添加一条数据 {"city":"shenzhen","income":30000}
def add_to_list(self,collection): query_dict = dict() query_dict["user_id"] = "A" result = self.db[collection].update(query_dict,{"$addToSet":{"data":{"city":"shenzhen","income":30000}}}) if result.get("nModified") == 1: print("添加成功") { "_id" : ObjectId("5e5ccf222a89d7c2fc06e9d0"), "user_id" : "A", "data" : [ { "city" : "beijing", "income" : 100000 }, { "city" : "shanghai", "income" : 150000 }, { "city" : "shanghai", "income" : 150000 }, { "city" : "shenzhen", "income" : 30000 } ] }
# 问题：这种天界方式：不能向data列表里面添加相同的键值对，连续插入{"city":"shenzhen","income":20000}，并不会成功！
# TODO 待续
2020-3-20
需求：多个牧场下，每一个羊的饮水总数小于2的，返回其equipment_number
数据样式：
/* 1 */ { "_id" : ObjectId("5e746c378fc1e7a977e6be06"), "farm_id" : "123", "farm_name" : "测试", "fold_id" : "123", "fold_name" : "测试", "device_number" : "123", "equipment_number" : "123", "animal_number" : "123", "drink_quantity" : 100, "type" : "goat", "drink_time" : ISODate("2020-03-20T15:09:43.454Z") } /* 2 */ { "_id" : ObjectId("5e746c448fc1e7a977e6be07"), "farm_id" : "123", "farm_name" : "测试", "fold_id" : "123", "fold_name" : "测试", "device_number" : "123", "equipment_number" : "123", "animal_number" : "123", "drink_quantity" : 200, "type" : "goat", "drink_time" : ISODate("2020-03-20T15:09:56.139Z") } /* 3 */ { "_id" : ObjectId("5e746c488fc1e7a977e6be08"), "farm_id" : "123", "farm_name" : "测试", "fold_id" : "123", "fold_name" : "测试", "device_number" : "123", "equipment_number" : "123", "animal_number" : "123", "drink_quantity" : 300, "type" : "goat", "drink_time" : ISODate("2020-03-20T15:10:00.115Z") } /* 4 */ { "_id" : ObjectId("5e7474b1e47b4ffc8fbd4d3b"), "farm_id" : "123", "farm_name" : "测试", "fold_id" : "123", "fold_name" : "测试", "device_number" : "123", "equipment_number" : "124", "animal_number" : "124", "drink_quantity" : 100, "type" : "goat", "drink_time" : ISODate("2020-03-20T15:45:53.727Z") } /* 5 */ { "_id" : ObjectId("5e7474b7e47b4ffc8fbd4d3c"), "farm_id" : "123", "farm_name" : "测试", "fold_id" : "123", "fold_name" : "测试", "device_number" : "123", "equipment_number" : "124", "animal_number" : "124", "drink_quantity" : 200, "type" : "goat", "drink_time" : ISODate("2020-03-20T15:45:59.674Z") } /* 6 */ { "_id" : ObjectId("5e7474c0e47b4ffc8fbd4d3d"), "farm_id" : "123", "farm_name" : "测试", "fold_id" : "123", "fold_name" : "测试", "device_number" : "123", "equipment_number" : "125", "animal_number" : "125", "drink_quantity" : 100, "type" : "goat", "drink_time" : ISODate("2020-03-20T15:46:08.953Z") } /* 7 */ { "_id" : ObjectId("5e748632217a21f9adb48c12"), "farm_id" : "125", "farm_name" : "测试", "fold_id" : "125", "fold_name" : "测试", "device_number" : "125", "equipment_number" : "125", "animal_number" : "125", "drink_quantity" : 100, "type" : "goat", "drink_time" : ISODate("2020-03-20T17:00:34.398Z") }
查询 123 125牧场下，饮水次数小于2的equipment_mumber，饮水次数就是有一条数据，就是饮水一次
def aggregate_many(self): # 获取所有牧场下，饮水次数小于2的羊的equipment_number match_dict = {"$match":{"farm_id":{"$in":["123","125"]}}} project_dict = {"$project":{"_id":0}} group_dict = {"$group":{"_id":{"equipment_number":"$equipment_number","farm_id":"$farm_id"},"total_count":{"$sum":1}}} # match_dict_1 = {"$match":{"total_count":{"$lt":2}}} result = self.db["sheep_water_intake"].aggregate([match_dict,project_dict,group_dict]) for i in result: print(i) {'_id': {'equipment_number': '125', 'farm_id': '125'}, 'total_count': 1} {'_id': {'equipment_number': '125', 'farm_id': '123'}, 'total_count': 1} {'_id': {'equipment_number': '124', 'farm_id': '123'}, 'total_count': 2} {'_id': {'equipment_number': '123', 'farm_id': '123'}, 'total_count': 3}
首先考虑去重的问题，group_dict = {"$group":{"_id":{"equipment_number":"$equipment_number","farm_id":"$farm_id"},"total_count":{"$sum":1}}}
group应该是先分组，分完组之后，进行累加，先看看不分组的数据
def aggregate_many(self): # 获取所有牧场下，饮水次数小于2的羊的equipment_number match_dict = {"$match":{"farm_id":{"$in":["123","125"]}}} project_dict = {"$project":{"_id":0}} # group_dict = {"$group":{"_id":{"equipment_number":"$equipment_number","farm_id":"$farm_id"},"total_count":{"$sum":1}}} match_dict_1 = {"$match":{"total_count":{"$lt":2}}} result = self.db["sheep_water_intake"].aggregate([match_dict,project_dict]) for i in result: print(i) {'farm_id': '123', 'farm_name': '测试', 'fold_id': '123', 'fold_name': '测试', 'device_number': '123', 'equipment_number': '123', 'animal_number': '123', 'drink_quantity': 100, 'type': 'goat', 'drink_time': datetime.datetime(2020, 3, 20, 15, 9, 43, 454000)} {'farm_id': '123', 'farm_name': '测试', 'fold_id': '123', 'fold_name': '测试', 'device_number': '123', 'equipment_number': '123', 'animal_number': '123', 'drink_quantity': 200, 'type': 'goat', 'drink_time': datetime.datetime(2020, 3, 20, 15, 9, 56, 139000)} {'farm_id': '123', 'farm_name': '测试', 'fold_id': '123', 'fold_name': '测试', 'device_number': '123', 'equipment_number': '123', 'animal_number': '123', 'drink_quantity': 300, 'type': 'goat', 'drink_time': datetime.datetime(2020, 3, 20, 15, 10, 0, 115000)} {'farm_id': '123', 'farm_name': '测试', 'fold_id': '123', 'fold_name': '测试', 'device_number': '123', 'equipment_number': '124', 'animal_number': '124', 'drink_quantity': 100, 'type': 'goat', 'drink_time': datetime.datetime(2020, 3, 20, 15, 45, 53, 727000)} {'farm_id': '123', 'farm_name': '测试', 'fold_id': '123', 'fold_name': '测试', 'device_number': '123', 'equipment_number': '124', 'animal_number': '124', 'drink_quantity': 200, 'type': 'goat', 'drink_time': datetime.datetime(2020, 3, 20, 15, 45, 59, 674000)} {'farm_id': '123', 'farm_name': '测试', 'fold_id': '123', 'fold_name': '测试', 'device_number': '123', 'equipment_number': '125', 'animal_number': '125', 'drink_quantity': 100, 'type': 'goat', 'drink_time': datetime.datetime(2020, 3, 20, 15, 46, 8, 953000)} {'farm_id': '125', 'farm_name': '测试', 'fold_id': '125', 'fold_name': '测试', 'device_number': '125', 'equipment_number': '125', 'animal_number': '125', 'drink_quantity': 100, 'type': 'goat', 'drink_time': datetime.datetime(2020, 3, 20, 17, 0, 34, 398000)}
最终的结果
def aggregate_many(self): # 获取所有牧场下，饮水次数小于2的羊的equipment_number match_dict = {"$match":{"farm_id":{"$in":["123","125"]}}} project_dict = {"$project":{"_id":0}} group_dict = {"$group":{"_id":{"equipment_number":"$equipment_number","farm_id":"$farm_id"},"total_count":{"$sum":1}}} match_dict_1 = {"$match":{"total_count":{"$lt":2}}} result = self.db["sheep_water_intake"].aggregate([match_dict,project_dict,group_dict,match_dict_1]) for i in result: print(i) {'_id': {'equipment_number': '125', 'farm_id': '125'}, 'total_count': 1} {'_id': {'equipment_number': '125', 'farm_id': '123'}, 'total_count': 1}
$lookup 多表联查
test2
{ "_id" : ObjectId("5e7c756b2a89d7c2fc178f57"), "brand" : "惠普公司", "address" : "美国" }
test1
{ "_id" : ObjectId("5e7c753b2a89d7c2fc178f38"), "name" : "暗夜精灵笔记本电脑", "brand_id" : "5e7c756b2a89d7c2fc178f57",
} { "_id" : ObjectId("5e7c75d02a89d7c2fc178fb0"), "name" : "暗夜精灵2", "brand_id" : "5e7c756b2a89d7c2fc178f57", "price" : 5600 }
通过test2的_id获取所有brand_id为_id的电脑名称和价格
from pymongo import MongoClient class PyMongoTest(object): def __init__(self): self.host = "xx" self.port = xx self.username = "xx" self.password = "xx" self.database = "xx" self.client = MongoClient(host=self.host,port=self.port) self.db = self.client[self.database] self.connect_result = False if self.username and self.password: self.connect_result = self.db.authenticate(self.username,self.password) def aggregate_two_collection(self): collection_one = "test2" collection_two = "test1" lookup_dict = {"$lookup":{"from":collection_two,"localField":"_id","foreignField":"brand_id","as":"brand_product"}} result = self.db[collection_one].aggregate([lookup_dict]) for r in result: print(r) p = PyMongoTest() p.aggregate_two_collection() # 结果 {'_id': ObjectId('5e7c756b2a89d7c2fc178f57'), 'brand': '惠普公司', 'address': '美国', 'brand_product': []}
将test2改为
{ "_id" : ObjectId("5e7c756b2a89d7c2fc178f57"), "brand" : "惠普公司", "address" : "美国", "oid" : "5e7c756b2a89d7c2fc178f57" }
def aggregate_two_collection(self): collection_one = "test2" collection_two = "test1" lookup_dict = {"$lookup":{"from":collection_two,"localField":"oid","foreignField":"brand_id","as":"brand_product"}} result = self.db[collection_one].aggregate([lookup_dict]) for r in result: print(r) # 结果 {'_id': ObjectId('5e7c756b2a89d7c2fc178f57'), 'brand': '惠普公司', 'address': '美国', 'oid': '5e7c756b2a89d7c2fc178f57', 'brand_product': [{'_id': ObjectId('5e7c753b2a89d7c2fc178f38'), 'name': '暗夜精灵笔记本电脑', 'brand_id': '5e7c756b2a89d7c2fc178f57'}, {'_id': ObjectId('5e7c75d02a89d7c2fc178fb0'), 'name': '暗夜精灵2', 'brand_id': '5e7c756b2a89d7c2fc178f57', 'price': 5600}]}
可以看出：from是要关联的集合名，localField是关联的字段，foreignField也是关联的字段，但是必须注意，这两个字段的类型必须相同，要不就拿不出数据，as就是关联后，列表的名称
修改test1
/* 1 */ { "_id" : ObjectId("5e7c753b2a89d7c2fc178f38"), "name" : "暗夜精灵笔记本电脑", "brand_id" : "5e7c756b2a89d7c2fc178f57F",
} /* 2 */ { "_id" : ObjectId("5e7c75d02a89d7c2fc178fb0"), "name" : "暗夜精灵2", "brand_id" : "5e7c756b2a89d7c2fc178f57D", "price" : 5600 }
{'_id': ObjectId('5e7c756b2a89d7c2fc178f57'), 'brand': '惠普公司', 'address': '美国', 'oid': '5e7c756b2a89d7c2fc178f57', 'brand_product': []}
说明 localField是关联的字段，foreignField也是关联的字段，值也必须相同。
将test1两个数据的brand_id修改成和test1的oid值一样，做下面测试
关联后，只想输出部分字段，怎么办？
def aggregate_two_collection(self): collection_one = "test2" collection_two = "test1" lookup_dict = {"$lookup":{"from":collection_two,"localField":"oid","foreignField":"brand_id","as":"brand_product"}} project_dict = {"$project":{"_id":0,"oid":0}} result = self.db[collection_one].aggregate([lookup_dict,project_dict]) for r in result: print(r) # 结果 {'brand': '惠普公司', 'address': '美国', 'brand_product': [{'_id': ObjectId('5e7c753b2a89d7c2fc178f38'), 'name': '暗夜精灵笔记本电脑', 'brand_id': '5e7c756b2a89d7c2fc178f57'}, {'_id': ObjectId('5e7c75d02a89d7c2fc178fb0'), 'name': '暗夜精灵2', 'brand_id': '5e7c756b2a89d7c2fc178f57', 'price': 5600}]}
project只影响原表的字段输出，不影响要关联表的字段，如果需要影响要关联表的字段输出呢？
更改test1数据为
/* 1 */ { "_id" : ObjectId("5e7c753b2a89d7c2fc178f38"), "name" : "暗夜精灵笔记本电脑", "brand_id" : "5e7c756b2a89d7c2fc178f57", "price" : 5000 } /* 2 */ { "_id" : ObjectId("5e7c75d02a89d7c2fc178fb0"), "name" : "暗夜精灵2", "brand_id" : "5e7c756b2a89d7c2fc178f57", "price" : 5600 }
def aggregate_two_collection(self): collection_one = "test2" collection_two = "test1" lookup_dict = {"$lookup":{"from":collection_two,"localField":"oid","foreignField":"brand_id","as":"brand_product"}} project_dict = {"$project":{"_id":0,"brand_product._id":0}} result = self.db[collection_one].aggregate([lookup_dict,project_dict]) for r in result: print(r) #结果 {'brand': '惠普公司', 'address': '美国', 'oid': '5e7c756b2a89d7c2fc178f57', 'brand_product': [{'name': '暗夜精灵笔记本电脑', 'brand_id': '5e7c756b2a89d7c2fc178f57', 'price': 5000}, {'name': '暗夜精灵2', 'brand_id': '5e7c756b2a89d7c2fc178f57', 'price': 5600}]}
计算两款电脑的平均值？
def aggregate_two_collection(self): collection_one = "test2" collection_two = "test1" lookup_dict = {"$lookup":{"from":collection_two,"localField":"oid","foreignField":"brand_id","as":"brand_product"}} project_dict = {"$project":{"_id":0,"brand_product._id":0}} unwind_dict = {"$unwind":"$brand_product"} group_dict = {"$group":{"_id":{"oid":"$oid"},"avg_price":{"$avg":"$brand_product.price"}}} result = self.db[collection_one].aggregate([lookup_dict,project_dict,unwind_dict,group_dict]) for r in result: print(r) # 结果 {'_id': {'oid': '5e7c756b2a89d7c2fc178f57'}, 'avg_price': 5300.0}
$substr 切割字符串操作
/* 1 */ { "_id" : ObjectId("5e7dc3322a89d7c2fc18605d"), "animal_number" : "1001", "status" : "0", "time" : ISODate("2020-03-01T23:00:00.000Z") } /* 2 */ { "_id" : ObjectId("5e7dc3462a89d7c2fc18606e"), "animal_number" : "1001", "status" : "1", "time" : ISODate("2020-03-01T12:00:00.000Z") } /* 3 */ { "_id" : ObjectId("5e7dc35d2a89d7c2fc186093"), "animal_number" : "1001", "status" : "0", "time" : ISODate("2020-03-02T15:00:00.000Z") } /* 4 */ { "_id" : ObjectId("5e7dc3702a89d7c2fc1860a4"), "animal_number" : "1001", "status" : "1", "time" : ISODate("2020-03-02T22:33:00.000Z") } /* 5 */ { "_id" : ObjectId("5e7dc3912a89d7c2fc1860c3"), "animal_number" : "1001", "status" : "0", "time" : ISODate("2020-03-03T21:39:00.000Z") } /* 6 */ { "_id" : ObjectId("5e7dc39e2a89d7c2fc1860ce"), "animal_number" : "1001", "status" : "1", "time" : ISODate("2020-03-04T23:00:00.000Z") }
获取每天status为0的次数，和status为1的次数
def aggregate(self): match_dict = {"$match":{"animal_number":"1001"}} project = {"$project":{"_id":0,"animal_number":"$animal_number","status":"$status","time":{"$substr":["$time",0,10]}}} result = self.db["test2"].aggregate([match_dict,project]) for info in result: print(info) # 结果 {'animal_number': '1001', 'status': '0', 'time': '2020-03-01'} {'animal_number': '1001', 'status': '1', 'time': '2020-03-01'} {'animal_number': '1001', 'status': '0', 'time': '2020-03-02'} {'animal_number': '1001', 'status': '1', 'time': '2020-03-02'} {'animal_number': '1001', 'status': '0', 'time': '2020-03-03'} {'animal_number': '1001', 'status': '1', 'time': '2020-03-04'} project里面使用 "status":1和"status":"$status"表示的含义一样，均表示需要展示 $substr:["$需要切割字段的名字"，起始位置，终止位置]
def aggregate(self): match_dict = {"$match":{"animal_number":"1001"}} project = {"$project":{"_id":0,"animal_number":"$animal_number","status":"$status","time":{"$substr":["$time",0,10]}}} group_dict = {"$group":{"_id":{"time":"$time","status":"$status"},"every_status_every_day_count":{"$sum":1}}} result = self.db["test2"].aggregate([match_dict,project,group_dict]) for info in result: print(info) # 结果 {'_id': {'time': '2020-03-03', 'status': '0'}, 'every_status_every_day_count': 1} {'_id': {'time': '2020-03-04', 'status': '1'}, 'every_status_every_day_count': 1} {'_id': {'time': '2020-03-01', 'status': '1'}, 'every_status_every_day_count': 1} {'_id': {'time': '2020-03-01', 'status': '0'}, 'every_status_every_day_count': 1} {'_id': {'time': '2020-03-02', 'status': '0'}, 'every_status_every_day_count': 1} {'_id': {'time': '2020-03-02', 'status': '1'}, 'every_status_every_day_count': 1}
# 项目中碰到的一次关联查询记录，本地字段为string，被关联的字段为ObjectId
role表
{ "_id" : ObjectId("60c374a466548e27b554619e"), "name" : "admin", "alist" : [ "60c374e166548e27b554624e", "60c374ed66548e27b5546270" ] } /* 2 */ { "_id" : ObjectId("60c374bc66548e27b55461dd"), "name" : "auth", "alist" : [ "60c374e166548e27b554624e", "60c374ed66548e27b5546270" ] }
authority表
/* 1 */ { "_id" : ObjectId("60c374e166548e27b554624e"), "name" : "book" } /* 2 */ { "_id" : ObjectId("60c374ed66548e27b5546270"), "name" : "log" }
要求：查询所有role的所有权限
def test(): match = {"$match": {"_id": {"$ne": ""}}} project = {"$project": {"_id": 0, "name": 1, "alist": 1}} # 拆分列表 uwind = {"$unwind": "$alist"} # 增加字段，将str转换为ObjectId, 曲线救国 str_objid = {"$addFields": {"oid": {"$convert": {"input": "$alist", "to": "objectId"}}}} # 关联查询：本地字段和被关联字段的类型必须一致才可以。 lookup = {"$lookup": {"from": "authority", "localField": "oid", "foreignField": "_id", "as": "infos"}} result = conn["test"]["role"].aggregate([match, project, uwind, str_objid, lookup]) for info in result: print(info) # {'name': 'admin', 'alist': '60c374e166548e27b554624e', 'oid': ObjectId('60c374e166548e27b554624e'), 'infos': [{'_id': ObjectId('60c374e166548e27b554624e'), 'name': 'book'}]} # {'name': 'admin', 'alist': '60c374ed66548e27b5546270', 'oid': ObjectId('60c374ed66548e27b5546270'), 'infos': [{'_id': ObjectId('60c374ed66548e27b5546270'), 'name': 'log'}]} # {'name': 'auth', 'alist': '60c374e166548e27b554624e', 'oid': ObjectId('60c374e166548e27b554624e'), 'infos': [{'_id': ObjectId('60c374e166548e27b554624e'), 'name': 'book'}]} # {'name': 'auth', 'alist': '60c374ed66548e27b5546270', 'oid': ObjectId('60c374ed66548e27b5546270'), 'infos': [{'_id': ObjectId('60c374ed66548e27b5546270'), 'name': 'log'}]} test()
虽然查出来了，但明显感觉到，执行效率比较低，总结出来，造成这种问题的原因是表设计的不合理。
#