mongodb（五)：聚合操作（python）
2024-10-09 09:15:52
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，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和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和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":"equipmentnumber","farmid":"equipmentnumber","farmid":"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}
最新文章
- Mac OS X 上安装 ASP.NET 5
- MongoDB的简单操作（asp.net）
- 在Abp中执行sql语句
- 揣摩实现一个ioc容器需要做的事情
- css3实现小黄人
- SVG添加链接(转载)
- 第二百二十八天 how can I 坚持
- Linux下杀僵尸进程办法
- Win7设置共享文件夹
- poj2229
- Umbraco中的ModelBuilder
- Java源代码分析与生成
- MOSS母板页制作 学习笔记（一）
- 14 java 日期处理 joda-time
- 关于RSA加密
- LA-3135 - Argus(优先队列)
- 3、Spring的AOP详解和案例
- swoole 弹幕系统
- webpack的四大核心概念
- Java学习随笔（1）--groovy爬虫