Spring Data MongoDB - Guide to the @Query Annotation

Introduction

If you have worked with Spring Data JPA for any length of time - you're probably acquainted with derived query methods:

@Repository
public interface BookRepository extends MongoRepository<Book, String> {
   List<Book> findByAuthor(String name);
}

They are a nifty and quick way to offload the burden of writing queries to Spring Data JPA by simply defining method names.

In this hypothetical scenario - we've defined a MongoRepository for a Book class, which has an attribute called author of type String.

Instead of implementing this method in a service implementing BookRepository - Spring Data JPA generates a query automatically given the name of the method. It'll generate a query that returns a list of all Book records, with a matching author.

Once the method is called with some input, the following request is made:

find using query: 
{ "author" : "Max Tegmark"} 
fields: Document{{}} 
for class: 
class com.example.demo.Book in collection: books

logging.level.org.springframework.data.mongodb.core.MongoTemplate=DEBUG

This is an extremely flexible and powerful feature of Spring Data JPA and it allows you to bootstrap queries without writing the queries themselves, or even implementing any handling logic in the back-end.

However, they become very difficult to create when complex queries are required:

public interface PropertyRepository extends MongoRepository<Property, String> {
   List<Property> findPropertiesByTransactionTypeAndPropertyType(@Param("transaction_type") TransactionType transactionType, @Param("property_type") PropertyType propertyType);
}

And this is for just two parameters. What happens when you want to create a query for 5 parameters?

Also, just how many method variations will you create?

This is the point when you'll most likely want to prefer to write your own queries. This is doable via the @Query annotation.

The @Query annotation is applied at the method-level in MongoRepository interfaces, and pertains to a single method. The language used inside of the annotation depends on your back-end. Naturally, for Mongo back-ends, you'll be writing native Mongo queries, though, @Query also supports relational databases, and accepts native queries for them, or the neutral JPQL (Java Persistence Query Language) that's translated automatically to the native queries of the database you're using.

When the annotated method is called - the query from within the @Query annotation fires and returns the results.

If you'd like to read more about writing relational/SQL queries, read our Spring Data JPA - Guide to the @Query Annotation!

Domain Model and Repository

Let's quickly define a Book model that we'll be using as a @Document for our repository. To properly showcase various operations, such as using Mongo's lt and gt operators, - we'll have a couple of different properties:

@Document(collection = "books")
public class Book {

    @Id
    private String id;
    private String name;
    private String author;
    private long pageNumber;
    private long publishedYear;

    // Getters, setters, constructor, toString()
}

MongoDB deals with IDs of type String or ObjectId. It's up to you to choose which one you'll use - and ObjectId can easily be converted to Strings and vice-versa so it doesn't make much of a difference.

In any case, let's define a simple BookRepository for this model:

public interface BookRepository extends MongoRepository<Book, String> {
}

It's currently empty, but it works just fine for CRUD operations, given the fact that MongoRepository is a descendant of the CrudRepository interface. Additionally, paging and sorting are supported out of the box!

In the upcoming sections, we'll take a look at the @Query annotation itself, as well as MongoDB's query structure, how to reference method parameters as well as sort and page.

Understanding the @Query Annotation

The @Query annotation is pretty simple and straightforward:

@Query("mongo query")
public List<Book> findBy(String param1, String param2);

Once the findBy() method is called, the results are returned. Keep in mind that during compile-time, Spring Boot doesn't know what type the query will return in advance. For instance, if it returns multiple results, and you only have a single expected return value - an exception will be thrown during runtime.

It's up to you to ensure that the query's response matches the return type of the method.

You can have fixed or dynamic queries here. For instance, you can simplify the method name from before and delegate the messy parameters to the @Query annotation:

@Query("query with param1, param2, param3")
List<Book> findAllActive();

@Query("query with param1, param2, param3")
List<Book> findBy(param1, param2, param3);

In the first example, we have a fixed set of parameters, such as always searching for active books even if the client doesn't specify that. This is an advantage over derived query methods since the method name is clean. Alternatively, you can supply parameters to the methods which can then be injected into the @Query annotation itself:

@Query("{'active':true}")
List<Book> findAll();

@Query("{'author' : ?0, 'category' : ?1}")
List<Book> findPositionalParameters(String author, String category);

@Query("{'author' : :#{#author}, 'category' : :#{#category}}")
List<Book> findNamedParameters(@Param("author") String author, @Param("category") String category);

For those who might not be fully acquainted with MongoDB's query structure, let's take a primer on those before delving into extracting method parameters and using them in queries!

MongoDB Query Structure

MongoDB has a pretty straightforward query structure, though, one different from SQL structures. If you haven't used MongoDB much before and if you're used to relational databases, it's a good idea to refresh your memory on these structures.

All Mongo queries take place between curly brackets:

{query}

The standard equality condition follows a simple pattern:

{ 
<field1> : <value1>, 
<field2> : <value2>, 
... 
}

For instance, we can query for our books as:

{ 
author : 'Max Tegmark', 
pageNumber : 568, 
... 
}

