自己关于使用MongoTemplate的一点总结。
1. 创建集合
db.material.insert([
{ "_id" : "M00001", "name" : "多乐士油漆", "price" : 80},
{ "_id" : "M00002", "name" : "马可波罗瓷砖", "price" : 20 },
{ "_id" : "M00003", "name": "海螺水泥", "price": 150 },
{ "_id" : "M00004", "name" : "大金空调", "price" : 500},
{ "_id" : "M00005", "name" : "公牛插座", "price" : 25},
{ "_id" : "M00006", "name" : "慕思寝具", "price" : 50},
{ "_id" : "M00007", "name" : "踏踏木门", "price" : 100}
])
db.merchant.insert([
{ "_id" : "T00001", "name" : "张三建材", "materialCode" : ["M00001", "M00002", "M00003"]},
{ "_id" : "T00002", "name" : "李四建材", "materialCode" : ["M00004", "M00002", "M00005"]},
{ "_id" : "T00003", "name" : "王五建材", "materialCode" : ["M00006", "M00002", "M00007"]}
])
2. 聚合分组、求和、平均值查询
2.1 MongoTemplate查询
package com.hollysys.tn.controller;
import io.swagger.annotations.Api;
import lombok.Data;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.mapping.Document;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping(value = "/test")
@Api(tags = "测试")
public class TestController {
@Autowired
private MongoTemplate mongo;
@Data
@Document("material")
class Material{
private String _id;
private String name;
private Integer price;
}
@Data
@Document("merchant")
class Merchant{
private String _id;
private String name;
private List<String> materialCode;
}
@GetMapping("/test")
public List<Map> test() {
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.unwind("materialCode"),
Aggregation.lookup("material", "materialCode", "_id", "merchant_as"),
Aggregation.unwind("merchant_as"),
Aggregation.project("_id","name","materialCode")
.and("merchant_as.name").as("materialName")
.and("merchant_as.price").as("price"),
Aggregation.group("materialCode", "materialName")
.count().as("count")
.sum("price").as("sum")
.avg("price").as("avg"),
Aggregation.match(Criteria
.where("count").gte(3))
);
List<Map> result = mongo.aggregate(aggregation,"merchant",Map.class).getMappedResults();
System.out.println(result);
return result;
}
}
2.2 Mongo中的查询
db.merchant.aggregate([
{ "$unwind" : "$materialCode" },
{
"$lookup" : {
"from" : "material",
"localField" : "materialCode",
"foreignField" : "_id",
"as" : "merchant_as"
}
},
{ "$unwind" : "$merchant_as" },
{
"$project" : {
"_id" : 1,
"name" : 1,
"materialCode" : 1,
"materialName" : "$merchant_as.name",
"price" : "$merchant_as.price"
}
},
{
"$group" : {
"_id" : { "materialCode" : "$materialCode", "materialName" : "$materialName" },
"count" : { "$sum" : 1 },
"sum" : { "$sum" : "$price" },
"avg" : { "$avg" : "$price" }
}
},
{
"$match" : {
"count" : { "$gte" : 3 }
}
}
])
2.3 使用postman的查询结果
[
{
"materialCode": "M00002",
"materialName": "马可波罗瓷砖",
"count": 3,
"sum": 60.0,
"avg": 20.0
}
]
3. 代码步骤解析
- 1.因为主表中的materialCode是一个数组,所以先将其打散,用到了
$unwind
,具体可以查查文档,打散后的格式是这样的:
- 2.用打散后的数据使用
$lookup
去关联表material
得到每一个物料的价格和名称,多表关联可以参考我另一篇文章关于Mongodb + java + 多表关联查询(MongoTemplate),结果如下:
- 3.因为
merchant_as
是一个数组,并且里面也有_id
这个字段,所以需要使用$lookup
打散并且使用$project
取部分需要的字段,然后给到下游,给到下游的数据是这样的:
- 4.然后对数据分组求和,
_id
是用来分组的字段,将分组的结果给到下游,结果如下:
- 5.对累计
count
字段做筛选,选出大于等于三个厂家都卖的建材,结果如下:
4. 小结
主要使用到mongo的聚合函数,理解了mongo中数据管道这个概念,理解起来就比较简单,所以建议使用MongoTemplate查询的时候,先在mongo中写一遍。所以如果不会mongo语法的童鞋,也建议先学学mongo。以上仅是我的一点经验、本人也是初学者,文章写得有问题还希望指出来,大家一起学习进步!