Ordering on parent-aggregations based on sub-aggregrations

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.

Suggestions for version 8.5 is also fine.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.