Ordering the result of aggregation

Elastic Stack Elasticsearch
1 
GET testusage/_search?size=0
{
  "query": {
    "bool": {
      "filter": {"range": {
        "str_login_time": {
          "gte": "2017-03-16",                 
		      "lte": "2018-03-17"										
        }
      }}
    }
  },
  "aggs": {
    "By Time": {
      "terms": {
        "field": "str_time.keyword",
        "size": 24
      }
    }
  }
}

The output:

  "aggregations": {
    "By Time": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "12:00AM-1:00PM",
          "doc_count": 67222
        },
        {
          "key": "11:00AM-12:00AM",
          "doc_count": 60576
        },
        {
          "key": "1:00PM-2:00PM",
          "doc_count": 55583
        },
        {
          "key": "4:00PM-5:00PM",
          "doc_count": 49822
        },
        {
          "key": "5:00PM-6:00PM",
          "doc_count": 49182
        },
        {
          "key": "6:00PM-7:00PM",
          "doc_count": 36849
        },
        {
          "key": "3:00PM-4:00PM",
          "doc_count": 33488
        },
        {
          "key": "10:00AM-11:00AM",
          "doc_count": 33424
        },
        {
          "key": "2:00PM-3:00PM",
          "doc_count": 25477
        },
        {
          "key": "7:00PM-8:00PM",
          "doc_count": 24724
        },
        {
          "key": "8:00PM-9:00PM",
          "doc_count": 16351
        },
        {
          "key": "9:00AM-10:00AM",
          "doc_count": 14356
        },
        {
          "key": "9:00PM-10:00PM",
          "doc_count": 10088
        },
        {
          "key": "8:00AM-9:00AM",
          "doc_count": 9313
        },
        {
          "key": "10:00PM-11:00PM",
          "doc_count": 5788
        },
        {
          "key": "7:00AM-8:00AM",
          "doc_count": 5738
        },
        {
          "key": "11:00PM-12:00PM",
          "doc_count": 3669
        },
        {
          "key": "6:00AM-7:00AM",
          "doc_count": 2671
        },
        {
          "key": "0:00AM-1:00AM",
          "doc_count": 1763
        },
        {
          "key": "5:00AM-6:00AM",
          "doc_count": 1242
        },
        {
          "key": "1:00AM-2:00AM",
          "doc_count": 828
        },
        {
          "key": "2:00AM-3:00AM",
          "doc_count": 481
        },
        {
          "key": "4:00AM-5:00AM",
          "doc_count": 468
        },
        {
          "key": "3:00AM-4:00AM",
          "doc_count": 383
        }
      ]
    }
  }

I need to order the result by key
as

1:00AM-2:00AM
2:00AM-3:00AM
3:00AM-4:00AM
4:00AM-5:00AM
5:00AM-6:00AM
6:00AM-7:00AM
7:00AM-8:00AM
8:00AM-9:00AM
9:00AM-10:00AM
10:00AM-11:00AM
11:00AM-12:00AM
12:00AM-1:00PM
1:00PM-2:00PM
2:00PM-3:00PM
3:00PM-4:00PM
4:00PM-5:00PM
5:00PM-6:00PM
6:00PM-7:00PM
7:00PM-8:00PM
8:00PM-9:00PM
9:00PM-10:00PM
10:00PM-11:00PM
11:00PM-12:00PM
0:00AM-1:00AM
2

The terms aggregation accepts an order parameter, that allows you to sort the buckets however you wish to sort them.

In this case, you could nest a metric min aggregation inside your terms aggregation to find the oldest datetime in each bucket, and then sort on that datetime. The resulting request would look like this:

GET testusage/_search?size=0
{
  "query": {
    "bool": {
      "filter": {"range": {
        "str_login_time": {
          "gte": "2017-03-16",                 
		      "lte": "2018-03-17"										
        }
      }}
    }
  },
  "aggs": {
    "By Time": {
      "terms": {
        "field": "str_time.keyword",
        "size": 24,
        "order": {
          "min_time": "asc"
        }
      },
      "aggs": {
        "min_time": {
          "min": {
            "field": "str_login_time"
          }
        }
      }
    }
  }
}

This will only work if your first day of data contains a document for each hour. Otherwise, the order will get mixed up again.

If that's not the case, what you could do is add a numerical equivalent of str_time to your documents, and then sort on the minimum of that value. If it's not possible to change your documents, you may need to resort to scripting to calculate a value to sort on, which is going to be computationally expensive.

1 Like
3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.