Terms aggregation ordered by date field

krystalcode · September 23, 2015, 9:27pm

Hello,

I have a document type with 2 fields:

my_field.hash
my_field.time

Each value of the "hash" corresponds to a unique value of "time". It is extremely unlikely that 2 different "hashes" will have the same "time".

I'm trying to set up a visualisation in Kibana (bar chart) where the X-axis uses a Terms aggregation on the my_field.hash; that part works as expected. I want however to order the bar char on the my_field.time field, instead on the count. This means that the resulting chart would display the number of documents (Y-axis) per "hash" (X-axis) ordered by "time" (X-axis).

I tried to set the Order By property of the X-axis aggregation to Max, but it doesn't give me the option to select any field. All other options (count, average, sum, min, max, unique) do not seem to help either.

Is there any way to achieve this? Possibly with some JSON input in the Advanced aggregation settings?

Rather new to Kibana/ElasticSearch here and any help would be appreciated.

warkolm · September 24, 2015, 1:20am

Try adding a sub agg with a date histogram?

krystalcode · September 24, 2015, 6:42am

That won't work unfortunately:

There can only be one aggregation on X-axis and that has to be the terms aggregation on the "hash" field so that the legend is correct.
The bars need to be evenly spaced on the X-axis while a date histogram will distribute them based on time.

It seems to me that this is a feature missing, I'll raise an issue on github.

tbragin · September 26, 2015, 5:37am

I don't completely understand your use case - could you post a couple of sample documents and mock up on paper a visualization you're trying to achieve?

krystalcode · September 26, 2015, 9:25am

Sure, that will clarify things.
I'm storing activity on a git repo and this includes the commits. Sample document:

{
    "priority":    "low/normal/high",
    "commit.hash": "62c964b764f6cbad45fcdd589fdffa54f8adb5fe",
    "commit.time": "2015-09-24T21:32:29+0000"
}

I am plotting a date histogram that shows the number of documents aggregated by "commit.time", with sub-aggregation on the "priority" field. The result is this:

This is good, but each bar contains counts for more than one commit. I need another view that clearly shows the progression per commit. That needs a "Terms" aggregation on the "commit.hash" field and the result is this:

The results are properly aggregated by "commit.hash" but they are sorted by the number of documents that each commit has. I want them to be sorted by "commit.hash" so that oldest commits show first and newest show last.

I thought that using Order By = Custom Metric -> Aggregation = Max and Field = "commit.time" would work, but the Field select does not give me any choices - it's an empty select for me.

Another similar case is that I want to simply display the commit hashes on a table for navigation. The result is:

And I want them to be sorted by "commit.time" descending.

tbragin · September 26, 2015, 12:54pm

Thanks for explaining your use case!

Sorting a term agg by date is indeed not possible now. I didn't see any similar requests on github, so feel free to raise one: https://github.com/elastic/kibana/issues

As a workaround for the second visualization you mentioned (the bar chart with terms), could you simply use filtering by commit number on the first date histogram (see mockup below)? Bars won't be right next to each other, but in some way progression is better represented because you see gaps between hashes.