Can elasticsearch do GROUP BY and ORDER BY count?

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?

You'll want to do a terms aggregation.

thanks

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

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.

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

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.