How to Use Aggregation to Compute the Sum of a Field in Elasticsearch with NodeJS

Introduction

If you’re running a NodeJS application with Elasticsearch it won’t be long before you’ll need to compute the sum of field in your dataset. Computing the sum of a field is a common practice when using Elasticsearch. You may need to sum up the sales made in a year or calculate the quantity of a product in stock. These sums can be computed easily using aggregation. Aggregation has many use cases besides summation but in this article we will focus on a specific example of computing the sum. We will be using a simple application in NodeJS which interacts with Elasticsearch via the elasticsearch npm module. We’ll show you how step-by-step. But if you’d just rather see the example code, click here to jump to Just the Code.

Note: The code will vary depending on all your system parameters but we hope to give you an idea of how this is done.

Prerequisites

Before we show you how to compute the average value of a field with Elasticsearch in Javascript, it’s important to make sure a few prerequisites are in place. There are only a few of system requirements for this task: NodeJS needs to be installed The elasticsearch npm module installed. A simple npm install elasticsearch should work in most cases. Elasticsearch also needs to be installed and running. * In our example, we have Elasticsearch installed locally using the default port of 9200. If your Elasticsearch installation is running on a different server, you’ll need to modify your javascript syntax accordingly.

Introduce our Example Data

Let’s look at an example that uses an index called store, which represents a small grocery store. This store index contains a type called products which lists the store’s products. To keep things simple, our example dataset will only contain a handful of products with just the following fields: id, price, quantity, and department. The code below shows the JSON used to create the dataset:

idnamepricequantitydepartment
1Multi-Grain Cereal4.994Packaged Foods
21lb Ground Beef3.9929Meat and Seafood
3Dozen Apples2.4912Produce
4Chocolate Bar1.292Packaged Foods, Checkout
51 Gallon Milk3.2916Dairy
60.5lb Jumbo Shrimp5.2912Meat and Seafood
7Wheat Bread1.295Bakery
8Pepperoni Pizza2.995Frozen
912 Pack Cola5.296Packaged Foods
10Lime Juice0.9920Produce
1112 Pack Cherry Cola5.595Packaged Foods
121 Gallon Soy Milk3.3910Dairy
131 Gallon Vanilla Soy Milk3.499Dairy
141 Gallon Orange Juice3.294Juice

Here is the json we used to define the mapping if our index:

{
    "mappings": {`
        "products": {
            "properties" : {`
                "name": { "type": "text"},
                "price": { "type": "double"},
                "quantity": { "type": "integer"},
                "department": { "type": "keyword"}
            }
        }
    }
}
'

Use the Sum Aggregation

Now let’s say we want to compute the total number of products we have in stock by adding the quantities of each product. 4 (Multi-Grain Cereal) + 29 (1lb Ground Beef) + … With 14 products that may be easy to do by hand, but in the real world you may have hundreds or thousands of entries that you need to sum together.

The code to sum the quantities of each product is below. We’ll dissect the code afterwards.

File: sum.js

var elasticsearch = require("elasticsearch");

var client = new elasticsearch.Client({
  hosts: ["http://localhost:9200"]
});

/* Calculate the sum using aggregation */
client.search({
  size: 0,
  index: 'store',
  type: 'products',
  body: {
        "aggs" : {
            "quantity_sum" : {
                "sum" : {
                  "field" : "quantity"
                }
            }
        }
    }
}).then(function(resp) {
  console.log("Successful query!");
  console.log(JSON.stringify(resp, null, 4));
}, function(err) {
  console.trace(err.message);
});

We won’t go over the full-list of parameter options ( you can consult the documentation for that ) but we give the search function everything it needs to compute the aggregation. First we give the search a size: 0 to indicate that we don’t want to return documents but only the aggregation data. Next we provide it with the index store and type products to operate on. Then we create an aggregation with the aggs object inside the body. We give that aggregation a name quantity_sum. We set the aggregation type to sum so Elasticsearch knows what calculation to compute. Then we specify the field that we want to take an average on field: "quantity".

You can run this application using NodeJS with this command:

$ node sum.js

Let’s see if it works. To see if it works we used promises to console.log the results when it is successful and log an error on failure. Let’s run the application and see what happens:

$ node sum.js
Successful query!
{
    "took": 42,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 14,
        "max_score": 0,
        "hits": []
    },
    "aggregations": {
        "quantity_sum": {
            "value": 139
        }
    }
}

We get back our success message and we can verify that our aggregator gave us the value we expected:

    "aggregations": {
        "quantity_sum": {
            "value": 139
        }
    }

Conclusion

In this tutorial we demonstrated how to calculate the sum of a field using aggregation in Elasticsearch with Javascript running on NodeJS. This was a basic example of an aggregation but can easily be built upon to perform the more complex aggregations you’ll require for your application. We hope you found tutorial helpful and you can apply it to your specific application. If you have questions or this didn’t work for you please reach out to us so we can help. Thank you.

Just the Code

If you’re already comfortable with NodeJS and aggregations here’s all the code we used to demonstrate how to find an average with Elasticsearch and NodeJS.

File: sum.js

var elasticsearch = require("elasticsearch");

var client = new elasticsearch.Client({
  hosts: ["http://localhost:9200"]
});

/* Calculate the sum using aggregation */
client.search({
  size: 0,
  index: 'store',
  type: 'products',
  body: {
        "aggs" : {
            "quantity_sum" : {
                "sum" : {
                  "field" : "quantity"
                }
            }
        }
    }
}).then(function(resp) {
  console.log("Successful query!");
  console.log(JSON.stringify(resp, null, 4));
}, function(err) {
  console.trace(err.message);
});

Pilot the ObjectRocket Platform Free!

Try Fully-Managed Redis,
MongoDB & Elasticsearch

Get Started

OR

Try CockroachDB
in Beta

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.