Hi,
I have a list of documents in which i want to aggregate on one field called type and get result with lest price in that specific type and sort based on custom logic.
Ex :
Document Structure is
Id, Name, Type, price, preference, recent-id, country ..
Values like
1, A, Type1, 10000, 2, 6, Canada
2,B, Type1, 20000, 3, 6, Canada
3, c, Type1, 5000, 2, 7, Canada
4,d, Type2, 20000, 3, 6, Canada
5, e, Type2, 80000, 2, 8, Canada
6,f, Type3, 20000, 3, 6, Canada
7, g, Type3, 30000, 2, 9, Canada
8,h, Type4, 20000, 3, 5, Canada
9, i, Type4, 60000, 2, 4, Canada
10,j, Type4, 20000, 3, 16, Canada
11, A, Type1, 10000, 2, 6, India
12,B, Type1, 20000, 3, 6, India
13, c, Type1, 5000, 2, 7, India
14,d, Type2, 20000, 3, 6, India
15, e, Type2, 80000, 2, 8, India
16,f, Type3, 20000, 3, 6, India
17, g, Type3, 30000, 2, 9, India
18,h, Type4, 20000, 3, 5, India
19, i, Type4, 60000, 2, 4, India
20,j, Type4, 20000, 3, 16, India
Now I want to get a list of documents matching with Canada aggregated by type and only one record for each type which has lowest price and result should be sorted based on custom boost and sort logic. I need to paginate this result
I have tried this with two queries one first match with country and aggregate based on the type and top hit with sort on price with size as a large number.
In second query pass list of Ids and fetch records in paginated order. Problem with this is Top hits with large numbers is taking 3-4 secs each time.
Sample first query is :
{
"size": 0,
"query": {
"bool": {
"filter": {
"bool": {
"must": [
{
"terms": {
"country": [
"canada"
]
}
}
]
}
}
}
},
"aggs": {
"group_type": {
"terms": {
"field": "type"
},
"aggs": {
"group_docs": {
"top_hits": {
"size": 10000,
"sort": [
{
"price": {
"order": "asc"
}
}
],"_source": {
"includes": [
"id"
]
}
}
}
}
}
}
}'
Above querys take lot of time.
Can someone suggest a better way to design and write this query or the entire document