MongDB Aggregation 实现多表关联查询
初始数据
表数据初始化语句放在了文章附录。
表对应Java实体类:
public class Grade {
String id;
String gradeName;
// 下面是用于关联查询存储数据的属性
Student student;
List students;
Contact contact;
}public class Student {
String id;
String name;
Integer age;
String gradeId;
// 下面是用于关联查询存储数据的属性
Grade grade;
Contact contact;
String ages;
}public class Contact {
String id;
String phone;
String studentId;
}$lookup（左连接） $lookup相当于进行
左连接，右表数据以数组形式显示原生查询示例 grade表左连接student表
// 以grade为主表，左连接student表
db.getCollection("grade").aggregate([
{
$lookup:{
from:"student",// 连接表
localField: "_id",// 主表关联字段
foreignField: "gradeId",// 连接表关联字段
as: "students"// 别名，数组形式显示
}
},
])
文章图片
MongoTemplate查询示例 【MongDB Aggregation 实现多表关联查询】在主表对象中使用
List存储连接表的数据，若某行记录中连接表数据为空，则为空List// 使用List存储连接表的数据
static void demo1()
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.lookup("student", "_id", "gradeId", "students")
);
List gradeList = mongoTemplate.aggregate(aggregation, "grade", Grade.class).getMappedResults();
gradeList.forEach(System.out::println);
}
// 结果
Grade(id=1, gradeName=一年级, student=null, students=[], contact=null)
Grade(id=2, gradeName=二年级, student=null, students=[Student(id=2, name=lisi, age=2, gradeId=2, grade=null, contacts=null), Student(id=607fe7cb9db49f1b3ba72bcb, name=lisi, age=4, gradeId=2, grade=null, contacts=null), Student(id=607fe7d83981b4053c3e0474, name=lisi, age=6, gradeId=2, grade=null, contacts=null)], contact=null)
Grade(id=3, gradeName=三年级, student=null, students=[Student(id=1, name=zhangsan, age=1, gradeId=3, grade=null, contacts=null), Student(id=607fe7cb9db49f1b3ba72bca, name=zhangsan, age=3, gradeId=3, grade=null, contacts=null), Student(id=607fe7d83981b4053c3e0473, name=zhangsan, age=5, gradeId=3, grade=null, contacts=null)], contact=null)
在主表对象中
使用单个对象存储连接表数据，若某行记录中连接表数据为空，则为null。// 使用单个Student对象存储连接表的数据，会自动映射连接表数组中的第一条数据。
// 所以若是一对多关联，一定要在关联后使用$unwind展开，否则只会取第一条数据。
static void demo2() {
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.lookup("student", "_id", "gradeId", "student")
);
List gradeList = mongoTemplate.aggregate(aggregation, "grade", Grade.class).getMappedResults();
gradeList.forEach(System.out::println);
}
// 结果
Grade(id=1, gradeName=一年级, student=null, students=null, contact=null)
Grade(id=2, gradeName=二年级, student=Student(id=2, name=lisi, age=2, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=三年级, student=Student(id=1, name=zhangsan, age=1, gradeId=3, grade=null, contacts=null), students=null, contact=null)
$unwind数组展开（内连接/左连接） 左连接查询后的关联数据为数组对象的字段属性，使用
$unwind可以将数组对象展开显示。展开默认内连接方式展开，也可以指定左连接方式。$unwind语法 语法1（field 版）：指定要展开的数组属性，默认若数组对象为null或空数组，则不会显示对应的展开，即相当于内连接
{ $unwind: }// 指定要展开的数组属性
语法2（document版）：
{
$unwind:
{
path: ,// 指定要展开的数组属性
includeArrayIndex: ,
preserveNullAndEmptyArrays: // 为true时相当于左连接展开
}
} 原生查询示例 grade表左连接student表，再内连接展开数组
//使用$unwind将数组对象内连接展开
db.getCollection("grade").aggregate([
{
$lookup:{
from:"student",
localField: "_id",
foreignField: "gradeId",
as: "student"
}
},
{
$unwind:"$student"// 使用unwind后会将数组字段展开，相当于内连接展开
},
])结果如下：下面结果缺少了一年级的数据，因为一年级对应的数组对象是空数组
文章图片
grade表左连接student表，再左连接展开数组
//使用$unwind将数组对象左连接展开
db.getCollection("grade").aggregate([
{
$lookup:{
from:"student",
localField: "_id",
foreignField: "gradeId",
as: "student"
}
},
{
$unwind:{// 使用unwind后会将数组字段展开，相当于左连接展开
path:"$student",
preserveNullAndEmptyArrays:true
}
},
])结果如下：
文章图片
MongoTemplate查询示例 内连接展开
// 内连接展开
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.lookup("student", "_id", "gradeId", "student"),
Aggregation.unwind("student")// 相当于内连接展开
);
List gradeList = mongoTemplate.aggregate(aggregation, "grade", Grade.class).getMappedResults();
gradeList.forEach(System.out::println);
// 结果
Grade(id=2, gradeName=二年级, student=Student(id=2, name=lisi, age=2, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=2, gradeName=二年级, student=Student(id=607fe7cb9db49f1b3ba72bcb, name=lisi, age=4, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=2, gradeName=二年级, student=Student(id=607fe7d83981b4053c3e0474, name=lisi, age=6, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=三年级, student=Student(id=1, name=zhangsan, age=1, gradeId=3, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=三年级, student=Student(id=607fe7cb9db49f1b3ba72bca, name=zhangsan, age=3, gradeId=3, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=三年级, student=Student(id=607fe7d83981b4053c3e0473, name=zhangsan, age=5, gradeId=3, grade=null, contacts=null), students=null, contact=null)
左连接展开
// 左连接展开
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.lookup("student", "_id", "gradeId", "student"),
Aggregation.unwind("student", true)// 加上参数true，相当于左连接展开
);
List gradeList = mongoTemplate.aggregate(aggregation, "grade", Grade.class).getMappedResults();
gradeList.forEach(System.out::println);
// 结果
Grade(id=1, gradeName=一年级, student=null, students=null, contact=null)
Grade(id=2, gradeName=二年级, student=Student(id=2, name=lisi, age=2, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=2, gradeName=二年级, student=Student(id=607fe7cb9db49f1b3ba72bcb, name=lisi, age=4, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=2, gradeName=二年级, student=Student(id=607fe7d83981b4053c3e0474, name=lisi, age=6, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=三年级, student=Student(id=1, name=zhangsan, age=1, gradeId=3, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=三年级, student=Student(id=607fe7cb9db49f1b3ba72bca, name=zhangsan, age=3, gradeId=3, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=三年级, student=Student(id=607fe7d83981b4053c3e0473, name=zhangsan, age=5, gradeId=3, grade=null, contacts=null), students=null, contact=null)
A->B->C三表关联查询 使用$lookup和$unwind实现3表关联查询。若是B表关联C表，则A表关联B表后
必须用$unwind展开B表数组对象后，再将B表关联C表。原生查询示例 grade表左连接student表，再内连接展开，再左连接contact表，再内连接展开
db.getCollection("grade").aggregate([
{
$lookup:{
from:"student",
localField: "_id",
foreignField: "gradeId",
as: "student"
}
},
{
$unwind:"$student"
},
{
$lookup:{
from:"contact",
localField: "student._id",
foreignField: "studentId",
as: "contact"
}
},
{
$unwind:"$contact"
},
])结果如下：
文章图片
grade表左连接student表，再左连接展开，再左连接contact表，再左连接展开
db.getCollection("grade").aggregate([
{
$lookup:{
from:"student",
localField: "_id",
foreignField: "gradeId",
as: "student"
}
},
{
$unwind:{
path:"$student",
preserveNullAndEmptyArrays:true
}
},
{
$lookup:{
from:"contact",
localField: "student._id",
foreignField: "studentId",
as: "contact"
}
},
{
$unwind:{
path:"$contact",
preserveNullAndEmptyArrays:true
}
},
])结果如下：
文章图片
MongoTemplate查询示例 内连接三表关联查询
// 内连接三表关联查询
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.lookup("student", "_id", "gradeId", "student"),
Aggregation.unwind("student"),// 相当于内连接展开
Aggregation.lookup("contact", "student._id", "studentId", "contact"),
Aggregation.unwind("contact")// 相当于内连接展开，当然如果想contact数组显示也可以不展开
);
List gradeList = mongoTemplate.aggregate(aggregation, "grade", Grade.class).getMappedResults();
gradeList.forEach(System.out::println);
// 结果
Grade(id=3, gradeName=三年级, student=Student(id=1, name=zhangsan, age=1, gradeId=3, grade=null, contacts=null), students=null, contact=Contact(id=607f872a022b1e3516258669, phone=55555555555, studentId=1))
Grade(id=3, gradeName=三年级, student=Student(id=1, name=zhangsan, age=1, gradeId=3, grade=null, contacts=null), students=null, contact=Contact(id=607f872a022b1e351625866a, phone=66666666666, studentId=1))
左连接三表关联查询
// 左连接三表关联查询
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.lookup("student", "_id", "gradeId", "student"),
Aggregation.unwind("student", true),// 相当于左连接展开
Aggregation.lookup("contact", "student._id", "studentId", "contact"),
Aggregation.unwind("contact", true)// 相当于左连接展开
);
List gradeList = mongoTemplate.aggregate(aggregation, "grade", Grade.class).getMappedResults();
gradeList.forEach(System.out::println);
// 结果
Grade(id=1, gradeName=一年级, student=null, students=null, contact=null)
Grade(id=2, gradeName=二年级, student=Student(id=2, name=lisi, age=2, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=2, gradeName=二年级, student=Student(id=607fe7cb9db49f1b3ba72bcb, name=lisi, age=4, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=2, gradeName=二年级, student=Student(id=607fe7d83981b4053c3e0474, name=lisi, age=6, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=三年级, student=Student(id=1, name=zhangsan, age=1, gradeId=3, grade=null, contacts=null), students=null, contact=Contact(id=607f872a022b1e3516258669, phone=55555555555, studentId=1))
Grade(id=3, gradeName=三年级, student=Student(id=1, name=zhangsan, age=1, gradeId=3, grade=null, contacts=null), students=null, contact=Contact(id=607f872a022b1e351625866a, phone=66666666666, studentId=1))
Grade(id=3, gradeName=三年级, student=Student(id=607fe7cb9db49f1b3ba72bca, name=zhangsan, age=3, gradeId=3, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=三年级, student=Student(id=607fe7d83981b4053c3e0473, name=zhangsan, age=5, gradeId=3, grade=null, contacts=null), students=null, contact=null)
按条件、排序、分页、指定字段映射显示 使用$match、$sort、$skip、$limit、$project可以实现关联查询的条件、排序、分页、指定字段映射显示。
原生查询示例 student表左连接contact表，再左连接展开，再进行条件查询，排序、分页、指定字段显示
db.getCollection("student").aggregate([
{
$lookup:{
from:"contact",
localField: "_id",
foreignField: "studentId",
as: "contact"
}
},
{
$unwind:{
path:"$contact",
preserveNullAndEmptyArrays:true
}
},
{
$match:{// 查询条件
name:"zhangsan"
}
},
{
$sort:{// 排序规则
"age":-1
}
},
{
$skip:2// 跳过两条记录，用于分页
},
{
$limit:2// 限制显示两条记录，用于分页
},
{
$project:{// 字段映射
name:1,
ages:"$age",// 取别名，&age一定要""包围
"contact.phone":1// 内嵌文档字段展示，contact.phone一定要用""包围
}
},
])结果如下：
文章图片
MongoTemplate查询示例
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.lookup("contact", "_id", "studentId", "contact"),
Aggregation.unwind("contact", true),// 相当于左连接展开
Aggregation.match(Criteria.where("name").is("zhangsan")),
Aggregation.sort(Sort.Direction.DESC, "age"),
Aggregation.skip(2L),
Aggregation.limit(2L),
Aggregation.project("name")
.and("age").as("ages")
// 映射为嵌套字段一定要这样写，如果写成project("contact.phone")，则会直接映射为Student的中的phone字段
.and("contact.phone").as("contact.phone")
);
List studentList = mongoTemplate.aggregate(aggregation, "student", Student.class).getMappedResults();
studentList.forEach(System.out::println);
// 结果
Student(id=1, name=zhangsan, age=null, gradeId=null, grade=null, contact=Contact(id=null, phone=55555555555, studentId=null), ages=1)
Student(id=1, name=zhangsan, age=null, gradeId=null, grade=null, contact=Contact(id=null, phone=66666666666, studentId=null), ages=1)
参考资料
- java mongodb 多表关联查询，多条件查询，分页，排序
- 关于Mongodb + java + 多表关联查询（MongoTemplate）
// ----------------------------
// Collection structure for grade
// ----------------------------
db.getCollection("grade").drop();
db.createCollection("grade");
// ----------------------------
// Documents of grade
// ----------------------------
db.getCollection("grade").insert([ {
_id: "1",
gradeName: "一年级",
_class: "com.fwj.root.springboot.middleware.mongodb.demo3.Grade"
} ]);
db.getCollection("grade").insert([ {
_id: "2",
gradeName: "二年级",
_class: "com.fwj.root.springboot.middleware.mongodb.demo3.Grade"
} ]);
db.getCollection("grade").insert([ {
_id: "3",
gradeName: "三年级",
_class: "com.fwj.root.springboot.middleware.mongodb.demo3.Grade"
} ]);
student表
// ----------------------------
// Collection structure for student
// ----------------------------
db.getCollection("student").drop();
db.createCollection("student");
// ----------------------------
// Documents of student
// ----------------------------
db.getCollection("student").insert([ {
_id: "1",
name: "zhangsan",
age: NumberInt("1"),
gradeId: "3",
_class: "com.fwj.root.springboot.middleware.mongodb.demo3.Student"
} ]);
db.getCollection("student").insert([ {
_id: "2",
name: "lisi",
age: NumberInt("2"),
gradeId: "2",
_class: "com.fwj.root.springboot.middleware.mongodb.demo3.Student"
} ]);
db.getCollection("student").insert([ {
_id: ObjectId("607fe7cb9db49f1b3ba72bca"),
name: "zhangsan",
age: NumberInt("3"),
gradeId: "3",
_class: "com.fwj.root.springboot.middleware.mongodb.demo3.Student"
} ]);
db.getCollection("student").insert([ {
_id: ObjectId("607fe7cb9db49f1b3ba72bcb"),
name: "lisi",
age: NumberInt("4"),
gradeId: "2",
_class: "com.fwj.root.springboot.middleware.mongodb.demo3.Student"
} ]);
db.getCollection("student").insert([ {
_id: ObjectId("607fe7d83981b4053c3e0473"),
name: "zhangsan",
age: NumberInt("5"),
gradeId: "3",
_class: "com.fwj.root.springboot.middleware.mongodb.demo3.Student"
} ]);
db.getCollection("student").insert([ {
_id: ObjectId("607fe7d83981b4053c3e0474"),
name: "lisi",
age: NumberInt("6"),
gradeId: "2",
_class: "com.fwj.root.springboot.middleware.mongodb.demo3.Student"
} ]);
contact表
// ----------------------------
// Collection structure for contact
// ----------------------------
db.getCollection("contact").drop();
db.createCollection("contact");
// ----------------------------
// Documents of contact
// ----------------------------
db.getCollection("contact").insert([ {
_id: ObjectId("607f872a022b1e3516258669"),
phone: "55555555555",
studentId: "1",
_class: "com.fwj.root.springboot.middleware.mongodb.demo3.Contact"
} ]);
db.getCollection("contact").insert([ {
_id: ObjectId("607f872a022b1e351625866a"),
phone: "66666666666",
studentId: "1",
_class: "com.fwj.root.springboot.middleware.mongodb.demo3.Contact"
} ]);
推荐阅读
- 关于QueryWrapper|关于QueryWrapper,实现MybatisPlus多表关联查询方式
- MybatisPlus使用queryWrapper如何实现复杂查询
- python学习之|python学习之 实现QQ自动发送消息
- 孩子不是实现父母欲望的工具——林哈夫
- opencv|opencv C++模板匹配的简单实现
- Node.js中readline模块实现终端输入
- java中如何实现重建二叉树
- 人脸识别|【人脸识别系列】| 实现自动化妆
- paddle|动手从头实现LSTM
- pytorch|使用pytorch从头实现多层LSTM