I have an index with several columns having values like number of requests and a few columns are array fields.
I need to have sum of value column with group by of values in array column.
e.g. rows are like:
rowID, requests, array fields
1, 50, [a,b,c]
2, 100, [a,b]
3, 30 , [b,c]
So i want result as:
a = 150
b = 180
c = 80
The query i am trying always takes a lot of time. The query is:
{
"query": {
"filtered": {
"query": {
"match_all": {}
},
"filter": {}
}
},
"size": 0,
"aggs": {
"2": {
"terms": {
"field": "ARRAY COLUMN",
"size": 400,
"order": {
"1": "desc"
}
},
"aggs": {
"1": {
"sum": {
"field": "VALUES COLUMN"
}
}
}
}
}
}
What is the best way to have this aggregation to make it faster.