How to Use Aggregation to Compute the Sum of a Field in Elasticsearch with Curl
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. In this step-by-step example we will interact with Elasticsearch using Curl commands to perform a summation. 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 take a look at the how to perform a sum aggregation, it’s important to mention a few prerequisites that need to be in place. For this task, the system requirements are minimal: * Elasticsearch needs to be installed and running.
Though it’s not required, it’s helpful to have some basic familiarity with the curl
command.
* 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 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:
id | name | price | quantity | department |
---|---|---|---|---|
1 | Multi-Grain Cereal | 4.99 | 4 | Packaged Foods |
2 | 1lb Ground Beef | 3.99 | 29 | Meat and Seafood |
3 | Dozen Apples | 2.49 | 12 | Produce |
4 | Chocolate Bar | 1.29 | 2 | Packaged Foods, Checkout |
5 | 1 Gallon Milk | 3.29 | 16 | Dairy |
6 | 0.5lb Jumbo Shrimp | 5.29 | 12 | Meat and Seafood |
7 | Wheat Bread | 1.29 | 5 | Bakery |
8 | Pepperoni Pizza | 2.99 | 5 | Frozen |
9 | 12 Pack Cola | 5.29 | 6 | Packaged Foods |
10 | Lime Juice | 0.99 | 20 | Produce |
11 | 12 Pack Cherry Cola | 5.59 | 5 | Packaged Foods |
12 | 1 Gallon Soy Milk | 3.39 | 10 | Dairy |
13 | 1 Gallon Vanilla Soy Milk | 3.49 | 9 | Dairy |
14 | 1 Gallon Orange Juice | 3.29 | 4 | Juice |
Here is the json we used to define the mapping if our index:
1 2 3 4 5 6 7 8 9 10 11 12 | { "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. 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | $ curl -H "Content-Type: application/json" -XGET "127.0.0.1:9200/store/products/_search?size=0&pretty" -d ' > { > "aggs": { > "quantity_sum": { > "sum": { "field": "quantity"} > } > } > } > ' { "took" : 4, "timed_out" : false, "_shards" : { "total" : 5, "successful" : 5, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : 14, "max_score" : 0.0, "hits" : [ ] }, "aggregations" : { "quantity_sum" : { "value" : 139.0 } } } |
You can verify from the results that our quantity_sum
139 in fact do add up correctly.
Now let’s go over the important parts of the code:
We created an aggregator with "aggs"
keyword.
Then we gave it a name "quantity_sum"
. This can be whatever name suits your application.
Next we used "sum"
to set the type of aggregator so Elasticsearch knows what calculation to perform with this aggregation.
Lastly we specified which field we wanted to sum up ‘”quantity”`.
Note: In the curl url notice that we used
size=0
because otherwise this query would return every product. With this parameter set, all we see is the aggregate information. We also specify the index and type in the url.
Conclusion
In this tutorial we demonstrated how to use Elasticsearch to find the sum for a field in a dataset. There are many other things you can do with aggregation and you can consult the Elasticsearch documentation to learn more about it. The documentation is also useful if you need help with syntax.
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 curl and aggregations here’s all the code we used to demonstrate how to find the sum of a field with Elasticsearch and Curl.
1 2 3 4 5 6 7 8 9 | $ curl -H "Content-Type: application/json" -XGET "127.0.0.1:9200/store/products/_search?size=0&pretty" -d ' > { > "aggs": { > "quantity_sum": { > "sum": { "field": "quantity"} > } > } > } > ' |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started