ElasticSearch - Ordering aggregation by nested aggregation on nested field

Question

{
  "query": {
    "match_all": {}
  },
  "from": 0,
  "size": 0,
  "aggs": {
    "itineraryId": {
      "terms": {
        "field": "iid",
        "size": 2147483647,
        "order": [
          {
            "price>price>price.max": "desc"
          }
        ]
      },
      "aggs": {
        "duration": {
          "stats": {
            "field": "drn"
          }
        },
        "price": {
          "nested": {
            "path": "prl"
          },
          "aggs": {
            "price": {
              "filter": {
                "terms": {
                  "prl.cc.keyword": [
                    "USD"
                  ]
                }
              },
              "aggs": {
                "price": {
                  "stats": {
                    "field": "prl.spl.vl"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Here, I am getting the error:

"Invalid terms aggregation order path [price>price>price.max]. Terms 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. Sub-path [price] points to non single-bucket aggregation"

query works fine if I order by duration aggregation like

"order": [
      {
        "duration.max": "desc"
      }

So is there any way to Order aggregation by nested aggregation on nested field i.e something like below ?

"order": [
      {
        "price>price>price.max": "desc"
      }

This is not supported yet, but a community pull request has just been submitted to address this issue: github.com/elastic/elasticsearch/pull/24121 Original issue: github.com/elastic/elasticsearch/issues/16838 — Val
Yes, add the maximum price as a new field at the root level. — Val
Then you can have a max_price object with one price per currency "max_price": {"USD": 1000, "EUR": 1210, ...} — Val
No, because there will only be a single value per currency (always the max price for any nested currency). You can then add another max sub-aggregation on max_price.USD and sort the iid terms with it. — Val

J Aamish J Aamish · Accepted Answer · 2017-04-17T13:10:07

As Val has pointed out in the comments ES does not support it yet.

Till then you can first aggregate the nested aggregation and then use the reverse nested aggregation to aggregate the duration, that is present in the root of the document.

https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-reverse-nested-aggregation.html

ElasticSearch - Ordering aggregation by nested aggregation on nested field

1 Answers