CODEWITHSUNDEEP
elasticsearchelastic-stackdslquerydslelk
Sanker
Sanker

Reputation: 79

Sort Aggregation in elastic seach?

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"
          }
        }
      ]

Upvotes: 1

Views: 226

Answers (1)

Bhavya
Bhavya

Reputation: 16192

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"
          }
        }
      ]
    }

Upvotes: 1

Related Questions