Paging Query Results

The queries below will return all the results available.

List<Product> results = session
    .query(Product.class, Products_ByUnitsInStock.class)
    .whereGreaterThan("UnitsInStock", 10)
    .toList();

public static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
    public Products_ByUnitsInStock() {
        map = "docs.Products.Select(product => new {" +
            "    UnitsInStock = product.UnitsInStock" +
            "})";
    }
}

from index "Products/ByUnitsInStock"
where UnitsInStock > 10

Basic paging:

Let's assume that our page size is 10, and we want to retrieve the 3rd page. To do this, we need to issue following query:

List<Product> results = session
    .query(Product.class, Products_ByUnitsInStock.class)
    .whereGreaterThan("UnitsInStock", 10)
    .skip(20) // skip 2 pages worth of products
    .take(10) // take up to 10 products
    .toList(); // execute query

public static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
    public Products_ByUnitsInStock() {
        map = "docs.Products.Select(product => new {" +
            "    UnitsInStock = product.UnitsInStock" +
            "})";
    }
}

from index "Products/ByUnitsInStock"
where UnitsInStock > 10
limit 20, 10 // skip 20, take 10

Find total results count when paging:

While paging, you sometimes need to know the exact number of results returned from the query. The Client API supports this explicitly:

Reference<QueryStatistics> stats = new Reference<QueryStatistics>();

List<Product> results = session
    .query(Product.class, Products_ByUnitsInStock.class)
    .statistics(stats)
    .whereGreaterThan("UnitsInStock", 10)
    .skip(20)
    .take(10)
    .toList();

int totalResults = stats.value.getTotalResults();

public static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
    public Products_ByUnitsInStock() {
        map = "docs.Products.Select(product => new {" +
            "    UnitsInStock = product.UnitsInStock" +
            "})";
    }
}

from index "Products/ByUnitsInStock"
where UnitsInStock > 10
limit 20, 10 // skip 20, take 10

While the query will return with just 10 results, totalResults will hold the total number of matching documents.

Better performance:

It is recommended to explicitly set a page size when making a query that is expected to generate a significant number of results. This practice has several benefits:

• Optimizes bandwidth usage by reducing data transfer between the server and client.
• Prevents delays in response times caused by sending too much data over the network.
• Avoids high memory consumption when dealing with numerous documents.
• Ensures a more manageable user experience by not overwhelming users with massive datasets at once.

Performance hints:

• By default, if the number of returned results exceeds 2048, the server will issue a "Page size too big" notification (visible in the Studio) with information about the query.

• This threshold can be customized by modifying the value of the PerformanceHints.MaxNumberOfResults configuration key.

• As suggested by the hint, you may consider using Streaming query results instead of paging.

Performance Hint

• The QueryStatistics object contains the TotalResults property,
  which represents the total number of matching documents found in the query results.

• The QueryStatistics object also contains the SkippedResults property.
  Whenever this property is greater than 0, that implies the server has skipped that number of results from the index.

• The server will skip duplicate results internally in the following two scenarios:

  1. When making a Projection query with Distinct.

  2. When querying a Fanout index.

• In those cases:

  • The SkippedResults property from the stats object will hold the count of skipped (duplicate) results.

  • The TotalResults property will be invalidated -
    it will Not deduct the number of skipped results from the total number of results.

• In order to do proper paging in those scenarios:
  include the SkippedResults value when specifying the number of documents to skip for each page using:
  (currentPage * pageSize) + SkippedResults.

Examples

A projection query with Distinct:

List<Product> results;
int pageNumber = 0;
int pageSize = 10;
int skippedResults = 0;
Reference<QueryStatistics> stats = new Reference<>();

do {

    results = session
        .query(Product.class, Products_ByUnitsInStock.class)
        .statistics(stats)
        .skip((pageNumber * pageSize) + skippedResults)
        .take(pageSize)
        .whereGreaterThan("UnitsInStock", 10)
        .distinct()
        .toList();

    skippedResults += stats.value.getSkippedResults();
    pageNumber++;
} while (results.size() > 0);

public static class Products_ByUnitsInStock extends AbstractIndexCreationTask {
    public Products_ByUnitsInStock() {
        map = "docs.Products.Select(product => new {" +
            "    UnitsInStock = product.UnitsInStock" +
            "})";
    }
}

from index "Products/ByUnitsInStock"
where UnitsInStock > 10
select distinct *
limit 0, 10  // First loop will skip 0, take 10, etc.

Querying a Fanout index:

List<Order> results;
int pageNumber = 0;
int pageSize = 50;
int skippedResults = 0;
Reference<QueryStatistics> stats = new Reference<>();

do {
    results = session
        .query(Order.class, Order_ByOrderLines_ProductName.class)
        .statistics(stats)
        .skip((pageNumber * pageSize) + skippedResults)
        .take(pageSize)
        .toList();

    skippedResults += stats.value.getSkippedResults();
    pageNumber++;
} while (results.size() > 0);

public static class Orders_ByOrderLines_ProductName extends AbstractIndexCreationTask {
    public Orders_ByOrderLines_ProductName() {
        map = "docs.Orders.SelectMany(order => order.Lines, (order, line) => new {" +
            "    Product = line.ProductName " +
            "})";
    }
}

from index "Order/ByOrderLines/ProductName"
limit 0, 50  // First loop will skip 0, take 50, etc.