1. 首页
  2. 数据库
  3. MongDB Aggregation 实现多表关联查询

MongDB Aggregation 实现多表关联查询

fsta -
MongDB Aggregation 实现多表关联查询
初始数据

表数据初始化语句放在了文章附录。

表对应Java实体类:

public class Grade {
    String id;
    String gradeName;

    // 下面是用于关联查询存储数据的属性
    Student student;
    List<Student> 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查询示例

在主表对象中使用List存储连接表的数据，若某行记录中连接表数据为空，则为空List

// 使用List<Student>存储连接表的数据
static void demo1() 
    Aggregation aggregation = Aggregation.newAggregation(
            Aggregation.lookup("student", "_id", "gradeId", "students")
    );
    List<Grade> 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<Grade> 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: <field path> }// 指定要展开的数组属性

语法2（document版）：

{
  $unwind:
    {
      path: <field path>,// 指定要展开的数组属性
      includeArrayIndex: <string>,
      preserveNullAndEmptyArrays: <boolean>// 为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<Grade> 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<Grade> 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<Grade> 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<Grade> 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<Student> 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）附录MongoDB表初始化语句

grade表

// ----------------------------
// 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"
} ]);
特别申明：本文内容来源网络，版权归原作者所有，如有侵权请立即与我们联系（cy198701067573@163.com），我们将及时处理。
上一篇： mongoDb入门并整合springboot
下一篇： MongoDB学习之复制集

Tags 标签

mongodbjava多表查询

扩展阅读

你也许还不懂静态方法和实例方法
2020-09-03 11:39:33 []
从每秒6000写请求谈起
2020-09-11 11:06:20 []
提高招采和供应链效率，制造型企业应该这么做！
2020-09-13 22:13:33 []
从用户输入手机验证码开始
2020-09-13 20:16:19 []
Base64编码的前世今生
2020-10-13 20:09:26 []
redis的两种持久化的机制，你真的了解么？
2020-10-14 22:10:19 []
贝壳找房诚邀java/php研发工程师
2020-10-21 01:45:18 []
电竞网站APP源码开发示例 英雄联盟【赛事数据】代码演示
2020-10-23 02:51:27 []
Java详细学习路线及路线图(2020最新版) | Java工程师成神之路 | Java最全学习路线
2020-11-11 06:51:58 []
使用唯一id,生成唯一字符串订单号,唯一邀请码等唯一字符串,并可以反序列化原id
2020-11-15 21:27:07 []

加个好友，技术交流

1628738909466805.jpg

码农天地

首页 HTML/CSS WEB服务器 PHP Linux 数据库 异常报错 插件工具 美文欣赏 站长直荐
友情链接：码农天地爱厂家深圳钻石回收麦思会议

Copyright © 2019-2024| 沪ICP备19030844号-2 | SiteMap