Perform Dynamic Group By Query
-
To run a dynamic query that aggregates data, use the
group_by()
method. -
Data can be grouped by a single or by multiple fields, and further aggregated by sum, type, or count.
-
RavenDB's query optimizer supports dynamic grouping by query, by automatically creating auto map-reduce indexes.
-
In This Page:
Group By a Single Field
orders = list(
session.query(object_type=Order)
.group_by("ship_to.country")
.select_key("ship_to.country", "country")
.select_sum(GroupByField("lines[].quantity", "ordered_quantity"))
.of_type(CountryAndQuantity)
)
from Orders
group by ShipTo.City
select ShipTo.City as Country, sum(Lines[].Quantity) as TotalQuantity
Group By Multiple Fields
results = list(
session.query(object_type=Order)
.group_by("employee", "company")
.select_key("employee", "employee_identifier")
.select_key("company")
.select_count()
.of_type(CountByCompanyAndEmployee)
)
from Orders
group by Employee, Company
select Employee as EmployeeIdentifier, Company, count()
Select Composite GroupBy Key
orders = list(
session.query(object_type=Order)
.group_by("employee", "company")
.select_key("key()", "employee_company_pair")
.select_count("count")
.of_type(CountOfEmployeeAndCompanyPairs)
)
from Orders
group by Employee, Company
select key() as EmployeeCompanyPair, count()
Group By Array
By Array Values
The following query will group by the lines[]
array product
property
and calculate the count per product.
Behind the scenes, an auto map-reduce index is created to handle the query.
products = list(
session.query(object_type=Order)
.group_by(GroupBy.array("lines[].product"))
.select_key("key()", "products")
.select_count()
.of_type(ProductsInfo)
)
from Orders
group by Lines[].Product
select Lines[].Product, count()
It is possible to group by the values of an array field and by the value of an additional property.
products = list(
session.advanced.document_query(object_type=Order)
.group_by("lines[].product", "ship_to.country")
.select_key("lines[].product", "product")
.select_key("ship_to.country", "country")
.select_count()
.of_type(ProductInfo)
)
from Orders
group by Lines[].Product, ShipTo.Country
select Lines[].Product as Product, ShipTo.Country as Country, count()
Grouping by values of multiple fields of the same array is supported as well.
results = list(
session.query(object_type=Order)
.group_by(GroupBy.array("lines[].product"), GroupBy.array("lines[].quantity"))
.select_key("lines[].product", "product")
.select_key("lines[].quantity", "quantity")
.select_count()
.of_type(ProductInfo)
)
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 creation of the reduction key will be based on the content of the array field specified by group_by
.
results = list(
session.query(object_type=Order)
.group_by(GroupBy.array("lines[].product"))
.select_key("key()", "products")
.select_count()
.of_type(ProductsInfo)
)
from Orders
group by array(Lines[].Product)
select key() as Products, count()
It is possible to group by the content of an array field and by that of a field of an additional property.
results = list(
session.query(object_type=Order)
.group_by(GroupBy.array("lines[].product"), GroupBy.field("ship_to.country"))
.select_key("lines[].product", "products")
.select_key("ship_to.country", "country")
.select_count()
.of_type(ProductsInfo)
)
from Orders
group by array(Lines[].Product), ShipTo.Country
select Lines[].Product as Products, ShipTo.Country as Country, count()
Grouping by multiple fields of the same array is also supported.
results = list(
session.query(object_type=Order)
.group_by(GroupBy.array("lines[].product"), GroupBy.array("lines[].quantity"))
.select_key("lines[].product", "products")
.select_key("lines[].quantity", "quantities")
.select_count()
.of_type(ProductsInfo)
)
from Orders
group by array(Lines[].Product), array(Lines[].Quantity)
select Lines[].Product as Products, Lines[].Quantity as Quantities, count()
Sorting
The results of a dynamic group_by query can be sorted by an aggregation function used in the query.
The results can be ordered by the aggregation operations count
and sum
.
By Count
results = list(
session.query(object_type=Order)
.group_by("employee")
.select_key("key()", "employee")
.select_count()
.order_by("count")
)
from Orders
group by Employee
order by count() as long
select Employee, count()
By Sum
results = list(
session.query(object_type=Order)
.group_by("employee")
.select_key("key()", "employee")
.select_sum(GroupByField("freight", "sum"))
.order_by("sum")
)
from Orders
group by Employee
order by sum(Freight) as double
select key() as Employee, sum(Freight) as Sum