see on GitHub

    Perform Dynamic Group By Query

    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

    see on GitHub

    related articlesRelated Articles

    Session

    Indexes

    Home / Docs / Client Api / Session / Querying / How To Perform Group By Query