Pivoting in Elasticsearch

Elastic Stack Kibana
1

Hey ,

Can anybody tell me is there a way in elasticsearch/kibana to provide pivoting feature as in Oracle?

I want to create a report with pivoting a field with sum as aggregation.

For example : I have this schema indexed in test-index/test-type Doc id being ID.

ID | NAME | Code | AMT
-----------------------
1  | ABC  |   a  | 10
2  | DEF  |   c  | 20
3  | XYZ  |   a  | 30
4  | ABC  |   a  | 10
5  | ABC  |   c  | 20
6  | XYZ  |   b  | 30
7  | DEF  |   c  | 10
8  | DEF  |   a  | 20

I want to generate a report pivoting done like this : SUM(AMT) FOR CODE IN (a,b,c)

NAME | a  |  b  | c 
---------------------
ABC  | 20 |  0  | 20
DEF  | 20 |  0  | 30
XYZ  | 30 | 30  | 0

Can we do it using elasticsearch and reports generated in kibana?
PS. I am new to Elasticsearch Query DSL, so it would be helpful if someone having good grasp on query DSL can help me out whether its possible in ES , or suggest me a way to do this??

2

You can do a table with an agg on NAME and then a sub-agg using a sum, on Code.
That'll look like this;

Screen Shot 2015-06-30 at 15.29.24 pm.jpg1609×950 104 KB

Not sure you can have it in the format you are after though.

3

Thanks for your reply @warkolm . But can you tell me how to Pivot?? i.e. make field value as a field and do aggregation upon it? Any solution to it ?

4

I don't think there is a direct equivalent to a pivot. I would also propose doing a "terms" aggregation on the "NAME" field and then adding the rest of the fields as columns in the table, as @warkolm showed in the screenshot.