MongDB Aggregation 实现多表关联查询

头像
fsta
    阅读 14 分钟
    1

    初始数据

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

    表对应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)

    参考资料

    附录

    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"
} ]);
    mongodbjava多表查询
    fsta
    8 声望0 粉丝
    « 上一篇
    Java的无锁原子类
    下一篇 »
    分代收集理论与垃圾回收算法

    引用和评论

    推荐阅读
    头像
    线程池基本介绍与使用

    fsta阅读 1.3k

    头像
    Bitmap 和 布隆过滤器傻傻分不清？你这不应该啊

    程序员小富赞 10阅读 4.7k评论 1

    头像
    Jerry和您聊聊Chrome开发者工具

    注销赞 9阅读 4.8k评论 3

    头像
    Spring 实现 3 种异步流式接口，干掉接口超时烦恼

    程序员小富赞 6阅读 2.4k

    头像
    💢线上高延迟请求排查

    crossoverJie赞 6阅读 4k评论 5

    头像
    这些年

    注销赞 2阅读 4k

    头像
    MongoDB Atlas与YoMio.AI近乎完美适配:推理更快速、查询更灵活、场景更丰富

    MongoDB数据平台赞 1阅读 12.7k

    0 条评论
    得票最新