Query for distinct results



Sample query with Distinct

// Get a sorted list without duplicates:
// =====================================

List<String> countries = session
    .query(Order.class)
    .orderBy("ShipTo.Country")
    .selectFields(String.class, "ShipTo.Country")
     // Call 'distinct' to remove duplicates from results
     // Items wil be compared based on field 'Country' that is specified in the above 'selectFields' 
    .distinct()
    .toList();
    
// Running this on the Northwind sample data
// will result in a sorted list of 21 countries w/o duplicates.
from "Orders"
order by ShipTo.Country
select distinct ShipTo.Country

Paging with distinct

A special approach must be used when calling distinct() while paging.
Please read the dedicated article about paging through tampered results.

Count with distinct

Use count() in combination with distinct() to get the number of unique items.
Similar to toList(), count() triggers query execution on the server-side.

// Count the number of unique countries:
// =====================================

int numberOfCountries = session
    .query(Order.class)
    .selectFields(String.class, "ShipTo.Country")
    .distinct()
    .count();
    
// Running this on the Northwind sample data,
// will result in 21, which is the number of unique countries.
// This RQL is intended for use when issuing a Raw Query from the client API.
// Running directly from the Studio will not display the number of results.
// ========================================================================

from "Orders"
select distinct ShipTo.Country
limit 0, 0

Performance cost and an alternative approach

  • Please keep in mind that using count() with distinct() might not be efficient for large sets of data
    as it requires scanning all index results to find unique values.

  • Getting the distinct items' count can also be achieved by creating a Map-Reduce index that will aggregate data by the field for which distinct count results are needed.

  • Using a Map-Reduce index is more efficient since computations are done during indexing time and not at query time. The entire dataset is indexed once, whereafter the aggregated value is always kept up to date as indexing will occur only for new/modified data.

Map-Reduce index example:

Index definition:

public static class Orders_ByShipToCountry extends AbstractIndexCreationTask {

public Orders_ByShipToCountry() {

    // The map phase indexes the country listed in each order document
    // CountryCount is assigned with 1, which will be aggregated in the reduce phase
    map = "docs.Orders.Select(order => new { " +
          "    Country = order.ShipTo.Country, " +
          "    CountryCount = 1 " +
          "})";

    // The reduce phase will group the Country results and aggregate the CountryCount
    reduce = "results.GroupBy(result => result.Country).Select(g => new { " +
             "    Country = g.Key, " +
             "    CountryCount = Enumerable.Sum(g, x => x.CountryCount) " +
             "})";
}

public static class Result {
    private String country;
    private int countryCount;

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    public int getCountryCount() {
        return countryCount;
    }

    public void setCountryCount(int countryCount) {
        this.countryCount = countryCount;
    }
}
}

Query the index:

// Query the map-reduce index defined above
try (IDocumentSession session = DocumentStoreHolder.store.openSession()) {
    Orders_ByShipToCountry.Result queryResult = session
        .query(Orders_ByShipToCountry.Result.class, Orders_ByShipToCountry.class)
        .toList();

    // The resulting list contains all index-entry items where each entry represents a country. 
    // The size of the list corresponds to the number of unique countries.
    int numberOfUniqueCountries = queryResult.length;
}
from index "Orders/ByShipToCountry"

Combining faceted queries with Map-Reduce:

Faceted queries can be used together with a map-reduce index as another alternative approach.
See a C# example for Implementing a count(distinct) query in RavenDB.