Is there a way to perform a nested aggregation from Kibana (v6.7.0)? I've been looking at aggregating with buckets and sub-buckets, but to no avail.
Say the data looks as follows (with ID being a string and Source also being a string:
| ID | Source |
===============
| a1 | srcA |
| a1 | srcB |
| a1 | srcC |
| b2 | srcA |
| b2 | srcB |
| c3 | srcA |
| d4 | srcA |
| d4 | srcB |
I'd like to visualize how many IDs are in N sources, i.e. a double aggregation. In SQL-Land, the first level of aggregation would be:
SELECT ID, COUNT(1) as N_SRC FROM DATA GROUP BY ID
yielding something like this:
| ID | N_SRC |
===============
| a1 | 3 |
| b2 | 2 |
| c3 | 1 |
| d4 | 2 |
So, what I am after is the possibility to run a nested aggregation as such:
SELECT N_SRC, COUNT(1) AS N FROM (
SELECT ID, COUNT(1) as N_SRC FROM DATA GROUP BY ID
) AS AGG_1
GROUP BY N_SRC
which would aggregate to the following:
| N_SRC | N |
===============
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
And a vertical bar chart would be looking something like this:
N
| ___
| ___ | | ___
|__|_|__|_|__|_|___
1 2 3 N_SRC
It can also be a horizontal bar chart of course.
This question seems similar, but doesn't have a satisfactory answer: (Elasticsearch - Kibana - Group By and Count) Also, I'd be after a bar chart and not a pie chart!
I've also looked at this, this and this but the answers are either quite dated or not applicable.
