Sort and order on elasticsearch nested aggregation-CodePudding

I want to aggregate field of a nested field for each bucket of a bucket aggregation, and sort and filter buckets with subaggregation results. I have this query that worked on non nested field.

aggs = {
    "account": {
        "terms": {
            "field": "to.keyword",
            "order": { "total_amount": "desc"},
            "size": 100,
            "min_doc_count": 20,
        },
        "aggs": {
            "total_amount": {
                "sum": {
                    "field": "balance_diffs.diff_balance_usd"
                }
            },
            "neg_amount_filter": {
                "bucket_selector": {
                    "buckets_path": {
                        "totalAmount": "total_amount"
                    },
                    "script": "params.totalAmount > 0"
                }
            }
        }
    }
}

I tried to converted to a nested aggregation but I have a 2 problems :

aggs = {
        "account": {
            "terms": {
                "field": "to.keyword",
                "order": { "amount": "desc"},
                "size": 100,
                "min_doc_count": 20,
            },
            "aggs": {
                "total_amount": {
                    "nested": {
                        "path": "balance_diffs"
                    },
                    "aggs": {
                        "amount": {
                            "sum": {
                                "field": "balance_diffs.diff_balance_usd"
                            },
                        },
                        "accounts_bucket_sort": {
                            "bucket_sort": {
                                "sort": [
                                    { "amount.value": { "order": "desc" } } 
                                ],
                            }
                        }
                    }
                },
            }
        }
    }

1- "total_amount" can't be accessed at top level in the order statement

2- the "neg_amount_filter" create a "search_phase_execution_exception" exception

If I remove these two parts, the agg works but I'm not able to filter and sort the results.

I also tried with reverse_nested and "multi_bucket_emulation" as describe in another post, but I still have the "search_phase_execution_exception" error

aggs = {
    "contract": {
        "terms": {
            "field": "to.keyword",
            "size": 100,
            "min_doc_count": 20,
        },
        "aggs": {
            "total_profit": {
                "nested": {
                    "path": "balance_diffs"
                },
                "aggs": {
                    "profit": {
                        "sum": {
                            "field": "balance_diffs.diff_balance_usd"
                        },
                    },
                    "back_to_parent": {
                        "reverse_nested": {},
                        "aggs": {
                            "multi_bucket_emulator": {
                                "filters": {
                                    "filters": {
                                            "placeholder_match_all_query": {
                                            "match_all": {}
                                        }
                                    }
                                },
                                "aggs": {
                                    "contracts_bucket_sort": {
                                        "bucket_sort": {
                                            "sort": [
                                                { "profit": { "order": "desc" } } 
                                            ],
                                        }
                                    }
                                }
                            },
                        }
                    }
                }
            },
        }
    }
}

CodePudding user response：

You can order terms aggregation by nested sub aggregation. In order you need to provide full path "total_amount>amount" instead of "amount".

{
  "aggs": {
    "account": {
      "terms": {
        "field": "to.keyword",
        "order": {
          "total_amount>amount": "desc"
        },
        "size": 100,
        "min_doc_count": 20
      },
      "aggs": {
        "total_amount": {
          "nested": {
            "path": "balance_diffs"
          },
          "aggs": {
            "amount": {
              "sum": {
                "field": "balance_diffs.diff_balance_usd"
              }
            },
            "accounts_bucket_sort": {
              "bucket_sort": {
                "sort": [
                  {
                    "amount.value": {
                      "order": "desc"
                    }
                  }
                ]
              }
            }
          }
        }
      }
    }
  }
}