Initial data

The table data initialization statement is placed in the appendix of the article.

Table corresponding Java entity class:

public class Grade {
    String id;
    String gradeName;

    // The following are the properties used to associate query storage data
    Student student;
    List<Student> students;
    Contact contact;
}

public class Student {
    String id;
    String name;
    Integer age;
    String gradeId;

    // The following are the properties used to associate query storage data
    Grade grade;
    Contact contact;
    String ages;
}

public class Contact {
    String id;
    String phone;
    String studentId;
}

$lookup (left connection)

$lookup is equivalent to the left connection, and the data in the right table is displayed in the form of array

Native Query example

The grade table is left connected to the student table

// grade is the main table, and the student table is connected on the left
db.getCollection("grade").aggregate([
    {
        $lookup:{
            from:"student",// Connection table
            localField: "_id",// Main table associated fields
            foreignField: "gradeId",// Join table associated fields
            as: "students"// Alias, array display
        }
    },
])

MongoTemplate query example

Use List in the main table object to store the data of the connection table. If the connection table data in a row record is empty, it is an empty List

// Use list < student > to store the data of the connection table
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);
}
// result
Grade(id=1, gradeName=first grade, student=null, students=[], contact=null)
Grade(id=2, gradeName=second grade, 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=Third grade, 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)

A single object is used in the main table object to store the connection table data. If the connection table data in a row record is empty, it is null.

