see on GitHub

Session: Querying: How to Perform Dynamic Group By Query

Since RavenDB 4.0, the query optimizer supports dynamic group by queries and automatically creates auto map-reduce indexes.

You can create a dynamic query that does an aggregation by using the groupBy() method.

The supported aggregation operations are:

  • count
  • sum


Group By Single Field

List<CountryAndQuantity> orders = session.query(Order.class)
    .groupBy("ShipTo.Country")
    .selectKey("ShipTo.Country", "Country")
    .selectSum(new GroupByField("Lines[].Quantity", "OrderedQuantity"))
    .ofType(CountryAndQuantity.class)
    .toList();
from Orders
group by ShipTo.City
select ShipTo.City as Country, sum(Lines[].Quantity) as TotalQuantity

Group By Multiple Fields

List<CountByCompanyAndEmployee> results = session.query(Order.class)
    .groupBy("Employee", "Company")
    .selectKey("Employee", "EmployeeIdentifier")
    .selectKey("Company")
    .selectCount()
    .ofType(CountByCompanyAndEmployee.class)
    .toList();
from Orders
group by Employee, Company
select Employee as EmployeeIdentifier, Company, count()

Select Composite GroupBy Key

List<CountOfEmployeeAndCompanyPairs> orders = session.query(Order.class)
    .groupBy("Employee", "Company")
    .selectKey("key()", "EmployeeCompanyPair")
    .selectCount("Count")
    .ofType(CountOfEmployeeAndCompanyPairs.class)
    .toList();
from Orders 
group by Employee, Company
select key() as EmployeeCompanyPair, count()

Group By Array

By Array Values

In order to group by values of array, you need to use groupBy(array(...)). The following query will group by product field from Lines collection and calculate the count per ordered products. Underneath a fanout, an auto map-reduce index will be created to handle such query.

List<ProductsInfo> products = session.query(Order.class)
    .groupBy(array("Lines[].Product"))
    .selectKey("key()", "Products")
    .selectCount()
    .ofType(ProductsInfo.class)
    .toList();
from Orders 
group by Lines[].Product
select Lines[].Product, count()

Inside a single group by statement you can mix collection values and value of another property. That's supported by DocumentQuery only:

List<ProductInfo> results = session.advanced().documentQuery(Order.class)
    .groupBy("Lines[].Product", "ShipTo.Country")
    .selectKey("Lines[].Product", "Product")
    .selectKey("ShipTo.Country", "Country")
    .selectCount()
    .ofType(ProductInfo.class)
    .toList();
from Orders 
group by Lines[].Product, ShipTo.Country 
select Lines[].Product as Product, ShipTo.Country as Country, count()

Grouping by multiple values from the same collection is supported as well:

List<ProductInfo> results = session.query(Order.class)
    .groupBy(array("Lines[].Product"), array("Lines[].Quantity"))
    .selectKey("Lines[].Product", "Product")
    .selectKey("Lines[].Quantity", "Quantity")
    .selectCount()
    .ofType(ProductInfo.class)
    .toList();
from Orders 
group by Lines[].Product, Lines[].Quantity 
select Lines[].Product as Product, Lines[].Quantity as Quantity, count()

By Array Content

Another option is to group by array content. The reduction key will be calculated based on all values of a collection specified in GroupBy. The client API exposes the GroupByArrayContent extension method for that purpose.

List<ProductsInfo> results = session.query(Order.class)
    .groupBy(array("Lines[].Product"))
    .selectKey("key()", "Products")
    .selectCount()
    .ofType(ProductsInfo.class)
    .toList();
from Orders
group by array(Lines[].Product)
select key() as Products, count()

Grouping by array content and a value of another property is supported by DocumentQuery:

List<ProductsInfo> results = session.query(Order.class)
    .groupBy(array("Lines[].Product"), field("ShipTo.Country"))
    .selectKey("Lines[].Product", "Products")
    .selectKey("ShipTo.Country", "Country")
    .selectCount()
    .ofType(ProductsInfo.class)
    .toList();
from Orders 
group by array(Lines[].Product), ShipTo.Country 
select Lines[].Product as Products, ShipTo.Country as Country, count()

Grouping by multiple values from the same collection is also supported by DocumentQuery:

List<ProductsInfo> results = session.query(Order.class)
    .groupBy(array("Lines[].Product"), array("Lines[].Quantity"))
    .selectKey("Lines[].Product", "Products")
    .selectKey("Lines[].Quantity", "Quantities")
    .selectCount()
    .ofType(ProductsInfo.class)
    .toList();
from Orders 
group by array(Lines[].Product), array(Lines[].Quantity) 
select Lines[].Product as Products, Lines[].Quantity as Quantities, count()

Note

In order to use the above methods you need to add the following static import statement:

import static net.ravendb.client.documents.queries.GroupBy.array;
import static net.ravendb.client.documents.queries.GroupBy.field;