How to add paging in Elasticsearch's aggregation？

user_8269493 0
Viewed 5821

I have an elasticsearch request as below：

{
    "size":0,
    "aggs":{
        "group_by_state":{
            "terms":{
                "field":"poi_id"
            },
            "aggs":{
                "sum(price)":{
                    "sum":{
                        "field":"price"
                    }
                }
            }
        }
    }
}

I want to add paging in this requst, just like

select poi_id, sum(price) from table group by poi_id limit 0,2

I've searched a lot, and found a link about it:https://github.com/elastic/elasticsearch/issues/4915.

But still I didn't get the implementation method.

Is there any way to implement it by Elasticsearch itself but not my application?

elasticsearch-aggregation elasticsearch paging
3 Answers

Late to the party, but just discovered 'composite' aggregations in v6.3+. These allow:
1. A more 'Sql like' grouping
2. Pagination by use of the 'after_key'.
Saved our day, hope it will help others too.

Example, getting number of hits per hour between 2 dates, grouped on 5 fields:

GET myindex-idx/_search
{
  "query": {
    "bool": {
      "must": [
        {"match": {"docType": "myDOcType"}}, 
        {"range": {
          "@date": {"gte": "2019-06-19T21:00:00", "lt": "2019-06-19T22:00:00"}
          }
        }
      ]
    }
  }, 
  "size": 0, 
  "aggs": {
    "mybuckets": {
      "composite": {
        "size": 100, 
        "sources": [
          {"@date": {
            "date_histogram": {
              "field": "@date", 
              "interval": "hour", 
              "format": "date_hour"}
            }
          }, 
          {"field_1": {"terms": {"field": "field_1"}}}, 
          {"field_2": {"terms": {"field": "field_2"}}}, 
          {"field_3": {"terms": {"field": "field_3"}}}, 
          {"field_4": {"terms": {"field": "field_4"}}}, 
          {"field_5": {"terms": {"field": "field_5"}}}
        ]
      }
    }
  }
}

Produces:

{
  "took": 255,
  "timed_out": false,
  "_shards": {
    "total": 80,
    "successful": 80,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 46989,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "mybuckets": {
      "after_key": {
        "@date": "2019-06-19T21",
        "field_1": 262,
        "field_2": 347,
        "field_3": 945,
        "field_4": 2258,
        "field_5": 0
      },
      "buckets": [
        {
          "key": {
            "@date": "2019-06-19T21",
            "field_1": 56,
            "field_2": 106,
            "field_3": 13224,
            "field_4": 46239,
            "field_5": 0
          },
          "doc_count": 3
        },
        {
          "key": {
            "@date": "2019-06-19T21",
            "field_1": 56,
            "field_2": 106,
            "field_3": 32338,
            "field_4": 76919,
            "field_5": 0
          },
          "doc_count": 2
        },
        ....

Following paging query issued like this, using the 'after_key object in the queries 'after' object:

GET myindex-idx/_search
{
  "query": {
    "bool": {
      "must": [
        {"match": {"docType": "myDOcType"}}, 
        {"range": {
          "@date": {"gte": "2019-06-19T21:00:00", "lt": "2019-06-19T22:00:00"}
          }
        }
      ]
    }
  }, 
  "size": 0, 
  "aggs": {
    "mybuckets": {
      "composite": {
        "size": 100, 
        "sources": [
          {"@date": {
            "date_histogram": {
              "field": "@date", 
              "interval": "hour", 
              "format": "date_hour"}
            }
          }, 
          {"field_1": {"terms": {"field": "field_1"}}}, 
          {"field_2": {"terms": {"field": "field_2"}}}, 
          {"field_3": {"terms": {"field": "field_3"}}}, 
          {"field_4": {"terms": {"field": "field_4"}}}, 
          {"field_5": {"terms": {"field": "field_5"}}}
        ],
      "after": {
        "@date": "2019-06-19T21",
        "field_1": 262,
        "field_2": 347,
        "field_3": 945,
        "field_4": 2258,
        "field_5": 0
        }
      }
    }
  }
}

This pages through the results, until the mybuckets returns empty

user_14751310
user_80978040
Related