Use limit and skip in MongoRepository<Customer,String>

Question

We are working on a project to get data from mongoDB. We have created repository class as below

@Repository
public interface CustomerRepository extends MongoRepository<Customer,String>{
     List<Customer> customers = findByCustomerId(final String customerId);
}

We are looking to add skip/offset and limit parameters to be used as part of findByCustomerId method. where limit is used to define number of records returned and skip/offset defines the number of records after which we need to get the records.

Please help how we can get this implemented in best way using MongoRepository.

Batchelor

Use "$skip" only if you want slow queries – Hawsehole

Answer 1

There are two ways to do this.

1. Use of @Aggregation annotation as mentioned in this answer. https://mcmap.net/q/1330176/-how-to-use-limit-and-skip-in-spring-data-mongodb-repository-duplicate

For example:

@Repository
public interface CustomerRepository extends MongoRepository<Customer,String>{

  @Aggregation(pipeline = {
    "{ '$match': { 'customerId' : ?0 } }", 
    "{ '$sort' : { 'customerId' : 1 } }", 
    "{ '$skip' : ?1 }", 
    "{ '$limit' : ?2 }"
  })
  List<Customer> findByCustomerId(final String customerId, int skip, int limit);

  @Aggregation(pipeline = {
    "{ '$match': { 'customerId' : ?0 } }", 
    "{ '$sort' : { 'customerId' : 1 } }", 
    "{ '$skip' : ?1 }"
  })
  Page<Customer> findCustomers(final String customerId, int skip, Pageable pageable);

}

The $match operator's query might need to be modified so that it better reflects the condition that needs to be satisfied by the matching documents.

2. Use Pageable argument in the query method and supply the PageRequest from the layer that calls the Repository method as shown in this answer. https://mcmap.net/q/557309/-query-with-sort-and-limit-in-spring-repository-interface

For the code snippet in the question this then becomes.

@Repository
public interface CustomerRepository extends MongoRepository<Customer,String> {

  Page<Customer> findByCustomerId(final String customerId, Pageable pageable);

}

// -------------------------------------------------------
// Call the repository method from a service
@Service
public class CustomerService {

  private final CustomerRepository customerRepository;

  public CustomerService(CustomerRepository customerRepository) {
    this.customerRepository = customerRepository;
  }

  public List<Customer> getCustomers(String customerId, int skip, int limit) {
    // application-specific handling of skip and limit arguments
    int page = 1; // calculated based on skip and limit values
    int size = 5; // calculated based on skip and limit values
    Page<Customer> page = customerRepository.findByCustomerId(customerId, 
                 PageRequest.of(page, size, Sort.Direction.ASC, "customerId"));
    List<Customer> customers = page.getContent();

    /*
    Here, the query method will retrieve 5 documents from the second 
    page.
    It skips the first 5 documents in the first page with page index 0. 
    This approach requires calculating the page to retrieve based on 
    the application's definition of limit/skip.
    */
    return Collections.unmodifiableList(customers);
  }
}

The aggregation approach is more useful. If the result is limited to a few documents then the query method can return List<Customer>. If there a lot of documents then the query method can be modified to use Pageable argument that returns Page<Customer> to page over the documents.

Refer to both Spring Data and MongoDB documentation.

https://docs.spring.io/spring-data/mongodb/docs/3.2.10/reference/html/#mongo.repositories

https://docs.spring.io/spring-data/mongodb/docs/3.2.10/reference/html/#mongodb.repositories.queries.aggregation

https://docs.spring.io/spring-data/mongodb/docs/3.2.10/api/org/springframework/data/mongodb/repository/Aggregation.html

https://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/domain/Pageable.html

https://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/domain/PageRequest.html

MongoDB Aggregation - https://www.mongodb.com/docs/manual/meta/aggregation-quick-reference/

Dynamic Queries

Custom Spring Data repository implementation along with use of MongoTemplate should help in implementing dynamic queries.

Custom Repositories - https://docs.spring.io/spring-data/mongodb/docs/3.2.10/reference/html/#repositories.custom-implementations

MongoTemplate - https://docs.spring.io/spring-data/mongodb/docs/3.2.10/api/org/springframework/data/mongodb/core/MongoTemplate.html

Retrieval

Can we modify the match statement to include customerId only when it is not null. Case 1: When we get customerId for example as 1234 then query should return customers with customerID 1234. Case 2: When customerId is null then the query should return all customers. Can we achieve this using above query? Please suggest – Batchelor

@PrasadReddy if the queries are dynamic then the approach should be to use a custom repository along with MongoTemplate. The updated answer refers to the documentation for it. – Retrieval

I added something like below @Aggregation(pipeline = { "{ '$match': { 'customerId' : ?0, '$exists' : true } }", "{ '$sort' : { 'customerId' : 1 } }", "{ '$skip' : ?1 }" }) But I am getting exception like command failed with error unknown top level operator $exists. Am I missing something or is this approach itself is wrong. Please help @Retrieval – Batchelor

Answer 2

A simple use case is to use a custom repository with the Query and SimpleMongoRepository classes.

CustomerRepository.java

@Repository
public interface CustomerRepository extends ResourceRepository<Customer, String> {
}

ResourceRepository.java

