For AI agents: a documentation index is available at https://www.mongodb.com/docs/llms.txt — markdown versions of all pages are available by appending .md to any URL path.
Docs Menu
Docs Home
/ /
Hibernate ORM Extension
Docs Home
/
Client Libraries
/
Java
/
Hibernate ORM Extension
Docs Home
/
Client Libraries
/
Java
/
Hibernate ORM Extension

Join Entities Across Collections

Overview

In this guide, you can learn how to use the MongoDB Extension for Hibernate ORM to query across entities that are linked by an association. You can use either Hibernate Query Language (HQL) or Jakarta Persistence Query Language (JPQL).

To join entities, you can either navigate the association path in a join clause, or name the entity in a join clause and supply an ON clause. You can also add an ON clause to an association join.

The Hibernate ORM extension translates each join to a MongoDB $lookup stage and an $unwind stage.

Note

Reference Columns in ON Conditions

An ON condition must compare columns. The Hibernate ORM extension does not support comparing entity references, as in on m = c.movie, or navigating an association within the condition, as in on c.movie.title = 'Blue Jasmine'. You can compare two whole composite identifiers, as described in the Composite Key Joins section of this guide. To learn which join types the Hibernate ORM extension supports, see Query Support on the Feature Compatibility page.

Sample Data

Movie Entity

The examples in this guide use the Movie entity, which represents the sample_mflix.movies collection from the Atlas sample datasets. The Movie entity has the following definition:

import com.mongodb.hibernate.annotations.ObjectIdGenerator;
import org.bson.types.ObjectId;
import java.time.Instant;
import java.util.List;
import jakarta.persistence.Embedded;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
@Entity
@Table(name = "movies")
public class Movie {
   @Id
   @ObjectIdGenerator
   private ObjectId id;
   private String title;
   private String plot;
   private int year;
   private List<String> cast;
   private List<String> directors;
   private Instant released;
   @Embedded
   private Awards awards;
   @OneToMany(mappedBy = "movie", fetch = FetchType.LAZY)
   private List<Comment> comments;
   public Movie(String title, String plot, int year, List<String> cast, List<String> directors, Instant released, Awards awards) {
      this.title = title;
      this.plot = plot;
      this.year = year;
      this.cast = cast;
      this.directors = directors;
      this.released = released;
      this.awards = awards;
   }
   public Movie() {
   }
   public ObjectId getId() {
      return id;
   }
   public String getTitle() {
      return title;
   }
   public void setTitle(String title) {
      this.title = title;
   }
   public String getPlot() {
      return plot;
   }
   public void setPlot(String plot) {
      this.plot = plot;
   }
   public int getYear() {
      return year;
   }
   public void setYear(int year) {
      this.year = year;
   }
   public List<String> getCast() {
      return cast;
   }
   public void setCast(List<String> cast) {
      this.cast = cast;
   }
   public List<String> getDirectors() {
      return directors;
   }
   public void setDirectors(List<String> directors) {
      this.directors = directors;
   }
   public Awards getAwards() {
      return awards;
   }
   public void setAwards(Awards awards) {
      this.awards = awards;
   }
   public Instant getReleased() {
      return released;
   }
   public void setReleased(Instant released) {
      this.released = released;
   }
   public List<Comment> getComments() {
      return comments;
   }
   public void setComments(List<Comment> comments) {
      this.comments = comments;
   }
}

To learn how to create a Java application that uses the MongoDB Extension for Hibernate ORM to interact with this MongoDB sample collection, see the Get Started tutorial.

The examples on this page join the sample_mflix.movies collection to the sample_mflix.comments collection. Documents in the comments collection store the _id value of the movie they describe in a movie_id field.

The Movie entity maps its comments field to the Comment entities that reference it. The movie's release date is stored in the Movie entity's released field.

Comment Entity

The following Comment entity maps to the comments collection and references its related Movie entity in its movie field:

