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
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();
var results = await (from o in asyncSession.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))
})
.ToListAsync();
from Orders
group by ShipTo.City
select ShipTo.City as Country, sum(Lines[].Quantity) as TotalQuantity
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();
var results = await asyncSession.Query<Order>()
.GroupBy(x => new
{
x.Employee,
x.Company
})
.Select(x => new
{
EmployeeIdentifier = x.Key.Employee,
x.Key.Company,
Count = x.Count()
})
.ToListAsync();
from Orders
group by Employee, Company
select Employee as EmployeeIdentifier, Company, count()
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();
var results = await asyncSession.Query<Order>()
.GroupBy(x => new EmployeeAndCompany
{
Employee = x.Employee,
Company = x.Company
})
.Select(x => new CountOfEmployeeAndCompanyPairs
{
EmployeeCompanyPair = x.Key,
Count = x.Count()
})
.ToListAsync();
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 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();
var results = await asyncSession.Query<Order>()
.GroupByArrayValues(x => x.Lines.Select(y => y.Product))
.Select(x => new
{
Count = x.Count(),
Product = x.Key
})
.ToListAsync();
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:
var results = session.Advanced.DocumentQuery<Order>()
.GroupBy("Lines[].Product", "ShipTo.Country")
.SelectKey("Lines[].Product", "Product")
.SelectKey("ShipTo.Country", "Country")
.SelectCount()
.OfType<ProductInfo>()
.ToList();
var results = await asyncSession.Advanced.AsyncDocumentQuery<Order>()
.GroupBy("Lines[].Product", "ShipTo.Country")
.SelectKey("Lines[].Product", "Product")
.SelectKey("ShipTo.Country", "Country")
.SelectCount()
.OfType<ProductInfo>()
.ToListAsync();
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:
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();
var results = await asyncSession.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
})
.ToListAsync();
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.
var results = session.Query<Order>()
.GroupByArrayContent(x => x.Lines.Select(y => y.Product))
.Select(x => new ProductsInfo
{
Count = x.Count(),
Products = x.Key
})
.ToList();
var results = await asyncSession.Query<Order>()
.GroupByArrayContent(x => x.Lines.Select(y => y.Product))
.Select(x => new ProductsInfo
{
Count = x.Count(),
Products = x.Key
})
.ToListAsync();
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
:
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();
var results = await asyncSession.Advanced.AsyncDocumentQuery<Order>()
.GroupBy(("Lines[].Product", GroupByMethod.Array), ("ShipTo.Country", GroupByMethod.None))
.SelectKey("Lines[].Product", "Products")
.SelectKey("ShipTo.Country", "Country")
.SelectCount()
.OfType<ProductsInfo>()
.ToListAsync();
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
:
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();
var results = await asyncSession.Advanced.AsyncDocumentQuery<Order>()
.GroupBy(("Lines[].Product", GroupByMethod.Array), ("Lines[].Quantity", GroupByMethod.Array))
.SelectKey("Lines[].Product", "Products")
.SelectKey("Lines[].Quantity", "Quantities")
.SelectCount()
.OfType<ProductsInfo>()
.ToListAsync();
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 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();
var results = await asyncSession.Query<Order>()
.GroupBy(x => x.Employee)
.Select(x => new
{
Employee = x.Key,
Count = x.Count()
})
.OrderBy(x => x.Count)
.ToListAsync();
from Orders
group by Employee
order by count() as long
select Employee, count()
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();
var results = await asyncSession.Query<Order>()
.GroupBy(x => x.Employee)
.Select(x => new
{
Employee = x.Key,
Count = x.Count()
})
.OrderBy(x => x.Count)
.ToListAsync();
from Orders
group by Employee
order by sum(Freight) as double
select key() as Employee, sum(Freight) as Sum