Issue with grouping . Data splitting to Pivot


#1

Issue Summary

I am pulling data from Elasticsearch and I need to do 2 aggregations. First aggregation is Day/Month . After Day aggregation, I am aggrgating by category . So for each Day for each category I want the count. Problem is that If I do 2 aggregations, category is splitting to pivot format to columns,. Ideally it shluld come under a field called Count, along with Date and Category.
This is the output

Steps to Reproduce

Code is here.
{
“index”: “index1”,
“aggs”: @{
@“Month”: {
“date_histogram”: {
“field”: “date”,
“interval”: “month”,
“format”: “MMM”
},
“aggs”: {
“Category”: {
“terms”: {
“field”: “category”
}
}
}
}
}
}

Expected output:
image


#2

I’m not sure I understand if the problem here is the way ElasticSearch works or how Redash parses the results. Can you include an example of how the data looks in Redash after running this query?


#3

Hi Arik Can you please give an update. Data looks exactly like the excel screenshot