package org.example;
import com.mongodb.hibernate.annotations.ObjectIdGenerator;
import jakarta.persistence.Column;
import org.bson.types.ObjectId;
import java.time.Instant;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;
@Entity
@Table(name = "comments")
public class Comment {
    @Id
    @ObjectIdGenerator
    @Column(name = "_id")
    private ObjectId id;
    private String name;
    private String email;
    private String text;
    private Instant date;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "movie_id")
    private Movie movie;
    public Comment(String name, String email, String text, Instant date, Movie movie) {
        this.name = name;
        this.email = email;
        this.text = text;
        this.date = date;
        this.movie = movie;
    }
    public Comment() {
    }
    public ObjectId getId() {
        return id;
    }
    public void setId(ObjectId id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getText() {
        return text;
    }
    public void setText(String text) {
        this.text = text;
    }
    public Instant getDate() {
        return date;
    }
    public void setDate(Instant date) {
        this.date = date;
    }
    public Movie getMovie() {
        return movie;
    }
    public void setMovie(Movie movie) {
        this.movie = movie;
    }
}

User Entity

The examples in the Compound ON Conditions section also use the following User entity, which maps to the sample_mflix.users collection:

package org.example;
import com.mongodb.hibernate.annotations.ObjectIdGenerator;
import jakarta.persistence.Column;
import org.bson.types.ObjectId;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
@Entity
@Table(name = "users")
public class User {
    @Id
    @ObjectIdGenerator
    @Column(name = "_id")
    private ObjectId id;
    private String name;
    private String email;
    public User(String name, String email) {
        this.name = name;
        this.email = email;
    }
    public User() {
    }
    public ObjectId getId() {
        return id;
    }
    public void setId(ObjectId id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
}

Inner Join

An inner join returns only entities that have a match on both sides of the association.

The following example uses an inner join to retrieve the title of each movie released in 2015 alongside the name of each person who commented on it. Movies that have no comments are excluded from the results:

var innerJoinResults = session.createQuery("select m.title, c.name from Movie m join m.comments c where m.year = :y", Object[].class)
                              .setParameter("y", 2015)
                              .getResultList();
for (var row : innerJoinResults) {
    System.out.println("Title: " + row[0] + ", Commenter: " + row[1]);
}
var innerJoinResults = entityManager.createQuery("select m.title, c.name from Movie m join m.comments c where m.year = :y", Object[].class)
                                    .setParameter("y", 2015)
                                    .getResultList();
for (var row : innerJoinResults) {
    System.out.println("Title: " + row[0] + ", Commenter: " + row[1]);
}

The Hibernate ORM extension translates the preceding join to the following stages:

{
  "$lookup": {
    "from": "comments",
    "localField": "_id",
    "foreignField": "movie_id",
    "as": "#c1_0"
  }
},
{ "$unwind": "$#c1_0" }

The Hibernate ORM extension generates the as field name from the alias in your query statement and uses it to reference joined fields in later stages of the pipeline.

Left Outer Join

A left outer join returns every entity on the left side of the association, including entities that have no match on the right side. Entities that have no match on the right side return null results for the right side of the join.

The following example uses a left outer join to retrieve the title of each movie released in 2015 alongside the name of each person who commented on it. Movies that have no comments are included in the results:

var leftJoinResults = session.createQuery("select m.title, c.name from Movie m left join m.comments c where m.year = :y", Object[].class)
                             .setParameter("y", 2015)
                             .getResultList();
for (var row : leftJoinResults) {
    System.out.println("Title: " + row[0] + ", Commenter: " + row[1]);
}
var leftJoinResults = entityManager.createQuery("select m.title, c.name from Movie m left join m.comments c where m.year = :y", Object[].class)
                                   .setParameter("y", 2015)
                                   .getResultList();
for (var row : leftJoinResults) {
    System.out.println("Title: " + row[0] + ", Commenter: " + row[1]);
}

The Hibernate ORM extension translates the preceding join to the following stages. The preserveNullAndEmptyArrays option retains movies that have no matching comments:

{
  "$lookup": {
    "from": "comments",
    "localField": "_id",
    "foreignField": "movie_id",
    "as": "#c1_0"
  }
},
{
  "$unwind": {
    "path": "$#c1_0",
    "preserveNullAndEmptyArrays": true
  }
}

The Hibernate ORM extension generates the as field name from the alias in your query statement and uses it to reference joined fields in later stages of the pipeline.

Join Fetch

A JOIN FETCH clause loads an associated entity in the same query as its parent, so you can access the association after the session closes.

The following example uses a JOIN FETCH clause to retrieve comments and load the Movie entity that each comment references:

var comments = session.createQuery("from Comment c join fetch c.movie where c.name = :n", Comment.class)
                      .setParameter("n", "Andrea Le")
                      .getResultList();
for (var c : comments) {
    System.out.println("Commenter: " + c.getName() + ", Title: " + c.getMovie().getTitle());
}
var comments = entityManager.createQuery("select c from Comment c join fetch c.movie where c.name = :n", Comment.class)
                            .setParameter("n", "Andrea Le")
                            .getResultList();
for (var c : comments) {
    System.out.println("Commenter: " + c.getName() + ", Title: " + c.getMovie().getTitle());
}

The Hibernate ORM extension translates a JOIN FETCH clause to the same pipeline stages as the equivalent join.

Compound ON Conditions

An ON condition can combine multiple field comparisons with AND or OR. Use a compound condition to join entities that are related by more than one field. To join entities that have a composite primary key, see the Composite Key Joins section of this guide.

The following example joins the users and comments collections on both the name and the email field, so that a comment matches a user only when both fields are equal:

var compoundOnResults = session.createQuery("select u.name, c.text from User u join Comment c on u.name = c.name and u.email = c.email", Object[].class)
                               .getResultList();
for (var row : compoundOnResults) {
    System.out.println("Name: " + row[0] + ", Comment: " + row[1]);
}
var compoundOnResults = entityManager.createQuery("select u.name, c.text from User u join Comment c on u.name = c.name and u.email = c.email", Object[].class)
                                     .getResultList();
for (var row : compoundOnResults) {
    System.out.println("Name: " + row[0] + ", Comment: " + row[1]);
}

Because the condition compares more than one field, the Hibernate ORM extension uses the let and pipeline form of the $lookup stage rather than the localField and foreignField form. The let option binds each field from the outer collection to a variable, and the pipeline option compares those variables to the fields of the joined collection in a $expr operator:

{
  "$lookup": {
    "from": "comments",
    "let": {
      "v0_u1_0_name": "$name",
      "v1_u1_0_email": "$email"
    },
    "pipeline": [
      {
        "$match": {
          "$expr": {
            "$and": [
              { "$eq": [ "$$v0_u1_0_name", "$name" ] },
              { "$eq": [ "$$v1_u1_0_email", "$email" ] }
            ]
          }
        }
      }
    ],
    "as": "#c1_0"
  }
},
{ "$unwind": "$#c1_0" }

Composite Key Joins

You can join entities whose primary key is a composite key declared with the @EmbeddedId annotation. To learn how to define a composite key, see the Composite Primary Keys section of the Create Entities guide.

The examples in this section use the following Book and Review entities, which are both keyed on a publisherId component and a bookNo component. The Review entity references its related Book entity in its book field.

Each entity declares its key in a separate @Embeddable record. Declaring the key as a record rather than a class provides the equals() and hashCode() methods that Hibernate ORM requires to compare identifier values.

The following BookId record defines the Book entity's key:

package org.example;
import jakarta.persistence.Embeddable;
@Embeddable
public record BookId(long publisherId, long bookNo) {
}

The Book entity has the following definition:

package org.example;
import jakarta.persistence.EmbeddedId;
import jakarta.persistence.Entity;
import jakarta.persistence.Table;
@Entity(name = "Book")
@Table(name = "books")
public class Book {
    @EmbeddedId
    private BookId id;
    private String title;
    public Book(BookId id, String title) {
        this.id = id;
        this.title = title;
    }
    public Book() {
    }
    public BookId getId() {
        return id;
    }
    public void setId(BookId id) {
        this.id = id;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
}

The following ReviewId record defines the Review entity's key:

package org.example;
import jakarta.persistence.Embeddable;
@Embeddable
public record ReviewId(long publisherId, long bookNo) {
}

The Review entity has the following definition. The book field maps the association to the Book entity:

package org.example;
import jakarta.persistence.EmbeddedId;
import jakarta.persistence.Entity;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;
@Entity(name = "Review")
@Table(name = "reviews")
public class Review {
    @EmbeddedId
    private ReviewId id;
    private String comment;
    @ManyToOne
    private Book book;
    public Review(ReviewId id, Book book, String comment) {
        this.id = id;
        this.book = book;
        this.comment = comment;
    }
    public Review() {
    }
    public ReviewId getId() {
        return id;
    }
    public void setId(ReviewId id) {
        this.id = id;
    }
    public String getComment() {
        return comment;
    }
    public void setComment(String comment) {
        this.comment = comment;
    }
    public Book getBook() {
        return book;
    }
    public void setBook(Book book) {
        this.book = book;
    }
}

The following code inserts the documents that the examples in this section use:

var blueDoor = new Book(new BookId(10, 2), "The Blue Door");
var winterLight = new Book(new BookId(10, 3), "Winter Light");
var saltAndStone = new Book(new BookId(20, 1), "Salt and Stone");
session.persist(blueDoor);
session.persist(winterLight);
session.persist(saltAndStone);
session.persist(new Review(new ReviewId(30, 7), blueDoor, "Gripping from the first page."));
session.persist(new Review(new ReviewId(30, 8), blueDoor, "A slow but rewarding read."));
session.persist(new Review(new ReviewId(20, 1), saltAndStone, "Beautifully written."));
session.persist(new Review(new ReviewId(10, 3), saltAndStone, "Dense, but worth the effort."));
var blueDoor = new Book(new BookId(10, 2), "The Blue Door");
var winterLight = new Book(new BookId(10, 3), "Winter Light");
var saltAndStone = new Book(new BookId(20, 1), "Salt and Stone");
entityManager.persist(blueDoor);
entityManager.persist(winterLight);
entityManager.persist(saltAndStone);
entityManager.persist(new Review(new ReviewId(30, 7), blueDoor, "Gripping from the first page."));
entityManager.persist(new Review(new ReviewId(30, 8), blueDoor, "A slow but rewarding read."));
entityManager.persist(new Review(new ReviewId(20, 1), saltAndStone, "Beautifully written."));
entityManager.persist(new Review(new ReviewId(10, 3), saltAndStone, "Dense, but worth the effort."));

Composite Foreign Key Storage

A foreign key is the field or set of fields that one entity uses to store the identifier of an entity it references. The Hibernate ORM extension uses foreign key values to match documents on each side of a join.

When an association targets an entity that has a composite key, the Hibernate ORM extension stores the foreign key in the association field as a sub-document. The sub-document mirrors the components of the target entity's _id sub-document.

The Review entity produces documents in the following format, in which the book field holds the foreign key as a sub-document:

{
  "_id": { "bookNo": 7, "publisherId": 30 },
  "comment": "An excellent read.",
  "book": { "bookNo": 2, "publisherId": 10 }
}

The Hibernate ORM extension supports this layout for @ManyToOne and @OneToOne associations. The inverse side of an association, which you map with the mappedBy element, stores no foreign key fields.

Join a Composite Key Association

To join an association whose target entity has a composite key, use a join clause, as you would for an entity that has a single-field key.

The following example retrieves the identifier of each review alongside the identifier of the book that it reviews through an inner join on the Review entity's book field:

var compositeJoinResults = session.createQuery("select r.id, b.id, b.title from Review r join r.book b", Object[].class)
        .getResultList();
for (var row : compositeJoinResults) {
    System.out.println("Review: " + row[0] + ", Book: " + row[1] + ", Book Title: " + row[2]);
}
var compositeJoinResults = entityManager.createQuery("select r.id, b.id, b.title from Review r join r.book b", Object[].class)
        .getResultList();
for (var row : compositeJoinResults) {
    System.out.println("Review: " + row[0] + ", Book: " + row[1] + ", Book Title: " + row[2]);
}

The Winter Light book does not appear in the results because it has no reviews.

Because a composite key spans more than one field, the Hibernate ORM extension uses the let and pipeline form of the $lookup stage. The let option binds each foreign key component to a variable. The pipeline option compares those variables to the components of the target _id sub-document in a $expr operator:

{
  "$lookup": {
    "from": "books",
    "let": {
      "v0_r1_0_book_publisherId": "$book.publisherId",
      "v1_r1_0_book_bookNo": "$book.bookNo"
    },
    "pipeline": [
      {
        "$match": {
          "$expr": {
            "$and": [
              { "$eq": [ "$_id.publisherId", "$$v0_r1_0_book_publisherId" ] },
              { "$eq": [ "$_id.bookNo", "$$v1_r1_0_book_bookNo" ] }
            ]
          }
        }
      }
    ],
    "as": "#b1_0"
  }
},
{ "$unwind": "$#b1_0" }

You can also use a JOIN FETCH clause to load a composite key association in the same query as its parent, as described in the Join Fetch section of this guide.

Compare Whole Composite Identifiers

In an ON condition, you can compare two whole composite identifiers with the = operator. The Hibernate ORM extension decomposes the comparison into one equality check for each key component.

You can use a whole-identifier comparison in a left outer join, and you can combine it with additional conditions by using the AND operator, as in ON b.id = r.id AND b.title = r.comment.

The following example joins each book to the review that has a matching identifier:

var wholeIdResults = session.createQuery("select b.id, b.title, r.id from Book b join Review r on b.id = r.id", Object[].class)
        .getResultList();
for (var row : wholeIdResults) {
    System.out.println("Book Title: " + row[1] + ", Book: " + row[0] + ", Review: " + row[2]);
}
var wholeIdResults = entityManager.createQuery("select b.id, b.title, r.id from Book b join Review r on b.id = r.id", Object[].class)
        .getResultList();
for (var row : wholeIdResults) {
    System.out.println("Book Title: " + row[1] + ", Book: " + row[0] + ", Review: " + row[2]);
}

The Blue Door does not appear in the results because no review shares its identifier.

The Hibernate ORM extension translates the preceding join to the following stages:

{
  "$lookup": {
    "from": "reviews",
    "let": {
      "v0_b1_0__id_publisherId": "$_id.publisherId",
      "v1_b1_0__id_bookNo": "$_id.bookNo"
    },
    "pipeline": [
      {
        "$match": {
          "$expr": {
            "$and": [
              { "$eq": [ "$$v0_b1_0__id_publisherId", "$_id.publisherId" ] },
              { "$eq": [ "$$v1_b1_0__id_bookNo", "$_id.bookNo" ] }
            ]
          }
        }
      }
    ],
    "as": "#r1_0"
  }
},
{ "$unwind": "$#r1_0" }

Composite Key Join Limitations

Composite key joins have the following limitations:

