1、mongodb关联查询基本操作
mongodb的关联查询需要用到聚合函数Aggregate(),关于聚合函数的详细内容,可以参考mongodb的官网,官网传送门;本文直接以具体的数据库数据来介绍关联查询的操作。现在定义如下数据:
# school表
{
"_id" : ObjectId("5f51b23d34f9c5155cda1ab3"),
"name" : "BJ_university",
"studentId" : ObjectId("5f51b23d34f9c5155cda1ab4"), // studentId为ObjectId类型
"_class" : "com.learn.entity.School"
}
# student表
{
"_id" : ObjectId("5f51b23d34f9c5155cda1ab4"),
"name" : "张三",
"sex" : "男",
"_class" : "com.learn.entity.Student"
}
{
"_id" : ObjectId("5f588b84965ec50380075154"),
"name" : "lisi",
"sex" : "男",
"_class" : "com.learn.entity.Student"
}
使用聚合函数进行关联查询,查询指令如下:
db.school.aggregate([
{
'$lookup':{
'from': 'student',
'localField' : 'studentId',
'foreignField' : '_id',
'as' : 'student'
}
}
])
如果使用mongoTemplate可以使用如下代码:
AggregationOperation lookupOperation=LookupOperation.newLookup()
.from("student")
.localField("studentId")
.foreignField("_id")
.as("student");
Aggregation aggregation=Aggregation.newAggregation(lookupOperation);
AggregationResults<Document> result=mongoTemplate.aggregate(aggregation, "school", Document.class);
List<Document> objs=result.getMappedResults();
//.....然后将Document解析为对应的model对象数据即可
查询结果为:
{
"_id" : ObjectId("5f51b23d34f9c5155cda1ab3"),
"name" : "BJ_university",
"studentId" : ObjectId("5f51b23d34f9c5155cda1ab4"),
"_class" : "com.learn.entity.School",
"student" : [
{
"_id" : ObjectId("5f51b23d34f9c5155cda1ab4"),
"name" : "张三",
"sex" : "男",
"_class" : "com.learn.entity.Student"
}
]
}
由以上的分析可知,关联查询时如果两个表的字段类型一致,可以直接使用$lookup进行关联查询操作。
2、关联字段类型不一致时
现在将上述中的数据改为如下:
# school表
{
"_id" : ObjectId("5f51b23d34f9c5155cda1ab3"),
"name" : "BJ_university",
"studentId" : "5f51b23d34f9c5155cda1ab4", // studentId为string类型
"_class" : "com.learn.entity.School"
}
# student表
{
"_id" : ObjectId("5f51b23d34f9c5155cda1ab4"),
"name" : "张三",
"sex" : "男",
"_class" : "com.learn.entity.Student"
}
{
"_id" : ObjectId("5f588b84965ec50380075154"),
"name" : "lisi",
"sex" : "男",
"_class" : "com.learn.entity.Student"
}
关联的数据一张表中为string类型,另一个表中为ObjectId类型
对于这种情况下如果还是使用上述的方式来查询数据,查询到的结果为:
{
"_id" : ObjectId("5f51b23d34f9c5155cda1ab3"),
"name" : "BJ_university",
"studentId" : "5f51b23d34f9c5155cda1ab4",
"_class" : "com.learn.entity.School",
"objId" : ObjectId("5f51b23d34f9c5155cda1ab4"),
"student" : []
}
这种情况下需要使用Aggregate中的另外一个piple——$addFields,具体使用如下:
db.school.aggregate([
{
'$addFields': {'objId':{'$toObjectId':'$studentId'}} # 如果是ObjectId转化为String,将'$toObjectId'改为'$toString'
},
{
'$lookup':{
'from': 'student',
'localField' : 'objId',
'foreignField' : '_id',
'as' : 'student'
}
}
])
使用mongoTemplate时可以进行如下操作:
String sql="{" +
" '$addFields': {'objId':{'$toObjectId':'$studentId'}}\r\n" +
" }";
Document doc=new Document().parse(sql);
AggregationOperation lookupOperation=LookupOperation.newLookup()
.from("student")
.localField("objId")
.foreignField("_id")
.as("student");
Aggregation aggregation=Aggregation.newAggregation(
new AggregationOperation() {
@Override
public Document toDocument(AggregationOperationContext context){
return doc;
}
},lookupOperation);
AggregationResults<Document> result=mongoTemplate.aggregate(aggregation, "school", Document.class);
List<Document> objs=result.getMappedResults();
//.....然后将Document解析为对应的model对象数据即可
执行结果如下:
{
"_id" : ObjectId("5f51b23d34f9c5155cda1ab3"),
"name" : "BJ_university",
"studentId" : "5f51b23d34f9c5155cda1ab4",
"_class" : "com.learn.entity.School",
"objId" : ObjectId("5f51b23d34f9c5155cda1ab4"), //新添加的字段,ObjectId类型
"student" : [
{
"_id" : ObjectId("5f51b23d34f9c5155cda1ab4"),
"name" : "张三",
"sex" : "男",
"_class" : "com.learn.entity.Student"
}
]
}
Document 为Bson接口的实现类,其parse方法会将json格式的操作命令转化为一个Document对象,然后就可以进行相应的操作,这里简单介绍下Document在查询数据时的使用:
Document doc1=new Document().parse("{'studentId':'5f51b23d34f9c5155cda1ab4'}");
FindIterable<Document> docFindIterable= mongoTemplate.getCollection("school").find(doc1);
// 这样即可查询出所有studentId=5f51b23d34f9c5155cda1ab4的数据
3、其他
对于上述的操作还有一个问题,由于在上述的操作中关联查询之后的student为一个数组,如果不想使用数组的方式呈现,可以使用’$unwind’将数组打散来呈现,具体操作如下:
db.school.aggregate([
{
'$addFields': {'objId':{'$toObjectId':'$studentId'}}
},
{
'$lookup':{
'from': 'student',
'localField' : 'objId',
'foreignField' : '_id',
'as' : 'student'
}
},
{
'$unwind':'$student'
},
])
# $unwind的详细使用如下:
{
$unwind:
{
path: <field path>,
includeArrayIndex: <string>,
preserveNullAndEmptyArrays: <boolean>
}
}
# path 要打散的字段
# includeArrayIndex 可选的。一个新字段的名称,用于保存元素的数组索引。名称不能以美元符号开头
# preserveNullAndEmptyArrays 可选的。如果为true,则如果path为null,缺失或空数组,则$unwind输出文档。如果为false,如果path为null,缺少或为空数组,$unwind则不会输出文档。默认值为false
呈现的效果如下:
{
"_id" : ObjectId("5f51b23d34f9c5155cda1ab3"),
"name" : "BJ_university",
"studentId" : "5f51b23d34f9c5155cda1ab4",
"_class" : "com.learn.entity.School",
"objId" : ObjectId("5f51b23d34f9c5155cda1ab4"), //新添加的字段,ObjectId类型
"student" : {
"_id" : ObjectId("5f51b23d34f9c5155cda1ab4"),
"name" : "张三",
"sex" : "男",
"_class" : "com.learn.entity.Student"
}
}
但是这样的话,原本只有一条数据,但是拆分以后获取到的数据个数就是student数组中元素的个数,在实际使用中可以根据具体情况来选择!
在$project
中可以对查询出来要显示的字段设置别名,如下:
db.school.aggregate([
{
'$addFields': {'objId':{'$toObjectId':'$studentId'}}
},
{
'$lookup':{
'from': 'student',
'localField' : 'objId',
'foreignField' : '_id',
'as' : 'student'
}
},
{
'$unwind':'$student'
},
{
'$project':{
# 以为student中的sex设置别名为例
sex:'$student.sex'
#...其他的省略
}
}
])
呈现的效果如下:
{
"_id" : ObjectId("5f51b23d34f9c5155cda1ab3"),
"name" : "BJ_university",
"studentId" : "5f51b23d34f9c5155cda1ab4",
"_class" : "com.learn.entity.School",
"objId" : ObjectId("5f51b23d34f9c5155cda1ab4"), //新添加的字段,ObjectId类型
"student" : {
"_id" : ObjectId("5f51b23d34f9c5155cda1ab4"),
"name" : "张三",
"sex" : "男",
"_class" : "com.learn.entity.Student"
},
"sex":"男" // 设置sex别名后显示的字段
}
4、DBRef在Lookup中的使用
现在有这样一种情况,将上例中的数据进行改造:
# school表
{
"_id" : ObjectId("5f51b23d34f9c5155cda1ab3"),
"name" : "BJ_university",
"stut":{
"$ref":"student",
"$id":ObjectId("5f51b23d34f9c5155cda1ab4")
}, //改造的点
"_class" : "com.learn.entity.School"
}
# student表
{
"_id" : ObjectId("5f51b23d34f9c5155cda1ab4"),
"name" : "张三",
"sex" : "男",
"_class" : "com.learn.entity.Student"
}
{
"_id" : ObjectId("5f588b84965ec50380075154"),
"name" : "lisi",
"sex" : "男",
"_class" : "com.learn.entity.Student"
}
此时在school中进行关联查询,正常情况下我们会按照下面的写法:
db.school.aggregate([
{
'$lookup':{
'from': 'student',
'localField' : 'stut.$id',
'foreignField' : '_id',
'as' : 'student'
}
}
])
但是执行的话却会报错,在lookup中是不能够使用$的形式的字段的,所以在执行lookup之前要进行如下的修改:
db.school.aggregate([
{$addFields:{"newstut":
{$arrayToObject:{$map:{
input:{$objectToArray:"$stut"},
in:{
k:{$cond:[
{$eq:[{"$substrCP":["$$this.k",0,1]},{$literal:"$"}]},
{$substrCP:["$$this.k",1,{$strLenCP:"$$this.k"}]},
"$$this.k"
]},
v:"$$this.v"
}
}}}
}},
{
'$lookup':{
'from': 'student',
'localField' : 'newstut.id',
'foreignField' : '_id',
'as' : 'student'
}
}
])
其中$addFields执行之后的效果如下:
# school表
{
"_id" : ObjectId("5f51b23d34f9c5155cda1ab3"),
"name" : "BJ_university",
"stut":{
"$ref":"student",
"$id":ObjectId("5f51b23d34f9c5155cda1ab4")
},
"newstut":{
"ref":"student",
"id":ObjectId("5f51b23d34f9c5155cda1ab4")
},
"_class" : "com.learn.entity.School"
}
5、从ISODate中获取年月日等
mongoDB数据如下:
{
"_id" : ObjectId("5f15e9b024aa9a00016fb7b0"),
"createStamp" : ISODate("2020-07-20T19:00:00.153+0000"),
}
可以使用如下方式:
db.collections.aggregate([
{
'$addFields': {
Year:{'$year':'$createStamp'},
Month:{'$month':'$createStamp'},
Day:{'$dayOfMonth':'$createStamp'},
Hour: { $hour: "$createStamp" },
Minutes: { $minute: "$createStamp" },
Seconds: { $second: "$createStamp" },
Milliseconds: { $millisecond: "$createStamp" },
DayOfYear: { $dayOfYear: "$createStamp" },
DayOfWeek: { $dayOfWeek: "$createStamp"},
Week: { $week: "$createStamp" }
}
}
])
执行结果如下:
{
"_id" : ObjectId("5f15e9b024aa9a00016fb7b0"),
"createStamp" : ISODate("2020-07-20T19:00:00.153+0000"),
"Year" : NumberInt(2020),
"Month" : NumberInt(7),
"Day" : NumberInt(20),
"Hour" : NumberInt(19),
"Minutes" : NumberInt(0),
"Seconds" : NumberInt(0),
"Milliseconds" : NumberInt(153),
"DayOfYear" : NumberInt(202),
"DayOfWeek" : NumberInt(2),
"Week" : NumberInt(29)
}