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

Modify Query Results

Overview

In this guide, you can learn how to sort, skip, limit, and group the results of a query by using the MongoDB Extension for Hibernate ORM.

Sort Results

You can sort query results by the value of a specified field by using the order by clause in your query statement. By default, the order by clause sorts documents in ascending order. To sort documents in descending order, append the desc keyword to the field name.

The following example retrieves matching documents from the sample_mflix.movies collection and sorts them by the year field in descending order:

var sortResult = session.createQuery("from Movie where title = :t order by year desc", Movie.class)
                        .setParameter("t", "Cinderella")
                        .getResultList();
for (var m : sortResult) {
    System.out.println("Title: " + m.getTitle() + ", Year: " + m.getYear());
}

Skip Results

You can skip a specified number of documents in your query results by chaining the setFirstResult() method to the query. Pass the number of initial documents to skip as an argument to this method.

The following example runs the same query as the preceding example but skips the first matching document in the results:

var skipResult = session.createQuery("from Movie where title = :t order by year desc", Movie.class)
                        .setParameter("t", "Cinderella")
                        .setFirstResult(1)
                        .getResultList();
for (var m : skipResult) {
    System.out.println("Title: " + m.getTitle() + ", Year: " + m.getYear());
}

Limit Results

You can limit the number of documents that your query returns by chaining the setMaxResults() method to the query. Pass the maximum number of documents to return as an argument to this method.

The following example runs the same query as the sort example but returns up to two matching documents in the results:

var limitResult = session.createQuery("from Movie where title = :t order by year desc", Movie.class)
                         .setParameter("t", "Cinderella")
                         .setMaxResults(2)
                         .getResultList();
for (var m : limitResult) {
    System.out.println("Title: " + m.getTitle() + ", Year: " + m.getYear());
}

Alternatively, you can use a limit clause in your HQL or JPQL statement to restrict the number of documents that your query returns, as shown in the following code:

var limitClauseResult = session.createQuery("from Movie where title = :t order by year desc limit 2", Movie.class)
                               .setParameter("t", "Cinderella")
                               .getResultList();

Group Results

You can collapse documents that share a value into a single result row by using the group by clause in your query statement. The Hibernate ORM extension translates the group by clause to a MongoDB $group stage, in which each grouping key becomes a sub-field of the _id field.

Important

Grouping Key Requirement

Every field that you reference in the select, having, or order by clause of a grouped query must be a grouping key or part of one. If you reference a field that the $group stage does not produce, the Hibernate ORM extension throws a FeatureNotSupportedException.

A grouped query also cannot use the distinct keyword.

Group by Field Values

To group results by the value of one or more fields, list the fields in the group by clause. To group by multiple fields, separate the field names with commas.

This example performs the following operations:

  • Retrieves documents that have a title value of "Hairspray" from the sample_mflix.movies collection.

  • Groups the movies by the year field, which returns one row for each distinct release year.

  • Sorts the results by their year values in ascending order.

var groupResult = session.createQuery(
                "select year from Movie where title = :title group by year order by year",
                Integer.class)
        .setParameter("title", "Hairspray")
        .getResultList();
for (var year : groupResult) {
    System.out.println("Year: " + year);
}

The Hibernate ORM extension translates this query to the following pipeline. The where clause becomes a $match stage that runs before the grouping. The order by clause becomes a $sort stage that sorts on the grouping key:

[
  { "$match": { "title": { "$eq": "Hairspray" } } },
  { "$group": { "_id": { "year": "$year" } } },
  { "$sort": { "_id.year": 1 } },
  { "$project": { "_id#year": "$_id.year", "_id": 0 } }
]

Group by a Computed Expression

A grouping key can be a computed expression instead of a field reference. The Hibernate ORM extension stores the expression in a generated _id sub-field named k<n>. When you write the same expression in the select or having clause, the Hibernate ORM extension resolves it to that sub-field rather than recomputing it.

The Hibernate ORM extension matches an expression to a grouping key only when you write the two identically. For example, a key of year + 1 does not match an occurrence of 1 + year.

You can also order by a computed expression key. Write the expression in the order by clause exactly as you write it in the group by clause.

The following example groups documents that have a title of "Hairspray" by the decade in which they were released and sorts the results in ascending order:

var groupExpressionResult = session.createQuery(
                "select year / 10 * 10 from Movie where title = :title group by year / 10 * 10 order by year / 10 * 10",
                Integer.class)
        .setParameter("title", "Hairspray")
        .getResultList();
for (var decade : groupExpressionResult) {
    System.out.println("Decade: " + decade);
}

The Hibernate ORM extension translates this query to the following pipeline, in which the $sort and $project stages read the grouping key from _id.k0 instead of recomputing the expression:

[
  { "$match": { "title": { "$eq": "Hairspray" } } },
  {
    "$group": {
      "_id": {
        "k0": {
          "$multiply": [
            { "$toInt": { "$divide": [ "$year", 10 ] } },
            10
          ]
        }
      }
    }
  },
  { "$sort": { "_id.k0": 1 } },
  { "$project": { "#c_1": "$_id.k0", "_id": 0 } }
]

You can also select a computed expression that contains a grouping key. In this case, the Hibernate ORM extension groups by the key and computes the expression from the key in the $project stage.

To learn more about computed expressions, see the Use Computed Expressions section in the Specify a Query guide.

Filter Grouped Results

To remove groups from the results, use the having clause. The Hibernate ORM extension uses the having clause to filter on the grouping key. You can combine multiple conditions by using the and and or operators.

This example performs the following operations:

  • Retrieves the "Hairspray" movies from the sample_mflix.movies collection.

  • Groups the movies by the year field, which returns one row for each distinct release year.

  • Filters the groups to only include year values greater than 2000.

  • Sorts the results by year in ascending order.

var havingResult = session.createQuery(
                "select year from Movie where title = :title group by year having year > 2000 order by year",
                Integer.class)
        .setParameter("title", "Hairspray")
        .getResultList();
for (var year : havingResult) {
    System.out.println("Year: " + year);
}

The Hibernate ORM extension translates this query to the following pipeline. The where clause becomes a $match stage that runs before the grouping. The having clause becomes a second $match stage that runs after the grouping and filters on the grouping key:

[
  { "$match": { "title": { "$eq": "Hairspray" } } },
  { "$group": { "_id": { "year": "$year" } } },
  { "$match": { "_id.year": { "$gt": 2000 } } },
  { "$sort": { "_id.year": 1 } },
  { "$project": { "_id#year": "$_id.year", "_id": 0 } }
]

Additional Information

To learn more about performing other operations on your MongoDB data, see the Perform CRUD Operations 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
Join Entities Across Collections
Next
Perform Native Queries
Rate this page

On this page