3.6之前的mongobd lookup 只能是形如
{
$lookup:
{
from: <collection to join>,
localField: <field from the input documents>,
foreignField: <field from the documents of the "from" collection>,
as: <output array field>
}
}
但是我现在的需求是除了关联字段外,关联的外表还有其他的字段需要筛选,百度后,参考这篇文章解决了问题
http://www.voidcn.com/article/p-pzacpwea-buq.html
我的查询bson
db.document.aggregate([
{
$match: {
$or:
[{
"name": "xxxx"
}, {
"no": "xxxx"
}]
}
},
{
$lookup:
{
from: "document1",
let: {
name: "$name",
code: "$no"
},
pipeline: [
{
$match:
{
$expr:
{
$or:
[
{
$eq: ["$name", "$$name"]
},
{
$eq: ["$code", "$$code"]
}
]
}
}
}
],
as: "document1"
}
},
{
$lookup:
{
from: "document2",
let: {
name: "$name",
code: "$no"
},
pipeline: [
{
$match:
{
$expr:
{
$or:
[
{
$eq: ["$name", "$$name"]
},
{
$eq: ["$code", "$$code"]
}
]
}
}
}
],
as: "document2"
}
},
{
$lookup:
{
from: "document3",
let: {
name: "$name",
code: "$no"
},
pipeline: [
{
$match:
{
$expr:
{
$or:
[
{
$eq: ["$name", "$$name"]
},
{
$eq: ["$code", "$$code"]
}
]
}
}
}
],
as: "document3"
}
},
{
$lookup:
{
from: "document4",
let: {
name: "$name",
code: "$no"
},
pipeline: [
{
$match:
{
$expr:
{
$or:
[
{
$eq: ["$name", "$$name"]
},
{
$eq: ["$code", "$$code"]
}
]
}
}
}
],
as: "document4"
}
},
{
$lookup:
{
from: "document5",
let: {
name: "$name",
code: "$no"
},
pipeline: [
{
$match:
{
$expr:
{
$or:
[
{
$eq: ["$name", "$$name"]
},
{
$eq: ["$code", "$$code"]
}
]
}
}
}
],
as: "document5"
}
},
{
$lookup:
{
from: "document6",
let: {
name: "$name",
code: "$no"
},
pipeline: [
{
$match:
{
$expr:
{
$or:
[
{
$eq: ["$name", "$$name"]
},
{
$eq: ["$code", "$$code"]
}
]
}
}
}
],
as: "document6"
}
},
{
$project: {
"total1": {
"$size": "$document1"
},
"total2": {
"$size": "$document2"
},
"total3": {
"$size": "$document3"
},
"total4": {
"$size": "$document4"
},
"total5": {
"$size": "$document5"
},
"total6": {
"$size": "$document6"
}
}
}
])
java 写法
private void numsInfoUpdate(Map<String,Object> map){
Bson filters = Aggregates.match(Filters.or(Filters.in("name",map.get("name),Filters.in("no",map.get("no))));
Bson group = Aggregates.group("$name"
,new BsonField("code",new BasicDBObject("$first","$code"))
,new BsonField("count",new BasicDBObject("$sum",1))
);
List<Bson> groupBson = new ArrayList<>();
groupBson.add(filters);
groupBson.add(group);
Bson match = Aggregates.match(Filters.or(Filters.eq("name",name),Filters.eq("no",code)));
List<Variable<String>> lets = returnLets();
List<Bson> pipelines = returnPipelines();
Bson lookup = Aggregates.lookup("document1" ,lets,pipelines,"document1");
Bson lookup1 = Aggregates.lookup("document2" ,lets,pipelines,"document2");
Bson lookup2 = Aggregates.lookup("document3" ,lets,pipelines,"document3");
Bson lookup3 = Aggregates.lookup("document4" ,lets,pipelines,"document4");
Bson lookup4 = Aggregates.lookup("document5" ,lets,pipelines,"document5");
Bson lookup5 = Aggregates.lookup("document6" ,lets,pipelines,"document6");
Bson project = Aggregates.project(Projections.fields(
Projections.include("corpName")
,new BasicDBObject("total1" ,new BasicDBObject("$size", "$document1"))
,new BasicDBObject("total2" ,new BasicDBObject("$size", "$document2"))
,new BasicDBObject("total3" ,new BasicDBObject("$size", "$document3"))
,new BasicDBObject("total4" ,new BasicDBObject("$size", "$document4"))
,new BasicDBObject("total5" ,new BasicDBObject("$size", "$document5"))
,new BasicDBObject("total6" ,new BasicDBObject("$size", "$document6"))
));
List<Bson> bsonList = new ArrayList<>();
bsonList.add(match);
bsonList.add(lookup);
bsonList.add(lookup1);
bsonList.add(lookup2);
bsonList.add(lookup3);
bsonList.add(lookup4);
bsonList.add(lookup5);
bsonList.add(project);
AggregateIterable<Document> aggregate = aggregate("document",bsonList);
}
private List<Variable<String>> returnLets() {
List<Variable<String>> lets = new ArrayList<>();
Variable<String> let1 = new Variable<>("name", "$name");
Variable<String> let2 = new Variable<>("code", "$no");
lets.add(let1);
lets.add(let2);
return lets ;
}
private List<Bson> returnPipelines() {
Bson pipeline = new BasicDBObject("$match",
new BasicDBObject("$expr",
new BasicDBObject("$or", new BasicDBObject[]{
new BasicDBObject("$eq", new String[]{"$name", "$$name"}),
new BasicDBObject("$eq", new String[]{"$code", "$$code"})
})
));
List<Bson> pipelines = new ArrayList<>();
pipelines.add(pipeline);
return pipelines ;
}
一开始参照上面链接中的文章时,我的代码总报错,其实心里隐约明白,大概是monggodb的maven版本低了。于是项目全局搜索,发现是这样引用的maven
是spring管理的mongo包,再ctrl鼠标点进去看到
是3.6哇,怎么代码始终报错呢,于是去maven 官网仓库找最新的引用,发现都到3.12了。但是项目现在很多地方已经引用了spring管理的mongobd的包,如果直接换成新版本的包,会有大量代码改动,百度后,这样解决
<!-- mongo start -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-mongodb</artifactId>
<exclusions>
<exclusion>
<groupId>org.mongodb</groupId>
<artifactId>mongodb-driver</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mongodb/mongo-java-driver -->
<dependency>
<groupId>org.mongodb</groupId>
<artifactId>mongo-java-driver</artifactId>
<version>3.12.3</version>
</dependency>
<!-- mongo end -->
即,需要什么包,就告诉maven移除原来的包