Bucket sort by a scripted metric

[Kun_Liu1 (Kun Liu)]

HI everyone,

I'm currently on elasticsearch 7.4. We have some complicated elasticsearch queries. I have an issue when sorting top 10 terms for some nested field by scripted metric.

Here is the query I'm having issue.

  {
    "size": 0,
    "aggs": {
      "nested_entities": {
        "nested": {
          "path": "entities"
        },
        "aggs": {
          "entities": {
            "terms": {
              "script": {
                "source": "doc['entities.type'].value + doc['entities.extracted_entity_id'].value",
                "lang": "painless"
              },
              "include": "person.*",
              "order": {
                "reversed_entities>nested_syndicated_items>total_audience": "desc"
              }
            },
            "aggs": {
              "one_doc": {
                "top_hits": {
                  "size": 1,
                  "_source": {
                    "include": ["entities.extracted_entity_id", "entities.text"]
                  }
                }
              },
              "reversed_entities": {
                "reverse_nested": {},
                "aggs": {
                  
                  "nested_syndicated_items": {
                    "nested": {
                      "path": "syndicated_items"
                    },
                    "aggs": {
                      "total_audience": {
                        "scripted_metric": {
                          "init_script": "state.source_id_map = [:]",
                          "map_script": "def source_id = doc['syndicated_items.source_id'].value; def audience = 0; if (doc.containsKey('syndicated_items.audience') && doc['syndicated_items.audience'] != null && doc['syndicated_items.audience'].size() > 0) { audience = doc['syndicated_items.audience'].value } if ((!state.source_id_map.containsKey(source_id)) || (state.source_id_map[source_id] <= audience)) { state.source_id_map[source_id] = audience; } ",
                          "combine_script": "return state.source_id_map;",
                          "reduce_script": "def newMap = [:];  for (source_id_map in states) { if (source_id_map != null) { for(source_id in source_id_map.keySet()) { newMap[source_id] = source_id_map[source_id]; } } } double sum = 0; for (source_id in newMap.keySet()) { sum += newMap[source_id]; } return sum;"
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    },
    "query": {
      "bool": {
        "filter": [{
          "terms": {
            "_workspace_ids": [8]
          }
        }, {
          "term": {
            "_workspace_ids": {
              "value": 8
            }
          }
        }, {
          "range": {
            "publication_date": {
              "gte": "2020-07-01T14:38:21.511489+10:00",
              "lt": "now",
              "time_zone": "+10:00"
            }
          }
        }]
      }
    }
  }

Error Response:

  {
    "error": {
      "root_cause": [
        {
          "type": "aggregation_execution_exception",
          "reason": "Invalid aggregation order path [reversed_entities>nested_syndicated_items>total_audience]. Buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end."
        }
      ],
      "type": "search_phase_execution_exception",
      "reason": "all shards failed",
      "phase": "query",
      "grouped": true,
      "failed_shards": [
        {
          "shard": 0,
          "index": "mediaitem-20200626102844",
          "node": "4THR09FZSzK9RhOtdu6gqQ",
          "reason": {
            "type": "aggregation_execution_exception",
            "reason": "Invalid aggregation order path [reversed_entities>nested_syndicated_items>total_audience]. Buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end."
          }
        }
      ]
    },
    "status": 500
  }

Any suggestions is much appreciated.

Thanks
Kun