mongodb聚合操作$使用心得
$操作符使用
$操作符一般用作
1. $addField 增加一个字段
使用场景一：查询的结果中增加字段
数据类型如下
{
"_id" : ObjectId("610cd5a0702aac3e62a77a13"),
"name" : "zhang",
"intro" : "a"
}
/* 2 */
{
"_id" : ObjectId("610cd5a4702aac3e62a77a14"),
"name" : "zhang",
"intro" : "b"
}
/* 3 */
{
"_id" : ObjectId("610cd5a6702aac3e62a77a15"),
"name" : "zhang",
"intro" : "b"
}
demo1：返回的结果中增加一个字段，保存执行的值
match_dict = {"$match":{"name": "zhang"}}
add_dict = {"$addFields": {"intro_list":"$intro"}}
ret = db["test"].aggregate([match_dict,add_dict])
print(list(ret))
# 结果
[{‘_id‘: ObjectId(‘610cd5a0702aac3e62a77a13‘), ‘name‘: ‘zhang‘, ‘intro‘: ‘a‘, ‘intro_list‘: ‘a‘}, {‘_id‘: ObjectId(‘610cd5a4702aac3e62a77a14‘), ‘name‘: ‘zhang‘, ‘intro‘: ‘b‘, ‘intro_list‘: ‘b‘}, {‘_id‘: ObjectId(‘610cd5a6702aac3e62a77a15‘), ‘name‘: ‘zhang‘, ‘intro‘: ‘b‘, ‘intro_list‘: ‘b‘}]
demo2：将一个值类型转换后，保存到增加的字段
match_dict = {"$match": {"name": "zhang"}}
# 将查询结果的值进行转换，保存到一个指定的字段中，多用于lookup时候。
add_dict = {"$addFields": {"str_id": {"$toString":"$_id"}}}
ret = db["test"].aggregate([match_dict, add_dict])
# 结果
[{‘_id‘: ObjectId(‘610cd5a0702aac3e62a77a13‘), ‘name‘: ‘zhang‘, ‘intro‘: ‘a‘, ‘str_id‘: ‘610cd5a0702aac3e62a77a13‘}, {‘_id‘: ObjectId(‘610cd5a4702aac3e62a77a14‘), ‘name‘: ‘zhang‘, ‘intro‘: ‘b‘, ‘str_id‘: ‘610cd5a4702aac3e62a77a14‘}, {‘_id‘: ObjectId(‘610cd5a6702aac3e62a77a15‘), ‘name‘: ‘zhang‘, ‘intro‘: ‘b‘, ‘str_id‘: ‘610cd5a6702aac3e62a77a15‘}]
ObjectId转换为str类型
# 以前都是查出结果后，循环str将id从Object转换为str，现在可以一步到位
add_dict = {"$addFields": {"_id": {"$toString":"$_id"}}}
# 结果为
[{‘_id‘: ‘610cd5a0702aac3e62a77a13‘, ‘name‘: ‘zhang‘, ‘intro‘: ‘a‘}, {‘_id‘: ‘610cd5a4702aac3e62a77a14‘, ‘name‘: ‘zhang‘, ‘intro‘: ‘b‘}, {‘_id‘: ‘610cd5a6702aac3e62a77a15‘, ‘name‘: ‘zhang‘, ‘intro‘: ‘b‘}]
数据类型如下
/* 1 */
{
"_id" : ObjectId("610cd5a0702aac3e62a77a13"),
"name" : "china",
"city_gdp" : [
{
"city" : "beijing",
"gdp" : 100
},
{
"city" : "shanghai",
"gdp" : 200
},
{
"city" : "tianjin",
"gdp" : 300
},
{
"city" : "chongqing",
"gdp" : 400
}
]
}
demo3：求gdp的总和和平均值
match_dict = {"$match": {"name": "china"}}
# 增加两个字段:保存总和和平均值
add_dict = {"$addFields": {"total": {"$sum":"$city_gdp.gdp"},"avg":{"$avg":"$city_gdp.gdp"}}}
ret = db["test"].aggregate([match_dict, add_dict])
print(list(ret))
# 结果
[{‘_id‘: ObjectId(‘610cd5a0702aac3e62a77a13‘), ‘name‘: ‘china‘, ‘city_gdp‘: [{‘city‘: ‘beijing‘, ‘gdp‘: 100}, {‘city‘: ‘shanghai‘, ‘gdp‘: 200}, {‘city‘: ‘tianjin‘, ‘gdp‘: 300}, {‘city‘: ‘chongqing‘, ‘gdp‘: 400}], ‘total‘: 1000, ‘avg‘: 250.0}]
使用场景二：给原始数据增加字段---类似于修改表结构
数据结构如下
/* 2 */
{
"_id" : ObjectId("610e97f48e28e168e81a2e62"),
"company" : "apple",
"computer" : 15000,
"iphone" : 6000
}
/* 3 */
{
"_id" : ObjectId("610e97fe8e28e168e81a2e87"),
"company" : "huawei",
"computer" : 10000,
"iphone" : 8000
}
每条数据增加一个字段，名字为pad，默认初始值为3000
update_dict = {"$addFields": {"pad": 3000}}
# 注意：必须使用列表包裹更新条件，否则报错
ret = db["test"].update_many({}, [update_dict])
# 结果
/* 2 */
{
"_id" : ObjectId("610e97f48e28e168e81a2e62"),
"company" : "apple",
"computer" : 15000,
"iphone" : 6000,
"pad" : 3000
}
/* 3 */
{
"_id" : ObjectId("610e97fe8e28e168e81a2e87"),
"company" : "huawei",
"computer" : 10000,
"iphone" : 8000,
"pad" : 3000
}
2.$addToSet
使用方式一：作为列表修改器，一般和update方法搭配使用。
数据类型如下
{
"_id" : ObjectId("610e91708e28e168e81a1c2f"),
"name" : "xian",
"foods" : [
"肉夹馍"
]
}
给foods列表中添加数据，达到去重效果
ret = db["test"].update_one(filter={"name": "xian"}, update={"$addToSet": {"foods": "糖醋面皮"}})
# 结果---插入相同的值会直接去重，但是上面的写法缺点是每次只能添加一个元素
{
"_id" : ObjectId("610e91708e28e168e81a1c2f"),
"name" : "xian",
"foods" : [
"肉夹馍",
"糖醋面皮"
]
}
# 添加一组元素 --- 配合$each使用
ret = db["test"].update_one(filter={"name": "xian"}, update={"$addToSet": {"foods": {"$each":["麻辣烫夹馍","东关烧烤"]}}})
# 结果
{
"_id" : ObjectId("610e91708e28e168e81a1c2f"),
"name" : "xian",
"foods" : [
"肉夹馍",
"糖醋面皮",
"麻辣烫夹馍",
"东关烧烤"
]
}
使用方式二：去重的话，一般和group搭配使用
数据类型如下
{
"_id" : ObjectId("610e8d0d8e28e168e81a1009"),
"name" : "zhang",
"intro" : "a"
}
/* 2 */
{
"_id" : ObjectId("610e8d148e28e168e81a1023"),
"name" : "zhang",
"intro" : "a"
}
/* 3 */
{
"_id" : ObjectId("610e8d1b8e28e168e81a103b"),
"name" : "zhang",
"intro" : "b"
}
查询intro值并去重
# 需要说明的是，$addToSet去重一般会和分组group一起使用
match_dict = {"$match": {"name": "zhang"}}
group_dict = {"$group": {"_id": None, "intro_list": {"$addToSet": "$intro"}}}
# 结果
[{‘_id‘: None, ‘intro_list‘: [‘b‘, ‘a‘]}]
3. $and
作用：逻辑与
4. $avg
作用：求平均
5. $add
作用：数学运算
数据类型如下
/* 2 */
{
"_id" : ObjectId("610e97f48e28e168e81a2e62"),
"company" : "apple",
"computer" : 15000,
"iphone" : 6000
}
/* 3 */
{
"_id" : ObjectId("610e97fe8e28e168e81a2e87"),
"company" : "huawei",
"computer" : 10000,
"iphone" : 8000
}
match_dict = {"$match": {"$or": [{"company": "apple"}, {"company": "huawei"}]}}
# project也可以增加字段，$add将不同字段的值进行相加
project_dict = {"$project": {"company": 1, "computer": 1, "iphone": 1, "total": {"$add": ["$computer", "$iphone"]}}}
ret = db["test"].aggregate([match_dict, project_dict])
print(list(ret))
# 结果
[{‘_id‘: ObjectId(‘610e97f48e28e168e81a2e62‘), ‘company‘: ‘apple‘, ‘computer‘: 15000, ‘iphone‘: 6000, ‘total‘: 21000}, {‘_id‘: ObjectId(‘610e97fe8e28e168e81a2e87‘), ‘company‘: ‘huawei‘, ‘computer‘: 10000, ‘iphone‘: 8000, ‘total‘: 18000}]
6. $all
作用：用于匹配数组元素
7. $abs
作用：求绝对值
数据
{
"_id" : ObjectId("610ea57f8e28e168e81a5370"),
"name" : "haerbin",
"temp" : -10
}
返回绝对值后的数据
match_dict = {"$match": {"name": "haerbin"}}
# 利用project可以增加字段的性质，替换_id的值
project = {"$project": {"name": 1, "temp": 1, "abs_temp": {"$abs": "$temp"}, "_id": {"$toString": "$_id"}}}
ret = db["test"].aggregate([match_dict, project])
# 结果
[{‘name‘: ‘haerbin‘, ‘temp‘: -10, ‘abs_temp‘: 10, ‘_id‘: ‘610ea57f8e28e168e81a5370‘}]
同样适用于find
# 完美解决find查询后处理_id类型转换的麻烦
ret = db["test"].find_one({"name": "haerbin"},{"name": 1, "temp": 1, "abs_temp": {"$abs": "$temp"}, "_id": {"$toString": "$_id"}})
# 结果：
{‘name‘: ‘haerbin‘, ‘temp‘: -10, ‘abs_temp‘: 10, ‘_id‘: ‘610ea57f8e28e168e81a5370‘}
8. $arrayElemAt
作用：返回数组内指定下标的元素
数据类型如下：
{
"_id" : ObjectId("610e91708e28e168e81a1c2f"),
"name" : "xian",
"foods" : [
"肉夹馍",
"糖醋面皮",
"麻辣烫夹馍",
"东关烧烤"
],
"pad" : 3000
}
返回foods下标为0的元素
match_dict = {"$match": {"name": "xian"}}
# 取foods列表中的第一个元素
project = {"$project": {"foods": 1, "pad": 1, "_id": {"$toString": "$_id"}, "foods_num1": {"$arrayElemAt":["$foods", 0]}}}
ret = db["test"].aggregate([match_dict, project])
# 结果
[{‘foods‘: [‘肉夹馍‘, ‘糖醋面皮‘, ‘麻辣烫夹馍‘, ‘东关烧烤‘], ‘pad‘: 3000, ‘_id‘: ‘610e91708e28e168e81a1c2f‘, ‘foods_num1‘: ‘肉夹馍‘}]
9. $arrayToObject
作用：将数组内的key-value元素转换为对象
使用场景一：将数据的字段A和字段B的值组成字典格式返回
数据类型如下
{
"_id" : ObjectId("610e97f48e28e168e81a2e62"),
"company" : "apple",
"computer" : 15000,
"iphone" : 6000,
"pad" : 3000
}
/* 3 */
{
"_id" : ObjectId("610e97fe8e28e168e81a2e87"),
"company" : "huawei",
"computer" : 10000,
"iphone" : 8000,
"pad" : 3000
}
输出，{"hawei":8000}，{"apple":"6000"}
match_dict = {"$match": {"$or": [{"company": "apple"}, {"company": "huawei"}]}}
# push不能和project搭配使用。
group_dict = {"$group": {"_id": None, "list": {"$push": {"k": "$company", "v": "$pad"}}}}
# project = {"$project": {"computer": 1, "iphone": 1, "_id": {"$toString": "$_id"}, "pad": 1,"list": {"$push": {"k": "$company", "v": "$pad"}}}}
ret = db["test"].aggregate([match_dict, group_dict])
# 结果
[{‘_id‘: None, ‘list‘: [{‘k‘: ‘apple‘, ‘v‘: 3000}, {‘k‘: ‘huawei‘, ‘v‘: 3000}]}]
match_dict = {"$match": {"$or": [{"company": "apple"}, {"company": "huawei"}]}}
group_dict = {"$group": {"_id": None, "list": {"$push": {"k": "$company", "v": "$iphone"}}}}
project = {"$project": {"return_dict": {"$arrayToObject": "$list"}}}
ret = db["test"].aggregate([match_dict, group_dict, project])
# 结果
[{‘_id‘: None, ‘return_dict‘: {‘apple‘: 6000, ‘huawei‘: 8000}}]
arrayToObject能够识别的类型如下
类型一：
[ [ “item”, “abc123”], [ “qty”, 25 ] ]
类型二：
[ { “k”: “item”, “v”: “abc123”}, { “k”: “qty”, “v”: 25 } ]
原文：https://www.cnblogs.com/meloncodezhang/p/15113079.html