Skip to main content

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 LINQ GroupBy() method or group by into syntax.

The supported aggregation operations are:

  • Count
  • Sum

<br />

Group By Single Field​

var results = (from o in session.Query<Order>()
      group o by o.ShipTo.Country
      into g
      select new
      {
          Country = g.Key,
          OrderedQuantity = g.Sum(order => order.Lines.Sum(line => line.Quantity))
      })
  .ToList();

Group By Multiple Fields​

var results = session.Query<Order>()
  .GroupBy(x => new
  {
      x.Employee,
      x.Company
  })
  .Select(x => new
  {
      EmployeeIdentifier = x.Key.Employee,
      x.Key.Company,
      Count = x.Count()
  })
  .ToList();

Select Composite GroupBy Key​

var results = session.Query<Order>()
  .GroupBy(x => new EmployeeAndCompany
  {
      Employee = x.Employee,
      Company = x.Company
  })
  .Select(x => new CountOfEmployeeAndCompanyPairs
  {
      EmployeeCompanyPair = x.Key,
      Count = x.Count()
  })
  .ToList();

Group By Array​

By Array Values​

In order to group by values of array, you need to use GroupByArrayValues. The following query will group by Product property 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.

var results = session.Query<Order>()
  .GroupByArrayValues(x => x.Lines.Select(y => y.Product))
  .Select(x => new
  {
      Count = x.Count(),
      Product = x.Key
  })
  .ToList();

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

var results = session.Advanced.DocumentQuery<Order>()
  .GroupBy("Lines[].Product", "ShipTo.Country")
  .SelectKey("Lines[].Product", "Product")
  .SelectKey("ShipTo.Country", "Country")
  .SelectCount()
  .OfType<ProductInfo>()
  .ToList();

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

var results = session.Query<Order>()
  .GroupByArrayValues(x => x.Lines.Select(y => new
  {
      y.Product,
      y.Quantity
  }))
  .Select(x => new ProductInfo
  {
      Count = x.Count(),
      Product = x.Key.Product,
      Quantity = x.Key.Quantity
  })
  .ToList();

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.

var results = session.Query<Order>()
  .GroupByArrayContent(x => x.Lines.Select(y => y.Product))
  .Select(x => new ProductsInfo
  {
      Count = x.Count(),
      Products = x.Key
  })
  .ToList();

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

var results = session.Advanced.DocumentQuery<Order>()
  .GroupBy(("Lines[].Product", GroupByMethod.Array), ("ShipTo.Country", GroupByMethod.None))
  .SelectKey("Lines[].Product", "Products")
  .SelectKey("ShipTo.Country", "Country")
  .SelectCount()
  .OfType<ProductsInfo>()
  .ToList();

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

var results = session.Advanced.DocumentQuery<Order>()
  .GroupBy(("Lines[].Product", GroupByMethod.Array), ("Lines[].Quantity", GroupByMethod.Array))
  .SelectKey("Lines[].Product", "Products")
  .SelectKey("Lines[].Quantity", "Quantities")
  .SelectCount()
  .OfType<ProductsInfo>()
  .ToList();

In order to use the above extension methods you need to add the following using statement:

using Raven.Client.Documents;

Sorting​

Results of dynamic group by queries can be sorted by an aggregation function used in the query. As the available aggregation operations are Count and Sum you can use them to order the results.

By Count​

var results = session.Query<Order>()
  .GroupBy(x => x.Employee)
  .Select(x => new
  {
      Employee = x.Key,
      Count = x.Count()
  })
  .OrderBy(x => x.Count)
  .ToList();

By Sum​

var results = session.Query<Order>()
  .GroupBy(x => x.Employee)
  .Select(x => new
  {
      Employee = x.Key,
      Sum = x.Sum(y => y.Freight)
  })
  .OrderBy(x => x.Sum)
  .ToList();