Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

Some of the elasticsearch aggregations are same as built-in database function such as GROUP BY, AVG, SUM, MIN, COUNT so on. Example below shows us how to use some of them in elasticsearch. For more information, read Aggregations.

Content of our elasticsearch index and database are equal. We have a few fields such as year, price, title so on. to work with.


Example 1


In this example, we're going to get statistics about year and price data.


Elasticsearch


curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
"size": 0,
"aggregations": {
"average_year": {
"avg": {
"field": "year"
}
},
"minimum_price": {
"min": {
"field": "price"
}
},
"maximum_price": {
"max": {
"field": "price"
}
},
"total_price": {
"sum": {
"field": "price"
}
},
"total_processed_records": {
"value_count": {
"field": "price"
}
}
}
}'

Result


{
"took": 2,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"failed": 0
},
"hits": {
"total": 5000,
"max_score": 0,
"hits": []
},
"aggregations": {
"average_year": {
"value": 2007.398
},
"minimum_price": {
"value": 0.5
},
"total_price": {
"value": 14409.989999999974
},
"maximum_price": {
"value": 5.55
},
"total_processed_records": {
"value": 5000
}
}
}

MySQL


mysql> SELECT
-> AVG(year), MIN(price), MAX(price), SUM(price), COUNT(*)
-> FROM post;
+-----------+------------+------------+------------+----------+
| AVG(year) | MIN(price) | MAX(price) | SUM(price) | COUNT(*) |
+-----------+------------+------------+------------+----------+
| 2007.398 | 0.50 | 5.55 | 14409.99 | 5000 |
+-----------+------------+------------+------------+----------+
1 row in set (0.00 sec)