Sum aggregation on array values


(Lokeshhctm) #1

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.


(system) #2