MongoDB - 聚合操作符 $eq、$gte、$in、$sum、$avg

1. $eq

$eq比较两个值并返回：true （当值相等时）|false（当值不相等时）

{ $eq: [ <expression1>, <expression2> ] }

构造测试数据：

db.inventory.drop()db.inventory.insertMany({{ "_id" : 1, "item" : "abc1", description: "product 1", qty: 300 },{ "_id" : 2, "item" : "abc2", description: "product 2", qty: 200 },{ "_id" : 3, "item" : "xyz1", description: "product 3", qty: 250 },{ "_id" : 4, "item" : "VWZ1", description: "product 4", qty: 300 },{ "_id" : 5, "item" : "VWZ2", description: "product 5", qty: 180 }
})

以下操作使用 $eq 操作符来判断 qty 是否等于 250：

db.inventory.aggregate([{$project:{item: 1,qty: 1,qtyEq250: { $eq: [ "$qty", 250 ] },_id: 0}}]
)

// 1
{"item": "abc1","qty": 300,"qtyEq250": false
}// 2
{"item": "abc2","qty": 200,"qtyEq250": false
}// 3
{"item": "xyz1","qty": 250,"qtyEq250": true
}// 4
{"item": "VWZ1","qty": 300,"qtyEq250": false
}// 5
{"item": "VWZ2","qty": 180,"qtyEq250": false
}

