Elasticsearch paginating a sorted, aggregated result

Question

As far as I'm aware, there isn't a way to do something like the following in Elasticsearch:

SELECT * FROM myindex
GROUP BY agg_field1, agg_field2, agg_field3 // aggregation
ORDER BY order_field1, order_field2, order_field3 // sort
LIMIT 1000, 5000 // paginate -- get page 6 of size 1000 records

Here are some related documents regarding this:

• https://www.elastic.co/guide/en/elasticsearch/reference/master/search-aggregations-bucket-terms-aggregation.html
• https://discuss.elastic.co/t/elasticsearch-aggregation-order-by-top-hit-score-with-partitions/102228
• https://github.com/elastic/elasticsearch/issues/21487

Is there a way to do the above in Elasticsearch? The one limitation we have is we will never have more than 10M records, so we (hopefully) shouldn't run into memory errors. My thinking was to do it as follows:

• Do an aggregation query
• Get the number of results from it
• Split it into N segments based on the results and page size we want
• Rerun the query with the above segments

What would be the best way to accomplish this? In your answer/suggestion, could you please post some sample code relating to how the above SQL query could be done in ES?

---

As an update to this question, here is a public index to test with:

# 5.6
e=Elasticsearch('https://search-testinges-fekocjpedql2f3rneuagyukvy4.us-west-1.es.amazonaws.com')
e.search('testindex')

# 6.4 (same data as above)
e = Elasticsearch('https://search-testinges6-fycj5kjd7l5uyo6npycuashch4.us-west-1.es.amazonaws.com')
e.search('testindex6')

It has 10,000 records. Feel free to test with it:

The query that I'm looking to do is as follows (in sql):

SELECT * FROM testindex
GROUP BY store_url, status, title
ORDER BY title ASC, status DESC
LIMIT 100 OFFSET 6000

In other words, I'm looking to sort an aggregated result (with multiple aggregations) and get an offset.

Answer 1

The composite aggregation might help here as it allows you to group by multiple fields and then paginate over the results. The only thing that it doesn't let you do is to jump at a given offset, but you can do that by iterating from your client code if at all necessary.

So here is a sample query to do that:

POST testindex6/_search
{
  "size": 0,
  "aggs": {
    "my_buckets": {
      "composite": {
        "size": 100,
        "sources": [
          {
            "store": {
              "terms": {
                "field": "store_url"
              }
            }
          },
          {
            "status": {
              "terms": {
                "field": "status",
                "order": "desc"
              }
            }
          },
          {
            "title": {
              "terms": {
                "field": "title",
                "order": "asc"
              }
            }
          }
        ]
      },
      "aggs": {
        "hits": {
          "top_hits": {
            "size": 100
          }
        }
      }
    }
  }
}

In the response you'll see and after_key structure:

  "after_key": {
    "store": "http://google.com1087",
    "status": "OK1087",
    "title": "Titanic1087"
  },

It's some kind of cursor that you need to use in your subsequent queries, like this:

{
  "size": 0,
  "aggs": {
    "my_buckets": {
      "composite": {
        "size": 100,
        "sources": [
          {
            "store": {
              "terms": {
                "field": "store_url"
              }
            }
          },
          {
            "status": {
              "terms": {
                "field": "status",
                "order": "desc"
              }
            }
          },
          {
            "title": {
              "terms": {
                "field": "title",
                "order": "asc"
              }
            }
          }
        ],
        "after": {
          "store": "http://google.com1087",
          "status": "OK1087",
          "title": "Titanic1087"
        }
      },
      "aggs": {
        "hits": {
          "top_hits": {
            "size": 100
          }
        }
      }
    }
  }
}

And it will give you the next 100 buckets. Hopefully this helps.

UPDATE:

If you want to know how many buckets in total there is going to be, the composite aggregation won't give you that number. However, since the composite aggregation is nothing else than a cartesian product of all the fields in its sources, you can get a good approximation of that total number by also returning the ]cardinality](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-cardinality-aggregation.html) of each field used in the composite aggregation and multiplying them together.

  "aggs": {
    "my_buckets": {
      "composite": {
        ...
      }
    },
    "store_cardinality": {
      "cardinality": {
        "field": "store_url"
      }
    },
    "status_cardinality": {
      "cardinality": {
        "field": "status"
      }
    },
    "title_cardinality": {
      "cardinality": {
        "field": "title"
      }
    }
  }

We can then get the total number of buckets by multiplying the figure we get in store_cardinality, status_cardinality and title_cardinality together, or at least a good approximation thereof (it won't work well on high-cardinality fields, but pretty well on low-cardinality ones).

Answer 2

Field collapsing is the answer.

Field collapsing feature is used when we want to group the hits on a specific field (as in group by agg_field).

Before Elastic 6, the way to group the fields is to use aggregation. This approach was lacking an ability to do efficient paging.

But now, with the field collapse provided out of the box by elastic, it is pretty easy.

Below is a sample query with field collapse taken from above link.

GET /twitter/_search
{
  "query": {
      "match": {
          "message": "elasticsearch"
      }
  },
  "collapse" : {
      "field" : "user", 
      "inner_hits": {
          "name": "last_tweets", 
          "size": 5, 
          "sort": [{ "date": "asc" }] 
      },
      "max_concurrent_group_searches": 4 
  },
  "sort": ["likes"]

}