You are currently browsing legacy 4.0 version of documentation. Click here to switch to the newest 6.0 version.
    We can help you with migration to the latest RavenDB
    Contact Us Now
    see on GitHub

    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 

    const orders = await session.query({ collection: "Orders" })
    .groupBy("ShipTo.Country")
    .selectKey("ShipTo.Country", "Country")
    .selectSum(new GroupByField("Lines[].Quantity", "OrderedQuantity"))
    .ofType(CountryAndQuantity)
    .all();
    from Orders
group by ShipTo.City
select ShipTo.City as Country, sum(Lines[].Quantity) as TotalQuantity

    Group By Multiple Fields 

    const results = await session.query({ collection: "Orders" })
    .groupBy("Employee", "Company")
    .selectKey("Employee", "EmployeeIdentifier")
    .selectKey("Company")
    .selectCount()
    .ofType(CountByCompanyAndEmployee)
    .all();
    from Orders
group by Employee, Company
select Employee as EmployeeIdentifier, Company, count() as Count

    Select Composite GroupBy Key 

    const orders = await session.query({ collection: "Orders" })
    .groupBy("Employee", "Company")
    .selectKey("key()", "EmployeeCompanyPair")
    .selectCount("Count")
    .ofType(CountOfEmployeeAndCompanyPairs)
    .all();
    from Orders 
group by Employee, Company
select key() as EmployeeCompanyPair, count() as 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. 

    const products = await session.query({ collection: "Orders" })
    .groupBy(GroupBy.array("Lines[].Product"))
    .selectKey("key()", "Products")
    .selectCount()
    .ofType(ProductsInfo)
    .all();
    from Orders 
group by Lines[].Product
select Lines[].Product, count() as Count

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

    const results = await session.advanced.documentQuery({ collection: "Orders" })
    .groupBy("Lines[].Product", "ShipTo.Country")
    .selectKey("Lines[].Product", "Product")
    .selectKey("ShipTo.Country", "Country")
    .selectCount()
    .ofType(ProductInfo)
    .all();
    from Orders 
group by Lines[].Product, ShipTo.Country 
select Lines[].Product as Product, ShipTo.Country as Country, count() as Count

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

    const results = await session.query({ collection: "Orders" })
    .groupBy(GroupBy.array("Lines[].Product"), GroupBy.array("Lines[].Quantity"))
    .selectKey("Lines[].Product", "Product")
    .selectKey("Lines[].Quantity", "Quantity")
    .selectCount()
    .ofType(ProductInfo)
    .all();
    from Orders 
group by Lines[].Product, Lines[].Quantity 
select Lines[].Product as Product, Lines[].Quantity as Quantity, count() as 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.

    const results = await session.query({ collection: "Orders" })
    .groupBy(GroupBy.array("Lines[].Product"))
    .selectKey("key()", "Products")
    .selectCount()
    .ofType(ProductsInfo)
    .all();
    from Orders
group by array(Lines[].Product)
select key() as Products, count() as Count

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

    const results = await session.query({ collection: "Orders" })
    .groupBy(GroupBy.array("Lines[].Product"), GroupBy.field("ShipTo.Country"))
    .selectKey("Lines[].Product", "Products")
    .selectKey("ShipTo.Country", "Country")
    .selectCount()
    .ofType(ProductsInfo)
    .toList();
    from Orders 
group by array(Lines[].Product), ShipTo.Country 
select Lines[].Product as Products, ShipTo.Country as Country, count() as Count

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

    const results = await session.query({ collection: "Orders" })
    .groupBy(GroupBy.array("Lines[].Product"), GroupBy.array("Lines[].Quantity"))
    .selectKey("Lines[].Product", "Products")
    .selectKey("Lines[].Quantity", "Quantities")
    .selectCount()
    .ofType(ProductsInfo)
    .all();
    from Orders 
group by array(Lines[].Product), array(Lines[].Quantity) 
select Lines[].Product as Products, Lines[].Quantity as Quantities, count() as Count

    see on GitHub

    related articlesRelated Articles

    Session

    Indexes

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