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 GROUP BY 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 group year and price fields to see how many records are there in index/database against each unique value.


Elasticsearch


curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
"size": 0,
"aggregations": {
"total_per_year": {
"terms": {
"field": "year"
}
},
"total_per_price": {
"terms": {
"field": "price"
}
}
}
}'

Result


{
"took": 3,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"failed": 0
},
"hits": {
"total": 5000,
"max_score": 0,
"hits": []
},
"aggregations": {
"total_per_year": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 2005,
"doc_count": 1285
},
{
"key": 2000,
"doc_count": 1259
},
{
"key": 2010,
"doc_count": 1255
},
{
"key": 2015,
"doc_count": 1201
}
]
},
"total_per_price": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 1,
"doc_count": 881
},
{
"key": 0.5,
"doc_count": 851
},
{
"key": 2.5,
"doc_count": 823
},
{
"key": 5.55,
"doc_count": 822
},
{
"key": 4,
"doc_count": 812
},
{
"key": 3.99,
"doc_count": 811
}
]
}
}
}

MySQL


# YEAR
mysql> SELECT
-> year, COUNT(year)
-> FROM post
-> GROUP BY year
-> ORDER BY COUNT(year) DESC;
+------+-------------+
| year | COUNT(year) |
+------+-------------+
| 2005 | 1285 |
| 2000 | 1259 |
| 2010 | 1255 |
| 2015 | 1201 |
+------+-------------+
4 rows in set (0.00 sec)

# PRICE
mysql> SELECT
-> price, COUNT(price)
-> FROM post
-> GROUP BY price
-> ORDER BY COUNT(price) DESC;
+-------+--------------+
| price | COUNT(price) |
+-------+--------------+
| 1.00 | 881 |
| 0.50 | 851 |
| 2.50 | 823 |
| 5.55 | 822 |
| 4.00 | 812 |
| 3.99 | 811 |
+-------+--------------+
6 rows in set (0.00 sec)