Sort Aggregation in elastic seach?

Question

I have use case where I need to get all unique user ids from Elasticsearch and it should be sorted by timestamp.

What I'm using currently is composite term aggregation with sub aggregation which will return the latest timestamp.

(I can't sort it in client side as it slow down the script)

Sample data in elastic search

{
  "_index": "logstash-2020.10.29",
  "_type": "doc",
  "_id": "L0Urc3UBttS_uoEtubDk",
  "_version": 1,
  "_score": null,
  "_source": {
    "@version": "1",
    "@timestamp": "2020-10-29T06:56:00.000Z",
    "timestamp_string": "1603954560",
    "search_query": "example 3",
    "user_uuid": "asdfrghcwehf",
    "browsing_url": "https://www.google.com/search?q=example+3",
  },
  "fields": {
    "@timestamp": [
      "2020-10-29T06:56:00.000Z"
    ]
  },
  "sort": [
    1603954560000
  ]
}

Expected Output:

[
        {
          "key" : "bjvexyducsls",
          "doc_count" : 846,
          "1" : {
            "value" : 1.603948557E12,
            "value_as_string" : "2020-10-29T05:15:57.000Z"
          }
        },
        {
          "key" : "lhmsbq2osski",
          "doc_count" : 420,
          "1" : {
            "value" : 1.6039476E12,
            "value_as_string" : "2020-10-29T05:00:00.000Z"
          }
        },
        {
          "key" : "m2wiaufcbvvi",
          "doc_count" : 1,
          "1" : {
            "value" : 1.603893635E12,
            "value_as_string" : "2020-10-28T14:00:35.000Z"
          }
        },
        {
          "key" : "rrm3vd5ovqwg",
          "doc_count" : 1,
          "1" : {
            "value" : 1.60389362E12,
            "value_as_string" : "2020-10-28T14:00:20.000Z"
          }
        },
        {
          "key" : "x42lk4t3frfc",
          "doc_count" : 72,
          "1" : {
            "value" : 1.60389318E12,
            "value_as_string" : "2020-10-28T13:53:00.000Z"
          }
        }
      ]

can you please provide some sample index data, mapping, and expected search query results? Do you have a `date` field in your sample index data? — ESCoder, Oct 29 '20 at 09:44
added the sample data question. need something like sorted list of aggregation. — Sanker, Oct 29 '20 at 09:57

score 1 · Accepted Answer · answered Oct 29 '20 at 09:57

1

Adding a working example with index data, mapping, search query, and search result

Index Mapping:

{
  "mappings":{
    "properties":{
      "user":{
        "type":"keyword"
      },
      "date":{
        "type":"date"
      }
    }
  }
}

Index Data:

{
  "date": "2015-01-01",
  "user": "user1"
}
{
  "date": "2014-01-01",
  "user": "user2"
}
{
  "date": "2015-01-11",
  "user": "user3"
}

Search Query:

{
  "size": 0,
  "aggs": {
    "user_id": {
      "terms": {
        "field": "user",
        "order": {
          "sort_user": "asc"
        }
      },
      "aggs": {
        "sort_user": {
          "min": {
            "field": "date"
          }
        }
      }
    }
  }
}

Search Result:

"aggregations": {
    "user_id": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "user2",
          "doc_count": 1,
          "sort_user": {
            "value": 1.3885344E12,
            "value_as_string": "2014-01-01T00:00:00.000Z"
          }
        },
        {
          "key": "user1",
          "doc_count": 1,
          "sort_user": {
            "value": 1.4200704E12,
            "value_as_string": "2015-01-01T00:00:00.000Z"
          }
        },
        {
          "key": "user3",
          "doc_count": 1,
          "sort_user": {
            "value": 1.4209344E12,
            "value_as_string": "2015-01-11T00:00:00.000Z"
          }
        }
      ]
    }

answered Oct 29 '20 at 09:57

ESCoder

15,431
2
19
42

@Sanker please go through my answer, and let me know if this resolves your issue ? – ESCoder Oct 29 '20 at 09:58
sure thanks... ! What if I have 10k + users ... ? can I use it with composite aggregation or any kind of pagination option available?? – Sanker Oct 29 '20 at 10:07
@Sanker I think you can use Composite Aggregation as it allows pagination within aggregated results, to know more about this you can refer to this https://www.elastic.co/guide/en/elasticsearch/reference/7.9/search-aggregations-bucket-composite-aggregation.html – ESCoder Oct 29 '20 at 10:13
1

thanks I'll. but what I understood is composite aggregation with sorting is not possible... anyway I'll have a try and update. – Sanker Oct 29 '20 at 10:15
1

Hello, I wrote an article on pagination for aggregation. I hope it could help https://spoon-elastic.com/all-elastic-search-post/pagination-aggregation-elasticsearch/ – Jaycreation Oct 29 '20 at 10:16
sure I'll check it out – Sanker Oct 29 '20 at 10:41
@Jaycreation that was indeed a great article , if you liked my answer, then please don't forget to upvote my answer as well – ESCoder Oct 29 '20 at 10:43
2

don't worry Bhavya upvote your answer was the first thing I did ;) – Jaycreation Oct 29 '20 at 10:50
@Sanker if my answer helped you resolve your issue, then please don't forget to upvote and accept my answer , and please go through my other answer https://stackoverflow.com/a/64590969/10348758, to your new question – ESCoder Oct 29 '20 at 12:19
@Sanker any update and luck here? Would be great if you can upvote and accept my answer if it helped you :) – ESCoder Oct 31 '20 at 04:55
@Sanker thank u for accepting my answer, can you please upvote my answer as well – ESCoder Nov 03 '20 at 17:20

Sort Aggregation in elastic seach?

1 Answers1

Linked