This query checks for all Book documents in the collection that fit both the author and pageNumber`. You can additionally throw in operators into the mix here:

{
author : 
    {
        $in : ['Max Tegmark', 'Ray Kurzweil']
    }
}

This query checks whether the author is any of the provided values. Some of the supported operators are $gt, $lt, $in, $nin, $or, $and, $nor, $not and $size, though there are quite a few and it's worth getting to know them. For instance, here's a query that searches for all documents by any of two authors, with a page count between 400 and 500, not released in 2018 and 2019:

{
author : { $in : ['Max Tegmark', 'Ray Kurzweil']},
pageNumber : { $gt : 400, $lt : 500},
publishedYear : {$nin : [2018, 2019]}
}

This is the majority of query knowledge you'll need for a good deal of queries, but don't skip out on actually getting to know MongoDB before seriously working with it. Additionally, you'll likely want to also work with aggregations.

If you'd like to read more about aggregations with MongoDB - read our Spring Data MongoDB - Guide to the @Aggregation Annotation

Referencing Named and Positional Method Parameters

With functional MongoDB knowledge under our belt - let's take a look at how we can reference method parameters. You can reference them either through their names, mixed with the @Param annotation and SpEL expressions, which is more verbose but more flexible, or, through positional arguments, which is typically the preferred approach due to simplicity:

@Query("{'author' : ?0, 'category' : ?1}")
List<Book> findPositionalParameters(String author, String category);

@Query("{'author' : :#{#author}, 'category' : :#{#category}}")
List<Book> findNamedParameters(@Param("author") String author, @Param("category") String category);

In the first approach, the first positional argument, ?0, corresponds to the first argument in the method, and the value of the argument will be used instead of ?0. This means that you have to keep track of the positions and not mix them up, otherwise, MongoDB will silently fail and just won't return the results, given the schema-flexibility, since you might as well have that property.

In the second approach, we're using SpEL expressions to match the provided parameters to the @Query parameters. You don't have to define them in any particular order since they'll be matched by name - not by position. Though, it still makes sense to keep a uniform position for API readability.

Let's define a simple endpoint in a REST controller to test this method out:

@RestController
public class HomeController {
    @Autowired
    private BookRepository bookRepository;

    @GetMapping("/find")
    public ResponseEntity main() {
        return ResponseEntity.
                ok(bookRepository.findPositionalParameters("Ray Kurzweil", "Fiction"));
    }
}

Once set up, let's send a curl request (or navigate to this URL via the browser):

$ curl localhost:8080/find

[ {
  "id" : "613fba633150f9788cd1858f",
  "name" : "Danielle: Chronicles of a Superheroine",
  "author" : "Ray Kurzweil",
  "pageNumber" : 472,
  "publishedYear" : 2019,
  "category" : "Fiction"

}                 

spring.jackson.serialization.INDENT_OUTPUT=true

Paging Results with Page and Pageable

Sorting and paging are supported out of the box, since MongoRepository extends the PagingAndSortingRepository! As usual, the process is to return a Page type, and supply a Pageable to the method itself:

@Query("{'author' : ?0}")
Page<Book> findBy(String author, Pageable pageable);

When calling the method, you need to supply a valid Pageable object, which can be created by making a page request:

@GetMapping("/find")
public ResponseEntity main() {
    //                  PageRequest.of(page, size)
    Pageable pageable = PageRequest.of(0, 2);

    return ResponseEntity.
            ok(bookRepository.findBy("Ray Kurzweil", pageable));
}

Here, we're creating a PageRequest for the first page (0-based indexing) with a size of 2 documents. If there are 10 fitting documents in the database, 5 pages will be returned, ranging from 0..4. You have to create

Let's print out the entire Page object that came back, where the content contains the query's results, and several other properties are also present pertaining to the page. This is where you can see how the results are organized within the page - i.e. the sorting, page size, page number, etc:

$ curl localhost:8080/find

{
  "content" : [ {
    "id" : "613fb60a3150f9788cd18589",
    "name" : "The Singularity Is Near",
    "author" : "Ray Kurzweil",
    "pageNumber" : 652,
    "publishedYear" : 2005,
    "category" : "Popular Science"
  }, {
    "id" : "613fba633150f9788cd1858f",
    "name" : "Danielle: Chronicles of a Superheroine",
    "author" : "Ray Kurzweil",
    "pageNumber" : 472,
    "publishedYear" : 2019,
    "category" : "Fiction"
  } ],
  "pageable" : {
    "sort" : {
      "sorted" : false,
      "unsorted" : true,
      "empty" : true
    },
    "offset" : 0,
    "pageNumber" : 0,
    "pageSize" : 2,
    "unpaged" : false,
    "paged" : true
  },
  "last" : true,
  "totalPages" : 1,
  "totalElements" : 2,
  "size" : 2,
  "number" : 0,
  "sort" : {
    "sorted" : false,
    "unsorted" : true,
    "empty" : true
  },
  "numberOfElements" : 2,
  "first" : true,
  "empty" : false
}        

If you'd like to just display the results, you can access the Stream<Book> of data and collect() it to a list:

@GetMapping("/find")
public ResponseEntity main() {
    //                  PageRequest.of(page, size)
    Pageable pageable = PageRequest.of(0, 2);
    return ResponseEntity.
            ok(bookRepository.findBy("Ray Kurzweil", pageable)
                    .get()
                    .collect(Collectors.toList()));
}

 curl localhost:8080/find
[ {
  "id" : "613fb60a3150f9788cd18589",
  "name" : "The Singularity Is Near",
  "author" : "Ray Kurzweil",
  "pageNumber" : 652,
  "publishedYear" : "2005-08-31T22:00:00Z",
  "category" : "Popular Science"
}, {
  "id" : "613fba633150f9788cd1858f",
  "name" : "Danielle: Chronicles of a Superheroine",
  "author" : "Ray Kurzweil",
  "pageNumber" : 472,
  "publishedYear" : 2019,
  "category" : "Fiction"
} ]

Pagination with Sorting

To extend this functionality with sorting, all you have to do is supply a Sort object to the PageRequest, stating by which property you'd like to sort and in which order:

@GetMapping("/find")
 public ResponseEntity main() {
    Pageable pageable = PageRequest.of(0, 3,
                                       Sort.by("name").ascending()
                                       .and(Sort.by("pageNumber").ascending()));

    return ResponseEntity.
            ok(bookRepository.findAll(pageable)
                    .get()
                    .collect(Collectors.toList()));
}

Here, we've sorted the results by ascending name and ascending page number. When sorting via multiple properties, you can chain any number of properties via and() and by supplying another Sort.by()!

The findAll() method is a default method present in the MongoRepository interface, and accepts both Sort and Pageable instances, and can be run without them as well. Here, we've taken advantage of that to query using the new Pageable:

$ curl localhost:8080/find

[ {
  "id" : "613fba633150f9788cd1858f",
  "name" : "Danielle: Chronicles of a Superheroine",
  "author" : "Ray Kurzweil",
  "pageNumber" : 472,
  "publishedYear" : 2019,
  "category" : "Fiction"
}, {
  "id" : "613fb6933150f9788cd1858e",
  "name" : "Our Mathematical Universe",
  "author" : "Max Tegmark",
  "pageNumber" : 432,
  "publishedYear" : 2014,
  "category" : "Popular Science"
}, {
  "id" : "613fb60a3150f9788cd18589",
  "name" : "The Singularity Is Near",
  "author" : "Ray Kurzweil",
  "pageNumber" : 652,
  "publishedYear" : 2005,
  "category" : "Popular Science"
} ]

The first property takes precedence here! Even though the second book has less pages than the first one, and we've sorted by ascending page number, sorting by name results in this order. If the order by name was ambiguous, the second property would make the cut.

Queries with Operators

All that being said, let's recreate the query from the beginning of the article:

{
author : { $in : ['Max Tegmark', 'Ray Kurzweil']},
pageNumber : { $gt : 400, $lt : 500},
publishedYear : {$nin : [2018, 2019]}
}

This is as easy as copy-pasting this query into the @Query annotation! Knowing we've got three books, and that one is 652 pages long, and that one of them was published in 2019 - we should expect only a single book to be returned here - "Our Mathematical Universe" by Max Tegmark!

Let's test if that's true:

@Query("{\n" +
        "author : { $in : ?0},\n" +
        "pageNumber : { $gt : ?1, $lt : ?2},\n" +
        "publishedYear : {$nin : ?3}\n" +
        "}")
List<Book> findBy(String[] authors, int pageNumLower, int pageNumUpper, int[] excludeYears);

Or, for a cleaner implementation:

@Query("{'author' : { $in : ?0}, 'pageNumber' : { $gt : ?1, $lt : ?2},'publishedYear' : {$nin : ?3}}")
List<Book> findBy(String[] authors, int pageNumLower, int pageNumUpper, int[] excludeYears);

Let's update the endpoint and provide some data:

@GetMapping("/find")
public ResponseEntity main() {
    return ResponseEntity.
            ok(bookRepository.findBy(
                    new String[]{"Ray Kurzweil", "Max Tegmark"}, // Authors
                    400,                                         // Lower pageNumber bound
                    500,                                         // Upper pageNumber bound
                    new int[]{2018, 2019}));                     // Exclusion years
    }

And when we send a request to it:

$ curl localhost:8080/find

[{
  "id" : "613fb6933150f9788cd1858e",
  "name" : "Our Mathematical Universe",
  "author" : "Max Tegmark",
  "pageNumber" : 432,
  "publishedYear" : 2014,
  "category" : "Popular Science"
}]

Like clockwork.

Conclusion

In this guide, we've taken a look at the @Query annotation in the context of Spring Data MongoDB.

The annotation allows you to define your own queries, native and JPQL, for various databases, relational and non-relational. We've opted to use native Mongo queries to interact with a non-relational database. After defining a model and a repository for it, we've explored the query structure used by MongoDB, and how the @Query annotation works in general. This was followed by referencing named and positional method parameters, paging and sorting query results, as well as how to use MongoDB operators to construct more complex queries!