title: Sort multi-bucket aggregation by source fields inside inner multi-bucket aggregation
body: <p><em><strong>TL;DR:</strong> Using an inner multi-bucket aggregation (<code>top_hits</code> with <code>size: 1</code>) inside an outer multi-bucket aggregation, is it possible to sort the buckets of the outer aggregation by the data in the inner buckets?</em></p>
  
  <hr>
  
  <p>I have the following index mappings</p>
  
  <pre><code>{
    "parent": {
      "properties": {
        "children": {
          "type": "nested",
          "properties": {
            "child_id": { "type": "keyword" }
          }
        }
      }
    }
  }
  </code></pre>
  
  <p>and each child (in data) has also the properties <code>last_modified: Date</code> and <code>other_property: String</code>.</p>
  
  <p>I need to fetch a list of children (of all the parents but without the parents), but only the one with the latest <code>last_modified</code> per each <code>child_id</code>. Then I need to sort and paginate those results to return manageable amounts of data.</p>
  
  <p>I'm able to get the data and paginate over it with a combination of <code>nested</code>, <code>terms</code>, <code>top_hits</code>, and <code>bucket_sort</code> aggregations (and also get the total count with <code>cardinality</code>)</p>
  
  <pre><code>{
    "query": {
      "match_all": {}
    },
    "size": 0,
    "aggs": {
      "children": {
          "nested": {
              "path": "children"
          },
          "aggs": {
              "totalCount": {
                  "cardinality": {
                      "field": "children.child_id"
                  }
              },
              "oneChildPerId": {
                  "terms": {
                      "field": "children.child_id",
                      "order": { "_term": "asc" },
                      "size": 1000000
                  },
                  "aggs": {
                      "lastModified": {
                          "top_hits": {
                              "_source": [
                                  "children.other_property"
                              ],
                              "sort": {
                                  "children.last_modified": {
                                      "order": "desc"
                                  }
                              },
                              "size": 1
                          }
                      },
                      "paginate": {
                          "bucket_sort": {
                              "from": 36,
                              "size": 3
                          }
                      }
                  }
              }
          }
      }
    }
  }
  </code></pre>
  
  <p>but after more than a solid day of going through the docs and experimenting, I seem to be no closer to figuring out, how to sort the buckets of my <code>oneChildPerId</code> aggregation by the <code>other_property</code> of that single child retrieved by <code>lastModified</code> aggregation.</p>
  
  <p><strong>Is there a way to sort a multi-bucket aggregation by results in a nested multi-bucket aggregation?</strong></p>
  
  <hr>
  
  <p>What I've tried:</p>
  
  <ul>
  <li>I thought I could use <code>bucket_sort</code> for that too, but apparently its <code>sort</code> can only be used with paths containing other single-bucket aggregations and ending in a metic one.</li>
  <li>I've tried to find a way to somehow transform the 1-result multi-bucket of <code>lastModified</code> into a single-bucket, but haven't found any.</li>
  </ul>
  
  <hr>
  
  <p><em>I'm using ElasticSearch 6.8.6 (the <code>bucket_sort</code> and similar tools weren't available in ES 5.x and older).</em></p>