Hi, I am using elasticsearch 6.3 . I have an index event_v4 which store the data of all events handled by our company. Now i need to get a list of cities that satisfy a certain conditions. But if the count is less than required then i need to drop the filter one by one to get required number of results. In order to achieve this i used function_score. Now i can order the parent aggregation on the score but now i need to order in this way, first order based on score then order based on the doc_count for that score.
Here is the query i have worked on so far:
GET event_v4/_search
{
"_source": false,
"size": 0,
"query": {
"function_score": {
"query": {
"bool": {
"must": [
{
"term": {
"published": 1
}
},
{
"term": {
"functionality": "open"
}
},
{
"term": {
"city_searchable": 1
}
}
],
"must_not": [
{
"term": {
"status": "U"
}
}
],
"should": null
}
},
"functions": [
{
"filter": {
"term": {
"category": "27"
}
},
"weight": 20
},
{
"filter": {
"term": {
"country": "IN"
}
},
"weight": 40
},
{
"filter": {
"terms": {
"event_type": [
"Tradeshow"
]
}
},
"weight": 10
}
],
"score_mode": "sum",
"boost_mode": "replace"
}
},
"aggs": {
"all_event_count": {
"terms": {
"field": "city",
"size": 30000
},
"aggs": {
"total_count": {
"terms": {
"script": "_score",
"value_type": "integer",
"order": {
"_key": "desc"
},
"min_doc_count": 5
}
},
"score": {
"max_bucket": {
"buckets_path": "total_count._key" // to get the score of the max bucket after min_doc_count is applied
}
},
"select_bucket": {
"bucket_selector": {
"buckets_path": {
"total_count": "total_count._bucket_count"
},
"script": "params.total_count >= 1" // to remove null buckets in child-aggs after min_doc_count is applied
}
},
"sort_city": {
"bucket_sort": {
"sort": [
{
"score": "desc"
},
{
"_count": "desc" // need to sort on doc_count of the score not the count of the parent aggs
}
],
"from": 0,
"size": 50
}
}
}
}
}
}
The result of the query is as follows:
{
"took": 75,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 17789,
"max_score": 0,
"hits": []
},
"aggregations": {
"all_event_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "70469",
"doc_count": 406,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 70,
"doc_count": 12
},
{
"key": 60,
"doc_count": 13
},
{
"key": 50,
"doc_count": 213
},
{
"key": 40,
"doc_count": 168
}
]
},
"score": {
"value": 70,
"keys": [
"70"
]
}
},
{
"key": "70624",
"doc_count": 340,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 70,
"doc_count": 13
},
{
"key": 60,
"doc_count": 14
},
{
"key": 50,
"doc_count": 198
},
{
"key": 40,
"doc_count": 115
}
]
},
"score": {
"value": 70,
"keys": [
"70"
]
}
},
{
"key": "70532",
"doc_count": 182,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 70,
"doc_count": 6
},
{
"key": 60,
"doc_count": 6
},
{
"key": 50,
"doc_count": 95
},
{
"key": 40,
"doc_count": 75
}
]
},
"score": {
"value": 70,
"keys": [
"70"
]
}
},
{
"key": "70699",
"doc_count": 127,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 60,
"doc_count": 5
},
{
"key": 50,
"doc_count": 79
},
{
"key": 40,
"doc_count": 41
}
]
},
"score": {
"value": 60,
"keys": [
"60"
]
}
},
{
"key": "70435",
"doc_count": 107,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 60,
"doc_count": 9
},
{
"key": 50,
"doc_count": 51
},
{
"key": 40,
"doc_count": 43
}
]
},
"score": {
"value": 60,
"keys": [
"60"
]
}
},
{
"key": "70630",
"doc_count": 64,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 60,
"doc_count": 5
},
{
"key": 50,
"doc_count": 29
},
{
"key": 40,
"doc_count": 30
}
]
},
"score": {
"value": 60,
"keys": [
"60"
]
}
},
{
"key": "70751",
"doc_count": 193,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 71
},
{
"key": 40,
"doc_count": 121
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "90429",
"doc_count": 91,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 14
},
{
"key": 40,
"doc_count": 76
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70472",
"doc_count": 73,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 52
},
{
"key": 40,
"doc_count": 15
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70772",
"doc_count": 56,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 32
},
{
"key": 40,
"doc_count": 22
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70672",
"doc_count": 38,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 22
},
{
"key": 40,
"doc_count": 16
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70425",
"doc_count": 26,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 20
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70479",
"doc_count": 24,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 19
},
{
"key": 40,
"doc_count": 5
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70466",
"doc_count": 22,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 16
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70701",
"doc_count": 22,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 16
},
{
"key": 40,
"doc_count": 6
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70497",
"doc_count": 21,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 11
},
{
"key": 40,
"doc_count": 9
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70490",
"doc_count": 17,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 16
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70592",
"doc_count": 17,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 14
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70746",
"doc_count": 15,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 9
},
{
"key": 40,
"doc_count": 5
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70487",
"doc_count": 15,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 13
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70563",
"doc_count": 13,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 8
},
{
"key": 40,
"doc_count": 5
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70464",
"doc_count": 11,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 10
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "83849",
"doc_count": 11,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 6
},
{
"key": 40,
"doc_count": 5
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70626",
"doc_count": 10,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 6
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70625",
"doc_count": 9,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 6
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "73797",
"doc_count": 8,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 8
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "84025",
"doc_count": 7,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 50,
"doc_count": 7
}
]
},
"score": {
"value": 50,
"keys": [
"50"
]
}
},
{
"key": "70452",
"doc_count": 7,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 40,
"doc_count": 5
}
]
},
"score": {
"value": 40,
"keys": [
"40"
]
}
},
{
"key": "1",
"doc_count": 1409,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 17
},
{
"key": 20,
"doc_count": 50
},
{
"key": 10,
"doc_count": 571
},
{
"key": 1,
"doc_count": 771
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "81795",
"doc_count": 387,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 10
},
{
"key": 20,
"doc_count": 35
},
{
"key": 10,
"doc_count": 167
},
{
"key": 1,
"doc_count": 175
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "68873",
"doc_count": 313,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 13
},
{
"key": 20,
"doc_count": 31
},
{
"key": 10,
"doc_count": 126
},
{
"key": 1,
"doc_count": 143
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "70834",
"doc_count": 285,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 6
},
{
"key": 20,
"doc_count": 20
},
{
"key": 10,
"doc_count": 136
},
{
"key": 1,
"doc_count": 123
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "70824",
"doc_count": 236,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 5
},
{
"key": 20,
"doc_count": 16
},
{
"key": 10,
"doc_count": 152
},
{
"key": 1,
"doc_count": 63
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "69091",
"doc_count": 228,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 17
},
{
"key": 10,
"doc_count": 167
},
{
"key": 1,
"doc_count": 42
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "69308",
"doc_count": 173,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 5
},
{
"key": 20,
"doc_count": 10
},
{
"key": 10,
"doc_count": 107
},
{
"key": 1,
"doc_count": 51
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "70038",
"doc_count": 156,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 8
},
{
"key": 20,
"doc_count": 7
},
{
"key": 10,
"doc_count": 75
},
{
"key": 1,
"doc_count": 66
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "69058",
"doc_count": 137,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 5
},
{
"key": 20,
"doc_count": 5
},
{
"key": 10,
"doc_count": 98
},
{
"key": 1,
"doc_count": 29
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "70194",
"doc_count": 123,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 6
},
{
"key": 10,
"doc_count": 100
},
{
"key": 1,
"doc_count": 16
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "97467",
"doc_count": 109,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 7
},
{
"key": 10,
"doc_count": 98
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "69966",
"doc_count": 108,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 6
},
{
"key": 10,
"doc_count": 49
},
{
"key": 1,
"doc_count": 51
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "81673",
"doc_count": 104,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 6
},
{
"key": 20,
"doc_count": 5
},
{
"key": 10,
"doc_count": 81
},
{
"key": 1,
"doc_count": 12
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "69222",
"doc_count": 88,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 5
},
{
"key": 20,
"doc_count": 7
},
{
"key": 10,
"doc_count": 43
},
{
"key": 1,
"doc_count": 33
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "68961",
"doc_count": 81,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 5
},
{
"key": 10,
"doc_count": 58
},
{
"key": 1,
"doc_count": 17
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "70208",
"doc_count": 79,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 5
},
{
"key": 10,
"doc_count": 65
},
{
"key": 1,
"doc_count": 9
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "70348",
"doc_count": 67,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 5
},
{
"key": 10,
"doc_count": 48
},
{
"key": 1,
"doc_count": 12
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "69154",
"doc_count": 55,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 9
},
{
"key": 10,
"doc_count": 39
},
{
"key": 1,
"doc_count": 5
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "69239",
"doc_count": 53,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 30,
"doc_count": 6
},
{
"key": 10,
"doc_count": 39
},
{
"key": 1,
"doc_count": 8
}
]
},
"score": {
"value": 30,
"keys": [
"30"
]
}
},
{
"key": "69000",
"doc_count": 181,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 20,
"doc_count": 8
},
{
"key": 10,
"doc_count": 82
},
{
"key": 1,
"doc_count": 90
}
]
},
"score": {
"value": 20,
"keys": [
"20"
]
}
},
{
"key": "70851",
"doc_count": 146,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 20,
"doc_count": 15
},
{
"key": 10,
"doc_count": 59
},
{
"key": 1,
"doc_count": 70
}
]
},
"score": {
"value": 20,
"keys": [
"20"
]
}
},
{
"key": "70803",
"doc_count": 140,
"total_count": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 20,
"doc_count": 7
},
{
"key": 10,
"doc_count": 76
},
{
"key": 1,
"doc_count": 53
}
]
},
"score": {
"value": 20,
"keys": [
"20"
]
}
}
]
}
}
}
Kindly provide a way to sort on the doc_count of the score in child aggs. Also any suggestions to achieve this requirement of dropping filters without using score is also welcomed.