Order Terms aggregations buckets by top hits sub aggregation

Question

Order Terms aggregations buckets by top hits sub aggregation doc source

I have a query,

Context: These is one Tour Packages Index, with one of property "set_url", multiple packages with same set_url are called family packages.

Requirement: Getting seo packages in sorting order of any field lets say duration (indexed as type: integer), but sorting will be based on min priced package per family and wants result in size of 20.

Problem statement in simple form: Lets say terms aggregator by set_url field produces 3 buckets A, B, C and each bucket has 3 packages, (A1,A2,A3), (B1, B2, B3) and (C1, C2, C3), Now First I want to compute min priced package from each bucket lets say it comes out to be A2, B3, C1 then I want to sort buckets by duration field of relevant min priced packages per bucket i.e A2, B3 and C1 packages.

What I am trying to do:

Apply some filters

apply terms top level aggregation on key set_url, making family packages buckets, Now I want to order these buckets across based on min prices family package that comes from top hits aggregation and top hits aggregation is metric aggregation according of ES docs.

These is sub aggregation of type top hits that does sorting on price on ascending order and take one size to pick min prices family package.

Current Query:

{
   index: "seo_packages",
   type: "seo_package",
   body: {
   size: 0,
   query: {
     bool: {
       filter: {
         and: [{
                  terms: {
                    package_type: ["seo"]
                  }
               },
               {
                  terms: {
                    categories: ["tour"]
                  }
               },
               {
                 term: {
                   is_published: true
                 }
               },
             ],
        },
    }
},
aggs: {
  group_by_set_url: {
    terms: {
      field: "set_url",
      size: 10,
      order: {
        "min_price_expressions.how to access doc source property duration here" => "asc"
      }
    },
    aggs: {
      min_price_expressions: {
        top_hits: {
          sort: [{
            price_expression: {
              order: "asc"
            }
          }],
              _source: {
                includes: [: id,: duration]
              },
              size: 1
            },
          }
        }
      }
    }
  }
}

What should be order clause here to access top hits sub aggregation document's property?

Thanks in advance.

Problem statement in simple form: Lets say terms aggregator produces 3 buckets A, B, C and each bucket has 3 packages, (A1,A2,A3), (B1, B2, B3) and (C1, C2, C3), Now First I want to compute min prices packages from each bucket lets say it comes out to be A2, B3, C1 then I want to sort buckets by duration field of relevant min prices packages per bucket i.e A2, B3 and C1 packages. — PRAVEEN SHARMA

Nishant Nishant · Accepted Answer · 2019-05-23T17:13:05

Since you want to sort by min value from a family package, instead of using top_hits aggregation you can use min aggregation as a nested aggregation of group_by_set_url aggregation. You can then use the value of min aggregation to sort the group_by_set_url aggregation. So the aggregation will look as below:

{
  "query": {
    // add query here
  },
  "aggs": {
    "group_by_set_url": {
      "terms": {
        "field": "set_url",
        "size": 10,
        "order": {
          "min_duration": "asc"
        }
      },
      "aggs": {
        "min_duration": {
          "min": {
            "field": "duration"
          }
        }
      }
    }
  }
}

Order Terms aggregations buckets by top hits sub aggregation

1 Answers