sorting results of aggregation in elasticsearch

Question

I have written a query to get the latest records per Id from my ES. But the result of this query only does the inner sorting and chooses the latest records. Now I need the results that I got to be sorted by date.

This is my query:

{
   "size":0,
   "query":{
      "bool":{
         "must":[
            {
               "match":{
                  "base":"XYZ"
               }
            },
            {
               "match":{
                  "Type":"low"
               }
            }
         ]
      }
   },
   "aggs":{
      "sources":{
         "terms":{
            "field":"Id"
         },
         "aggs":{
            "latest":{
               "top_hits":{
                  "size":1,

                  "_source":{
                     "includes":[
                        "base",
                        "Type"
                     ]
                  },
                  "sort":{
                     "orderDate":"desc"
                  }
               }
            }
         }
      }
   }
}

sample doc: { "_index": "XXXX", "_type": "logs", "_id": "bGd3qWgBnyB5Q6tB0Zjb", "_version": 1, "_score": null, "_source": { "qtyFulfilled": 1.5, "@timestamp": "2019-02-01T14:30:27.784Z", "base": "BTC", "Type": "LIMIT", }, "fields": { "@timestamp": [ "2019-02-01T14:30:27.784Z" ], "orderDate": [ "2019-02-01T14:30:27.727Z" ] } — Sty
– Sty, Commented Feb 1, 2019 at 16:35
The results of my query give my the latest doc of each Id. like for id 1 the latest doc, then for id 2 the latest doc. But I need the order of this end result to be sorted as well. Like a sorting on the result of the aggregation. — Sty
– Sty, Commented Feb 1, 2019 at 16:38
Hey, you shared a result of the query. I wanted to know how all the fields you are querying on look like in a document stored in elastic. — Nishant
– Nishant, Commented Feb 2, 2019 at 1:50

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

What you are trying to do is sorting the buckets by another another bucket. You can achieve this in two ways:

(a) By bucket_sort aggregation

(b) by using order param for terms aggregation referencing to another bucket.

(a) bucket_sort aggregation

This aggregation sorts the buckets of its parent multi-bucket aggregation. You can specify the field(s) based on which the buckets will be sorted. Using this, the query for your case will be:

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "base": "XYZ"
          }
        },
        {
          "match": {
            "Type": "low"
          }
        }
      ]
    }
  },
  "aggs": {
    "source": {
      "terms": {
        "field": "id"
      },
      "aggs": {
        "latest": {
          "top_hits": {
            "size": 1,
            "_source": {
              "includes": [
                "base",
                "Type"
              ]
            },
            "sort": {
              "orderDate": "desc"
            }
          }
        },
        "latestOrder": {
          "max": {
            "field": "orderDate"
          }
        },
        "bucket_sort_order": {
          "bucket_sort": {
            "sort": {
              "latestOrder": {
                "order": "desc"
              }
            }
          }
        }
      }
    }
  },
  "post_filter": {
    "term": {
      "status": "yes"
    }
  }
}

In the query above I have used a max aggregation named as latestOrder. This aggregation gives us the value for latest orderDate. If we look as the top hit aggregation the document returned by it will have the same orderDate as returned by the max aggeration i.e. latestOrder. The reason being we have ordered top hit by orderDate in desc and limited the size to one, which is equivalent to max orderDate.

latestOrder works as a sorting field for us which is then used in the bucket_sort aggregation to sort the parent buckets which are the buckets returned by terms aggregation.

(b) `order` param in terms agg

We use the similar approach as above. We use max aggregation latestOrder and reference it in order param of terms aggregation. So the query will be:

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "base": "XYZ"
          }
        },
        {
          "match": {
            "Type": "low"
          }
        }
      ]
    }
  },
  "aggs": {
    "source": {
      "terms": {
        "field": "id",
        "order": {
          "latestOrder": "desc"
        }
      },
      "aggs": {
        "latest": {
          "top_hits": {
            "size": 1,
            "_source": {
              "includes": [
                "base",
                "Type",
                "orderDate"
              ]
            },
            "sort": {
              "orderDate": "desc"
            }
          }
        },
        "latestOrder": {
          "max": {
            "field":"orderDate"
          }
        }
      }
    }
  },
  "post_filter": {
    "term": {
      "status": "yes"
    }
  }
}

UPDATE in queries: Based on discussion in comments added post_filter.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 2, 2019 at 2:56

Nishant

7,9121 gold badge24 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

30 Comments

Sty Over a year ago

Can I filter the result that i get from the above aggregation to get only the docs with base=" xyz" ?? I tried post_filter an bucket_filters but no success.

Nishant Over a year ago

It's doing the same. I haven't change the query that you were using to filter the results. The query already has a condition to match base="XYZ". You can change XYZ to xyz.

Nishant Over a year ago

Also I request you to please upvote and accept the answer if it solved your problem.

Sty Over a year ago

This is a sample data. I need to query the result that I get based on terms in the buckets. The query I did earlier is required and based on that result, the aggregation was done. Now post the aggregation I need to do another filter to get out some docs only that have a specific value in one of the fields that are present in includes [] . Eg. after the aggregation I got docs with different status :some yes and some no, i need only the ones with yes.

Nishant Over a year ago

@sox please post separate question for the same with the relevant details.

|

Collectives™ on Stack Overflow

sorting results of aggregation in elasticsearch

1 Answer 1

(a) bucket_sort aggregation

(b) `order` param in terms agg

30 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

(a) bucket_sort aggregation

(b) order param in terms agg

30 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

(b) `order` param in terms agg