  • You cannot apply the @JoinColumn or @JoinColumns annotation to an association whose target entity has a composite key. The Hibernate ORM extension derives the foreign key field names from the association name and throws a FeatureNotSupportedException when your application starts if you override them.

  • You cannot compare whole composite identifiers with an ordering operator such as > or < in an ON condition. Compare individual key components instead.

  • The Hibernate ORM extension does not support a @ManyToMany association when either entity has a composite key.

Range and Inequality ON Conditions

An ON condition can compare fields with range and inequality operators, such as < and >.

The following example joins the movies and comments collections to retrieve comments on movies released in 2015 that were posted after the movie's release date:

var nonEquijoinResults = session.createQuery("select m.title, c.name from Movie m join m.comments c on c.date > m.released where m.year = :y", Object[].class)
        .setParameter("y", 2015)
        .getResultList();
for (var row : nonEquijoinResults) {
    System.out.println("Title: " + row[0] + ", Commenter: " + row[1]);
}
var nonEquijoinResults = entityManager.createQuery("select m.title, c.name from Movie m join m.comments c on c.date > m.released where m.year = :y", Object[].class)
        .setParameter("y", 2015)
        .getResultList();
for (var row : nonEquijoinResults) {
    System.out.println("Title: " + row[0] + ", Commenter: " + row[1]);
}

The Hibernate ORM extension combines the association's key match and the ON condition into a single $expr operator, and uses the let and pipeline form of the $lookup stage:

{
  "$lookup": {
    "from": "comments",
    "let": {
      "v0_m1_0__id": "$_id",
      "v1_m1_0_released": "$released"
    },
    "pipeline": [
      {
        "$match": {
          "$expr": {
            "$and": [
              { "$eq": [ "$$v0_m1_0__id", "$movie_id" ] },
              { "$gt": [ "$date", "$$v1_m1_0_released" ] }
            ]
          }
        }
      }
    ],
    "as": "#c1_0"
  }
},
{ "$unwind": "$#c1_0" }

Additional Information

To learn more about performing other operations on your MongoDB data, see the Perform CRUD Operations or Specify a Query guide.

To learn more about using HQL and JPQL to run queries, see A Guide to Hibernate Query Language in the Hibernate ORM documentation.

Back
Datetime Functions in Queries
Next
Modify Query Results
Rate this page

On this page