ElasticSearch multiple terms aggregation order

Question

I have a document structure which describes a container, some of its fields are:

containerId -> Unique Id,String
containerManufacturer -> String
containerValue -> Double
estContainerWeight ->Double
actualContainerWeight -> Double

I want to run a search aggregation which has two levels of terms aggregations on the two weight fields, but in descending order of the weight fields, like below:

{
  "size": 0,
  "aggs": {
    "by_manufacturer": {
      "terms": {
        "field": "containerManufacturer",
        "size": 10,
        "order": {"estContainerWeight": "desc"} //Cannot do this
      },
      "aggs": {
        "by_est_weight": {
          "terms": {
            "field": "estContainerWeight",
            "size": 10,
            "order": { "actualContainerWeight": "desc"} //Cannot do this
          },
          "aggs": {
            "by_actual_weight": {
              "terms": {
                "field": "actualContainerWeight",
                "size": 10
              },
              "aggs" : {
                "container_value_sum" : {"sum" : {"field" : "containerValue"}}
              }
            }
          }
        }
      }
    }
  }
}

Sample documents:

{"containerId":1,"containerManufacturer":"A","containerValue":12,"estContainerWeight":5.0,"actualContainerWeight":5.1}
{"containerId":2,"containerManufacturer":"A","containerValue":24,"estContainerWeight":5.0,"actualContainerWeight":5.2}
{"containerId":3,"containerManufacturer":"A","containerValue":23,"estContainerWeight":5.0,"actualContainerWeight":5.2}
{"containerId":4,"containerManufacturer":"A","containerValue":32,"estContainerWeight":6.0,"actualContainerWeight":6.2}
{"containerId":5,"containerManufacturer":"A","containerValue":26,"estContainerWeight":6.0,"actualContainerWeight":6.3}
{"containerId":6,"containerManufacturer":"A","containerValue":23,"estContainerWeight":6.0,"actualContainerWeight":6.2}

Expected Output(not complete):

{
  "by_manufacturer": {
    "buckets": [
      {
        "key": "A",
        "by_est_weight": {
          "buckets": [
            {
              "key" : 5.0,
              "by_actual_weight" : {
                "buckets" : [
                  {
                    "key" : 5.2,
                    "container_value_sum" : {
                      "value" : 1234 //Not actual sum
                    }
                  },
                  {
                    "key" : 5.1,
                    "container_value_sum" : {
                      "value" : 1234 //Not actual sum
                    }
                  }
                ]
              }
            },
            {
              "key" : 6.0,
              "by_actual_weight" : {
                "buckets" : [
                  {
                    "key" : 6.2,
                    "container_value_sum" : {
                      "value" : 1234 //Not actual sum
                    }
                  },
                  {
                    "key" : 6.3,
                    "container_value_sum" : {
                      "value" : 1234 //Not actual sum
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    ]
  }
}

However, I cannot order by the nested aggregations. (Error: 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...)

For example, for the above sample output, I have no control on the buckets generated if I introduce a size on the terms aggregations (which I will have to do if my data is large),so I would like to only get the top N weights for each terms aggregation.

Is there a way to do this ?

Answer 1

If I understand your problem correctly, you would like to sort the manufacturer terms in decreasing order of the estimated weights of their containers and then each bucket of "estimated weight" in decreasing order of their actual weight.

{
  "size": 0,
  "aggs": {
    "by_manufacturer": {
      "terms": {
        "field": "containerManufacturer",
        "size": 10
      },
        "by_est_weight": {
          "terms": {
            "field": "estContainerWeight",
            "size": 10,
            "order": {
              "_term": "desc"       <--- change to this
            }
          },
            "by_actual_weight": {
              "terms": {
                "field": "actualContainerWeight",
                "size": 10,
                "order" : {"_term" : "desc"}   <----- Change to this
              },
              "aggs": {
                "container_value_sum": {
                  "sum": {
                    "field": "containerValue"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}