//输入文档实体类
@Data
@Document(collection = "inventory")
public class Inventory {private int _id;private String item;private String description;private int qty;
}//输出文档实体类
@Data
public class AggregationResult {private String item;private String qty;private Boolean qtyEq250;
}//执行聚合操作
@SpringBootTest
@RunWith(SpringRunner.class)
public class BeanLoadServiceTest {@Autowiredprivate MongoTemplate mongoTemplate;@Testpublic void queryTest() {// $project 聚合阶段ProjectionOperation projectionOperation = Aggregation.project("item", "qty").andExclude("_id").andExpression("qty==250").as("qtyEq250");Aggregation aggregation = Aggregation.newAggregation(projectionOperation);AggregationResults<AggregationResult> aggregate = mongoTemplate.aggregate(aggregation, Inventory.class, AggregationResult.class);List<AggregationResult> mappedResults = aggregate.getMappedResults();mappedResults.forEach(System.out::println);//AggregationResult(item=abc1, qty=300.0, qtyEq250=false)//AggregationResult(item=abc2, qty=200.0, qtyEq250=false)//AggregationResult(item=xyz1, qty=250.0, qtyEq250=true)//AggregationResult(item=VWZ1, qty=300.0, qtyEq250=false)//AggregationResult(item=VWZ2, qty=180.0, qtyEq250=false)}
}

2. $gte

$gt 比较两个值并返回：true（当第一个值大于第二个值时），false（当第一个值小于或者等于第二个值时）

{ $gt: [ <expression1>, <expression2> ] }

构造测试数据：

db.inventory.drop()db.inventory.insertMany([{ "_id" : 1, "item" : "abc1", description: "product 1", qty: 300 },{ "_id" : 2, "item" : "abc2", description: "product 2", qty: 200 },{ "_id" : 3, "item" : "xyz1", description: "product 3", qty: 250 },{ "_id" : 4, "item" : "VWZ1", description: "product 4", qty: 300 },{ "_id" : 5, "item" : "VWZ2", description: "product 5", qty: 180 }
])

以下操作使用 $gt 操作符来判断 qty 是否大于 250：

db.inventory.aggregate([{$project:{_id: 0,item: 1,qty: 1,qtyGt250: { $gt: [ "$qty", 250 ] }}}]
)

//输入文档实体类
@Data
@Document(collection = "inventory")
public class Inventory {private int _id;private String item;private String description;private int qty;
}//输出文档实体类
@Data
public class AggregationResult {private String item;private String qty;private Boolean qtyGt250;
}//执行聚合操作
@SpringBootTest
@RunWith(SpringRunner.class)
public class BeanLoadServiceTest {@Autowiredprivate MongoTemplate mongoTemplate;@Testpublic void queryTest() {//$project聚合阶段ProjectionOperation projectionOperation = Aggregation.project("item", "qty").andExclude("_id").andExpression("qty>250").as("qtyGt250");//组合聚合阶段Aggregation aggregation = Aggregation.newAggregation(projectionOperation);//执行聚合阶段AggregationResults<AggregationResult> aggregationResults = mongoTemplate.aggregate(aggregation, Inventory.class, AggregationResult.class);List<AggregationResult> mappedResults = aggregationResults.getMappedResults();//打印mappedResults.forEach(System.out::println);//AggregationResult(item=abc1, qty=300.0, qtyGt250=true)//AggregationResult(item=abc2, qty=200.0, qtyGt250=false)//AggregationResult(item=xyz1, qty=250.0, qtyGt250=false)//AggregationResult(item=VWZ1, qty=300.0, qtyGt250=true)//AggregationResult(item=VWZ2, qty=180.0, qtyGt250=false)}
}

3. $in

$in 返回一个布尔值，它可表示指定的值是否在数组中。

{ $in: [ <expression>, <array expression> ] }

例子	结果
{ $in: [ 2, [ 1, 2, 3 ] ] }	true
{ $in: [ "abc", [ "xyz", "abc" ] ] }	true
{ $in: [ "xy", [ "xyz", "abc" ] ] }	false
{ $in: [ [ "a" ], [ "a" ] ] }	false
{ $in: [ [ "a" ], [ [ "a" ] ] ] }	true
{ $in: [ /^a/, [ "a" ] ] }	false
{ $in: [ /^a/, [ /^a/ ] ] }	true

构造测试数据：

db.stores.drop()db.stores.insertMany([{ "_id" : 1, "location" : "上海", "fruits" : [ "apples", "oranges", "bananas" ] },{ "_id" : 2, "location" : "北京", "fruits" : [ "bananas", "pears", "grapes" ] },{ "_id" : 3, "location" : "南京", "fruits" : [ "cantaloupes", "watermelons", "apples" ] }
])

以下聚合操作查看每个文档中的 fruits 数组，并确定是否存在字符串 bananas。

db.stores.aggregate([// $project聚合阶段：将文档中的location字段重命名为 store location字段{$project: {"store_location" : "$location","has_bananas" : {$in: [ "bananas", "$fruits" ]}}}
])

// 1
{"_id": 1,"store_location": "上海","has_bananas": true
}// 2
{"_id": 2,"store_location": "北京","has_bananas": true
}// 3
{"_id": 3,"store_location": "南京","has_bananas": false
}

4. $sum

当用作累加器时，$sum 的语法如下：

{ $sum: <expression> }

构造测试数据：

db.students.drop()db.students.insertMany([{ "_id": 1, "quizzes": [ 10, 6, 7 ], "labs": [ 5, 8 ], "finalScore": 80, "midtermScore": 75 },{ "_id": 2, "quizzes": [ 9, 10 ], "labs": [ 8, 8 ], "finalScore": 95, "midtermScore": 80 },{ "_id": 3, "quizzes": [ 4, 5, 5 ], "labs": [ 6, 5 ], "finalScore": 78, "midtermScore": 70 }
])

计算测验总分数、实验总分数以及期末和期中考试的总分数：

db.students.aggregate([{$project: {quizTotal: { $sum: "$quizzes"},labTotal: { $sum: "$labs" },examTotal: { $sum: [ "$finalScore", "$midtermScore" ] }}}
])

// 1
{"_id": 1,"quizTotal": 23,"labTotal": 13,"examTotal": 155
}// 2
{"_id": 2,"quizTotal": 19,"labTotal": 16,"examTotal": 175
}// 3
{"_id": 3,"quizTotal": 14,"labTotal": 11,"examTotal": 148
}

// 输入文档实体类
@Data
@Document(collection = "students")
public class Student {@Idprivate int id;private List<Integer> quizzes;private List<Integer> labs;private int finalScore;private int midtermScore;
}// 输出文档实体类
@Data
public class AggregationResult {private int _id;private int quizTotal;private int labTotal;private int examTotal;
}// 执行聚合操作
@SpringBootTest
@RunWith(SpringRunner.class)
public class BeanLoadServiceTest {@Autowiredprivate MongoTemplate mongoTemplate;@Testpublic void queryTest() {//$project聚合阶段ProjectionOperation project = Aggregation.project().andExpression("{$sum: '$quizzes'}").as("quizTotal").andExpression("{$sum: '$labs'}").as("labTotal").andExpression("$finalScore + $midtermScore").as("examTotal");//组合聚合阶段Aggregation aggregation = Aggregation.newAggregation(project);//执行聚合阶段AggregationResults<AggregationResult> aggregationResults = mongoTemplate.aggregate(aggregation, Student.class, AggregationResult.class);List<AggregationResult> mappedResults = aggregationResults.getMappedResults();//打印mappedResults.forEach(System.out::println);//AggregationResult(_id=1, quizTotal=23, labTotal=13, examTotal=155)//AggregationResult(_id=2, quizTotal=19, labTotal=16, examTotal=175)//AggregationResult(_id=3, quizTotal=14, labTotal=11, examTotal=148)}
}

5. $avg

db.sales.drop()db.sales.insertMany([{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2) },{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1) },{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5) },{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10) },{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10) }
])

按照 item 字段对文档进行分组，以下操作使用 $avg 累加器来计算每组的平均金额和平均数量：

db.sales.aggregate([{$group:{_id: "$item",avgAmount: { $avg: { $multiply: [ "$price", "$quantity" ] } },avgQuantity: { $avg: "$quantity" }}}]
)

// 输入文档实体类
@Data
@Document(collection = "sales")
public class Sales {@Idprivate int id;private String item;private int price;private int quantity;
}// 输出文档实体类
@Data
public class AggregationResult {private int _id;private int avgAmount;private int avgQuantity;
}// 执行聚合操作
@Test
public void queryTest() {//$group聚合阶段GroupOperation groupOperation = Aggregation.group("item").avg(ArithmeticOperators.Multiply.valueOf("price").multiplyBy("quantity")).as("avgAmount").avg("quantity").as("avgQuantity");//组合聚合阶段Aggregation aggregation = Aggregation.newAggregation(groupOperation);//执行聚合阶段AggregationResults<AggregationResult> aggregationResults = mongoTemplate.aggregate(aggregation, Sales.class, AggregationResult.class);List<AggregationResult> mappedResults = aggregationResults.getMappedResults();//打印mappedResults.forEach(System.out::println);//AggregationResult(_id=0, avgAmount=3512, avgQuantity=8)
}