Sum and Average in Firestore | Leverage getAggregateFromServer in the Latest Update November 2023

As a Full Stack Developer working daily with Firebase and Firestore, I find it challenging to write complex queries in Firestore that would be straightforward in SQL or MongoDB. Given that Firestore’s strengths are its real-time capabilities and scalability, it’s unreasonable to expect it to handle such complex queries. Nonetheless, there are situations where we still need to execute more intricate queries.

If we need to obtain the Sum, Average, or Count of specific fields in a document, there used to be only one method: retrieving the documents from the database and performing the calculations on the front end or in Firebase functions. Fortunately, earlier this year, they introduced a new function, getCountFromServer() which helps in acquiring the count of a required field without needing to read the entire documents. However, other operations like Sum and Average are still not directly supported.

In the recent update in November 2023, Firestore introduced two additional functions, sum() and average(), along with the getAggregateFromServer() method. You can also use the count() method with getAggregateFromServer(). Here’s how you can implement these features in Firestore.

Sum and Average in Firestore

The first step is to update the Firebase package to the latest version (10.6.0). Here is an example demonstrating how to retrieve the Sum of total sales.

import {
  collection
  sum,
  getAggregateFromServer,
} from "@firebase/firestore";

try{
   const coll = collection(firestore,  "orders");
   const snapshot = await getAggregateFromServer(coll, {
        totalSales: sum('amount'),
      });
   console.log('totalSales: ', snapshot.data().totalSales);
}catch(e){
   console.log( e)
} 

Similarly, you can use the average() and count() functions. Additionally, it’s possible to combine all three of these operations in a single query. For a more complex requirement, here’s an example where I incorporate the use of where and orderBy clauses.

import {
  collection,
  orderBy,
  query,
  where,
  sum,
  average,
  count,
  getAggregateFromServer,
} from "@firebase/firestore";

let start_date = new Date();
let end_date = new Date();
start_date.setDate(start_date.getDate() - 30); // Getting last 30 days data
start_date.setHours(0, 0, 0, 0);
end_date.setDate(end_date.getDate() + 1);

try{
const orderQuery = query(
          collection(
            firestore,
            "orders"
          ),
          where("date", ">", new Date(start_date)),
          where("date", "<", new Date(end_date)),
          orderBy("date", "desc")
        );
const snapshot = await getAggregateFromServer(orderQuery, {
        numberOfSales: count('amount'),
        totalSales: sum('amount'),
        averageSales: average('amount'),
      });

      console.log('numberOfSales: ', snapshot.data().numberOfSales);
      console.log('totalSales: ', snapshot.data().totalSales);
      console.log('averageSales: ', snapshot.data().averageSales);
}catch(e){
   console.log( e)
} 

The primary advantage of using these methods is cost efficiency. They will only require a fraction of the reads, significantly reducing expenses. The cost depends on the number of indexes used. However, it’s important to be aware of some limitations associated with these aggregation queries.

• sum() and average() will consider only numeric field values. It will ignore other types
• You cannot use it with real-time listeners and offline queries.
• Aggregation queries read from index entries and include only indexed fields.
• Adding an OrderBy clause to an aggregation query limits the aggregation to the documents where the sorting field exists.
• If you combine aggregations that are on different fields, the calculation includes only the documents that contain all those fields.

Despite these limitations, I find the introduction of Sum and Average in Firestore to be beneficial. I’m optimistic that future releases will broaden support to include operations like Min, Max, etc., and offer more robust capabilities for handling complex queries.

<Happy Coding />