Can elasticsearch do GROUP BY and ORDER BY count?

Elastic Stack Elasticsearch
1

Let's say I have dictionary application where people come and look up word definitions.

Im trying to visualize top N most popular words during some timeframe.

In SQL I would do it possibly like this:

SELECT word, count(*) as cnt FROM lookups GROUP BY word ORDER BY cnt DESC;

So I plan on dumping data into ES in this format for example:

{"timestamp":"2016-11-07T12:38:56-05:00","word":"dog","is_mwu":"no","host":"08a2cf4807fb","ref":"dictionary"}
{"timestamp":"2016-11-07T12:38:58-05:00","word":"red","is_mwu":"no","host":"08a2cf4807fb","ref":"dictionary"}
{"timestamp":"2016-11-07T12:39:29-05:00","word":"red","is_mwu":"no","host":"08a2cf4807fb","ref":"dictionary"}
{"timestamp":"2016-11-08T09:05:49-05:00","word":"dog","is_mwu":"no","host":"08a2cf4807fb","ref":"dictionary"}

Is aggregate query like that possible with ES?

2

You'll want to do a terms aggregation.

3

thanks

4

Would I be able to set up those terms aggregations in kibana using some off the shelf chart?

5

Yes. On the x axis settings (assuming a line chart; other kinds of visualizations works similarly), split the buckets, choose terms aggregation and the field you want to group by. You may find https://www.timroes.de/2015/02/07/kibana-4-tutorial-part-3-visualize/ helpful.

6

Fantastic. I was able to launch everything in docker in minutes and get nice visual.
Thank u!