CODEWITHSUNDEEP
elasticsearchaggregationelasticsearch-aggregation
Joe Hasbani
Joe Hasbani

Reputation: 39

How do I sort a top hit in a nested aggregate by a field in the root document?

I am performing a top hit aggregation within a nested aggregation. I wish to order the top hits by a timestamp field in the root document so as I get the most recent version of the nested document (which may be indexed into several root documents). While the syntax seems to let me do this, the extracted sort keys seem to be 'junk'; it's the same huge number for every document. (The following is only a snippet of a much more complex aggregation -- otherwise, the approach might make no sense -- but it has the essence of my problem.)

{
   "size": 0,
   "aggs": {
      "nested_doc": {
         "nested": {
            "path": "nested_doc"
         },
         "aggs": {
            "most_recent": {
               "top_hits": {
                  "sort": "_index_time"
               }
            }
         }
      }
   }
}

In my results, the sort key does not match any such "_index_time", and changing the sort order has no effect. "_index_time" is declared to be an integer on the root document. If I change "_index_time" in the query to some meaningless string, the query errors out, so I know it is making sense of what I am asking, but it's not performing the sort -- or, at least, it's not getting the right data to sort on.

How do I correctly sort nested aggregations on root properties?

Alternatively, I tried adding a copy_to: 'nested_doc._index_time' to the '_index_time' declaration, and while that let me specify 'nested_doc._index_time' as the sort key, it still resulted in meaningless sort keys. Is the field just not correctly defined?

    _index_time: { type: 'integer'},

Upvotes: 2

Views: 1803

Answers (1)

Aman Garg
Aman Garg

Reputation: 3290

I have faced the same problem, then I came with one solution within the nested aggregation. In this we can add a parallel aggregation with the max aggregation on index_time. And then sort it based on the max aggregation you have added. You just need to adjust the order statement according to your aggregation on first level.

Please look into the below query:

{
   "size": 0,
   "aggs": {
      "nested_doc": {
         "nested": {
            "path": "nested_doc"
         },
         "order": {
            "max_date": "asc"
         }
         "aggs": {
            "most_recent": {
               "top_hits": {
                  "sort": "_index_time"
               }
            },
            "max_date": {
                 "max": {
                    "field": "_index_time"
                 }
             }

         }
      }
   }
}

Hope this will solve your problem.

Upvotes: 1

Related Questions