How to Implement SUM() function of SQL in cloud Firestore

Question

I am storing scores of a user in google cloud firestore as each score a new document in the collection named as "points".

collection name: points

• document1: {id:111,userid:345,value:50}
• document2:{id:222,userid:345,value:70}
• document3:{id:333,userid:345,value:30}
• document1:{id:444,userid:345,value:100}

I want to sum all values in value field.

I have google many times but found nothing. Is there any alternative for sum() or any other way to implement recording scores of a user?

Dunston

Answer 1

Since late 2023 Firestore has built-in support for calculating the sum of a certain field across documents efficiently, as well as calculating an average and counting the number of results. These operations are vastly cheaper than the naïve solution I described below, but when you'll still want to consider write-time calculations for aggregating across really large (think 10s of thousands) of documents across large number of clients.

For more on this, see the Firebase documentation on summarizing data with aggregation queries and the link to the write-time aggregation solution I included below. Also see Hugo's answer for more on this, and code samples of how to apply it to your data structure.

---

Previous answer 👇

There are no built-in aggregation operators in Cloud Firestore.

The naïve solution is to load the data in the client and sum it there, but that means that you (and your users) incur the cost of loading all documents for each time they need to show the sum.

A better way is to keep a so-called running total in your database, which you update whenever a document is written to (added, modified, removed) to the "points" collection. For this you have two options: do it from the client, or do it from a trusted environment (such as Cloud Functions). The Firestore documentation on aggregation queries describes both options and shows sample code.

Exude

I think as of October 2023, your statement "There are no built-in aggregation operators in Cloud Firestore" is not correct anymore. Please refer to my answer to see the latest update on the matter. – Elba

Good catch @HugoBiais 👍 --- I updated my answer with some current considerations and links, and also pointing to yours. – Exude

Answer 2

October 2023 update

With Firebase JS SDK 10.5.0 released on October 12th 2023, Cloud Firestore now supports sum and average aggregations queries.

I recommend you check the aggregation query documentation for more information.

Using the sum() function with the Web v9 SDK

Assuming you want to sum all the scores in the points collection:

const coll = collection(firestore, 'points');
const snapshot = await getAggregateFromServer(coll, {
  totalScores: sum('value')
});

console.log('totalScores: ', snapshot.data().totalScores);

Assuming you want to sum the scores of one particular user:

const coll = collection(firestore, 'points');
const q = query(coll, where('userid', '==', user.id));
const snapshot = await getAggregateFromServer(q, {
  totalUserScore: sum('value')
});

console.log('totalUserScore: ', snapshot.data().totalUserScore);

Note: Don't forget to import from firestore the functions used in the above examples. That includes collection, getAggregateFromServer, sum, query and where.

Be aware of the following limitations when using the sum() function (included here in the documentation)

• For sum() and average() aggregations, non-numeric values are ignored. sum() and average() aggregations take into account only integer values and floating-point number values.

• When combining multiple aggregations in a single query, note that sum() and average() ignore non-numeric values while count() includes non-numeric values.

Elba

Answer 3

Use a cloud function which will make a url for you.

Example:

import { Request, Response } from 'express'
import * as admin from 'firebase-admin'
import * as functions from 'firebase-functions'

export const getUsersCount = functions.runWith({ memory: '2GB', timeoutSeconds: 60 }).https.onRequest(async (req: Request, res: Response) => {

    const allUsers = await admin
    .firestore()
    .collection('users')
    .get()
    const numberOfUsers = allUsers.size;


    res.status(200).json({
        allTimeUsers: numberOfUsers,
    })
    return true;
})

Then just do Firebase deploy --only functions:getUsersCount The logs will print out the url for you. The url might take awhile to load if it's a big app.

Mississippian

Answer 4

You can either use forEach or iterate in a for loop. This answer on stack overflow could help. Here's an example from the same:

for (var i in querySnapshot.docs) {
    const doc = querySnapshot.docs[i]
    //do what you want to here
}

---OR---

you can use forEach like this

 const collRef = firebase.firestore().collection('todos');
  const query = collRef.orderBy('position');
  const items = query.get()
    .then((snapshot) => {
      let newCount = 0;
      snapshot.forEach((doc) => {
        const docRef = collRef.doc(doc.id);
        docRef.update({ position: newCount });
        newCount += 1;
      });
    });

Dionnadionne