// Using a single Student object to store the data of the connection table will automatically map the first data in the connection table array.
// Therefore, if it is a one to many association, you must use $unwind to expand after the association, otherwise only the first data will be taken.
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);
}
// result
Grade(id=1, gradeName=first grade, student=null, students=null, contact=null)
Grade(id=2, gradeName=second grade, student=Student(id=2, name=lisi, age=2, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=Third grade, student=Student(id=1, name=zhangsan, age=1, gradeId=3, grade=null, contacts=null), students=null, contact=null)

$unwind array expansion (inner connection / left connection)

The associated data after the left join query is the field property of the array object. The array object can be expanded and displayed by using $unwind. Expand the default inner connection mode, or specify the left connection mode.

$unwind syntax

Syntax 1 (field version): specify the array attribute to expand. By default, if the array object is null or empty, the corresponding expansion will not be displayed, that is, it is equivalent to inner connection

{ $unwind: <field path> }// Specifies the array properties to expand

Syntax 2 (document version):

{
  $unwind:
    {
      path: <field path>,// Specifies the array properties to expand
      includeArrayIndex: <string>,
      preserveNullAndEmptyArrays: <boolean>// When true, it is equivalent to left connection expansion
    }
}

Native Query example

The grade table is left connected to the student table, and then the inner connection expands the array

//Use $unwind to expand connections within array objects
db.getCollection("grade").aggregate([
    {
        $lookup:{
            from:"student",
            localField: "_id",
            foreignField: "gradeId",
            as: "student"
        }
    },
    {
        $unwind:"$student"// After using unwind, the array fields will be expanded, which is equivalent to inner connection expansion
    },
])

The results are as follows: the data of grade 1 is missing in the following results, because the array object corresponding to grade 1 is an empty array

The grade table is left connected to the student table, and then left connected to expand the array

//Use $unwind to expand the left join of array objects
db.getCollection("grade").aggregate([
    {
        $lookup:{
            from:"student",
            localField: "_id",
            foreignField: "gradeId",
            as: "student"
        }
    },
    {
        $unwind:{// After using unwind, the array field will be expanded, which is equivalent to the left connection expansion
            path:"$student",
            preserveNullAndEmptyArrays:true
        }
    },
])

The results are as follows:

MongoTemplate query example

Inner connection expansion

// Inner connection expansion
Aggregation aggregation = Aggregation.newAggregation(
        Aggregation.lookup("student", "_id", "gradeId", "student"),
        Aggregation.unwind("student")// Equivalent to inner connection expansion
);
List<Grade> gradeList = mongoTemplate.aggregate(aggregation, "grade", Grade.class).getMappedResults();
gradeList.forEach(System.out::println);
// result
Grade(id=2, gradeName=second grade, student=Student(id=2, name=lisi, age=2, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=2, gradeName=second grade, student=Student(id=607fe7cb9db49f1b3ba72bcb, name=lisi, age=4, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=2, gradeName=second grade, student=Student(id=607fe7d83981b4053c3e0474, name=lisi, age=6, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=Third grade, student=Student(id=1, name=zhangsan, age=1, gradeId=3, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=Third grade, student=Student(id=607fe7cb9db49f1b3ba72bca, name=zhangsan, age=3, gradeId=3, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=Third grade, student=Student(id=607fe7d83981b4053c3e0473, name=zhangsan, age=5, gradeId=3, grade=null, contacts=null), students=null, contact=null)

Left connection expansion

// Left connection expansion
Aggregation aggregation = Aggregation.newAggregation(
        Aggregation.lookup("student", "_id", "gradeId", "student"),
        Aggregation.unwind("student", true)// Adding the parameter true is equivalent to left connection expansion
);
List<Grade> gradeList = mongoTemplate.aggregate(aggregation, "grade", Grade.class).getMappedResults();
gradeList.forEach(System.out::println);
// result
Grade(id=1, gradeName=first grade, student=null, students=null, contact=null)
Grade(id=2, gradeName=second grade, student=Student(id=2, name=lisi, age=2, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=2, gradeName=second grade, student=Student(id=607fe7cb9db49f1b3ba72bcb, name=lisi, age=4, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=2, gradeName=second grade, student=Student(id=607fe7d83981b4053c3e0474, name=lisi, age=6, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=Third grade, student=Student(id=1, name=zhangsan, age=1, gradeId=3, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=Third grade, student=Student(id=607fe7cb9db49f1b3ba72bca, name=zhangsan, age=3, gradeId=3, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=Third grade, student=Student(id=607fe7d83981b4053c3e0473, name=zhangsan, age=5, gradeId=3, grade=null, contacts=null), students=null, contact=null)

A - > b - > C three table Association query

Use $lookup and $unwind to implement 3 table Association query. If table B is associated with table C, after table A is associated with table B, you must expand the array object of table B with $unwind, and then associate table B with table C.

Native Query example

The grade table connects the student table on the left, and then expands the inner connection, and then connects the contact table on the left, and then expands the inner connection

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"
    },
])

The results are as follows:

The grade table is left connected to the student table, then left connected and expanded, then left connected to the contact table, and then left connected and expanded

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
        }
    },
])

The results are as follows:

MongoTemplate query example

Internal connection three table Association query

// Internal connection three table Association query
Aggregation aggregation = Aggregation.newAggregation(
        Aggregation.lookup("student", "_id", "gradeId", "student"),
        Aggregation.unwind("student"),// Equivalent to inner connection expansion
        Aggregation.lookup("contact", "student._id", "studentId", "contact"),
        Aggregation.unwind("contact")// This is equivalent to inner connection expansion. Of course, if you want to display the contact array, you can not expand it
);
List<Grade> gradeList = mongoTemplate.aggregate(aggregation, "grade", Grade.class).getMappedResults();
gradeList.forEach(System.out::println);

// result
Grade(id=3, gradeName=Third grade, 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=Third grade, student=Student(id=1, name=zhangsan, age=1, gradeId=3, grade=null, contacts=null), students=null, contact=Contact(id=607f872a022b1e351625866a, phone=66666666666, studentId=1))

Left join three table Association query

// Left join three table Association query
Aggregation aggregation = Aggregation.newAggregation(
        Aggregation.lookup("student", "_id", "gradeId", "student"),
        Aggregation.unwind("student", true),// Equivalent to left connection expansion
        Aggregation.lookup("contact", "student._id", "studentId", "contact"),
        Aggregation.unwind("contact", true)// Equivalent to left connection expansion
);
List<Grade> gradeList = mongoTemplate.aggregate(aggregation, "grade", Grade.class).getMappedResults();
gradeList.forEach(System.out::println);

// result
Grade(id=1, gradeName=first grade, student=null, students=null, contact=null)
Grade(id=2, gradeName=second grade, student=Student(id=2, name=lisi, age=2, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=2, gradeName=second grade, student=Student(id=607fe7cb9db49f1b3ba72bcb, name=lisi, age=4, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=2, gradeName=second grade, student=Student(id=607fe7d83981b4053c3e0474, name=lisi, age=6, gradeId=2, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=Third grade, 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=Third grade, 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=Third grade, student=Student(id=607fe7cb9db49f1b3ba72bca, name=zhangsan, age=3, gradeId=3, grade=null, contacts=null), students=null, contact=null)
Grade(id=3, gradeName=Third grade, student=Student(id=607fe7d83981b4053c3e0473, name=zhangsan, age=5, gradeId=3, grade=null, contacts=null), students=null, contact=null)

Display by condition, sorting, pagination and specified field mapping

Using $match, $sort, $skip, $limit and $project, you can realize the condition, sorting, paging and specified field mapping display of associated query.

Native Query example

The student table is connected to the contact table on the left, then expanded on the left, and then queried by criteria, sorted, paged and displayed in specified fields

db.getCollection("student").aggregate([
    {
        $lookup:{
            from:"contact",
            localField: "_id",
            foreignField: "studentId",
            as: "contact"
        }
    },
    {
        $unwind:{
            path:"$contact",
            preserveNullAndEmptyArrays:true
        }
    },
    {
        $match:{// query criteria
            name:"zhangsan"
        }
    },
    {
        $sort:{// Sorting rules
            "age":-1
        }
    },
    {
        $skip:2// Skip two records for paging
    },
    {
        $limit:2// Limit the display of two records for pagination
    },
    {
        $project:{// Field mapping
            name:1,
            ages:"$age",// Alias, &age must be surrounded by ""
            "contact.phone":1// Embedded document field display, contact.phone must be surrounded by ""
        }
    },
])

The results are as follows:

MongoTemplate query example

Aggregation aggregation = Aggregation.newAggregation(
        Aggregation.lookup("contact", "_id", "studentId", "contact"),
        Aggregation.unwind("contact", true),// Equivalent to left connection expansion
        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")
                // If the mapping is a nested field, it must be written in this way. If it is written as project("contact.phone"), it will be directly mapped to the phone field in the Student
                .and("contact.phone").as("contact.phone")
);
List<Student> studentList = mongoTemplate.aggregate(aggregation, "student", Student.class).getMappedResults();
studentList.forEach(System.out::println);

// result
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)

reference material

• java mongodb multi table Association query, multi condition query, paging, sorting
• About Mongodb + java + multi table Association query (MongoTemplate)

appendix

MongoDB table initialization statement

grade table

// ----------------------------
// Collection structure for grade
// ----------------------------
db.getCollection("grade").drop();
db.createCollection("grade");

// ----------------------------
// Documents of grade
// ----------------------------
db.getCollection("grade").insert([ {
    _id: "1",
    gradeName: "first grade",
    _class: "com.fwj.root.springboot.middleware.mongodb.demo3.Grade"
} ]);
db.getCollection("grade").insert([ {
    _id: "2",
    gradeName: "second grade",
    _class: "com.fwj.root.springboot.middleware.mongodb.demo3.Grade"
} ]);
db.getCollection("grade").insert([ {
    _id: "3",
    gradeName: "Third grade",
    _class: "com.fwj.root.springboot.middleware.mongodb.demo3.Grade"
} ]);

student table

// ----------------------------
// 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 table

// ----------------------------
// 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"
} ]);