@NoRepositoryBean
public interface ResourceRepository<T, I> extends MongoRepository<T, I> {

    Page<T> findAll(Query query, Pageable pageable);
}

ResourceRepositoryImpl.java

@SuppressWarnings("rawtypes")
public class ResourceRepositoryImpl<T, I> extends SimpleMongoRepository<T, I> implements ResourceRepository<T, I> {

    private MongoOperations mongoOperations;

    private MongoEntityInformation entityInformation;

    public ResourceRepositoryImpl(final MongoEntityInformation entityInformation, final MongoOperations mongoOperations) {
        super(entityInformation, mongoOperations);

        this.entityInformation = entityInformation;
        this.mongoOperations = mongoOperations;
    }

    @Override
    public Page<T> findAll(final Query query, final Pageable pageable) {
        Assert.notNull(query, "Query must not be null!");

        long total = mongoOperations.count(query, entityInformation.getJavaType(), entityInformation.getCollectionName());
        List<T> content = mongoOperations.find(query.with(pageable), entityInformation.getJavaType(), entityInformation.getCollectionName());

        return new PageImpl<T>(content,pageable,total);
    }
}

CustomerService.java

@RequiredArgsConstructor
@Service
public class CustomerService {

   private final CustomerRepository repository;

    /**
     * @param customerId
     * @param limit the size of the page to be returned, must be greater than 0.
     * @param page zero-based page index, must not be negative.
     * @return Page of {@link Customer}
     */
    public Page<Customer> getCustomers(String customerId, int limit, int page) {
        Query query = new Query();
        query.addCriteria(Criteria.where("customerId").is(customerId));
        return repository.findAll(query, PageRequest.of(page, limit, Sort.by(Sort.Direction.ASC, "customerId")));
    }

    public List<Customer> getCustomersList(String customerId, int limit, int page) {
        Page<Customer> customerPage = getCustomers(customerId, limit, page);
        return customerPage.getContent();
    }
}

A reference with specific criteria: https://dzone.com/articles/advanced-search-amp-filtering-api-using-spring-dat

Data

Answer 3

I have used the Aggregation query with $skip and $limit, it works fine and is quite useful when you need to Paginate a complex piece of a query result. For simpler queries, I use the spring mongo template which takes a Query object. The query object takes a Pageable object where you define a page number and page size with a sorting option.

Criteria criterion = Criteria.where("field").is("value");//build your criteria here.
Query query = new Query(criterion);

Sort fieldSorting = Sort.by(Sort.Direction.DESC, "sortField"); // sort field 
        
int pageNo = 1; //which page you want to fetch. NoOfPages = TotalRecords/PageZie 
int pagesize = 10; // no of records per page
Pageable pageable = PageRequest.of(pageNo, pagesize, fieldSorting); // define your page

mongoTemplate.find(query.with(pageable), Object.class); // provide appropriate DTO class to map.

For mongo DB aggregation options - https://www.mongodb.com/docs/manual/reference/operator/aggregation/limit/ https://www.mongodb.com/docs/manual/reference/operator/aggregation/skip/

Sweater

Answer 4

Another (maybe simpler) approach for limiting the results of the query is adding the filters in the method declaration when using MongoRepository. Both the keywords top and first can be used to reach this goal, specifying also the amount of results desired (or by omitting it, obtaining thus just one result).

The code below is an example, available in the docs.spring.io documentation for MongoRepositories (link below).

User findFirstByOrderByLastnameAsc();

User findTopByOrderByAgeDesc();

Page<User> queryFirst10ByLastname(String lastname, Pageable pageable);

Slice<User> findTop3ByLastname(String lastname, Pageable pageable);

List<User> findFirst10ByLastname(String lastname, Sort sort);

List<User> findTop10ByLastname(String lastname, Pageable pageable);

You can also apply pagination to your query (more details in the documentation).

SOME EXTRA INFORMATION ABOUT SORTING:

As the other answers also gave some insight about the sorting, I would like to bring other options in this regard.

If your method will always sort the results in the same way, the sorting can be made by using the OrderBy keyword in your method declaration, followed by Asc or Desc depending on your use-case.

List<User> findFirst10ByLastnameOrderByAgeAsc(String lastname);

List<User> findFirst10ByLastnameOrderByAgeDesc(String lastname);

If you would like to sort your results dynamically, you can use the Sort argument on your method and provide.

List<User> findFirst10ByLastname(String lastname, Sort sort);

As an example, providing Sort.by(DESC, "age") in the method call will define { age : -1 } as the sort for the query.

References:

https://docs.spring.io/spring-data/mongodb/docs/3.2.10/reference/html/#repositories.query-methods

Horseflesh

Answer 5

Instead of returning List, I've tried with Optional, and it works. In this example, it returns newest record if it exists :

@Repository
public interface IoTTriggerDao extends MongoRepository<IotDECTrigger, String> {

@Query(value = """
    {
       "$and":
       [
          { 'dec._id' : ?0, 'active': false, 'runSingleTime': true },
          { 'processing' : false, 'ended': true}
       ]
    }
    """, sort = "{ 'activeFromDateEpoch' : -1 }")
Optional<IotDECTrigger> getDECSingleTimeFinishedTrigger(String decId);

}

Selwyn