# Pagination + Sorted Aggregations: Efficiently Retrieve Sorted List of Values?

**URL:** <https://discuss.elastic.co/t/pagination-sorted-aggregations-efficiently-retrieve-sorted-list-of-values/339325>\
**Category:** Elasticsearch\
**Created:** [July 26, 2023, 2:54pm UTC](https://discuss.elastic.co/t/pagination-sorted-aggregations-efficiently-retrieve-sorted-list-of-values/339325 "2023-07-26T14:54:10Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![openelasticsearch](https://avatars.discourse-cdn.com/v4/letter/o/e8c25b/32.png) [@openelasticsearch](https://discuss.elastic.co/u/openelasticsearch)\
**Post date:** [July 26, 2023, 2:54pm UTC](https://discuss.elastic.co/t/pagination-sorted-aggregations-efficiently-retrieve-sorted-list-of-values/339325/1 "2023-07-26T14:54:10Z")

</div>

Hi, I'm looking for some advice on the best way to implement an aggregation query that supports pagination and sorting.

**Quick Overview of My Documents & Desired Use Case:**  
I have indexes that contain documents with a number of relevant fields

```auto
{ user_id: 5, timestamp: 123456789, ... other fields ... },
{ user_id: 6, timestamp: 123456791, ... other fields ... }
{ user_id: 8, timestamp: 123456811, ... other fields ... }
{ user_id: 5, timestamp: 1234567891, ... other fields ... }

```

I'm trying to implement an aggregation query that will return a list of user\_ids sorted by most recent timestamp. Ideally, this aggregation will support pagination as `user_id` is a high cardinality field and there may be many documents that share the same user\_id. I want to avoid pulling all of the results at once due to speed and memory concerns.

For Example: Let's say I only want 1 user\_id per page of results & use the example documents I provided above.  
Page 1 -\> user\_id = 5  
Page 2 -\> user\_id = 8  
Page 3 -\> user\_id = 6

**What I've Tried so Far**

- Term/s Aggregation
- Composite Aggregation

Term/s aggregations have good support for sorting the result buckets & avoid duplicate user\_id results by grouping all documents containing the same user\_id into a bucket.

```auto
"users": {
      "terms": {
        "field": "user_id",
        "order": { "user_latest_timestamp.max": "desc" },
        "size": 200
      },
      "aggs": {
        "user_latest_timestamp": {
          "stats": { "field": "timestamp" }
        }
    }
}

```

The problem is that as far as I can tell, terms aggregations do not support pagination. If the index I'm running this query against contains 5,000+ unique user\_ids, then `size` must be set to some arbitrary value (5,000). The outcome is a slow query that returns 100s of KBs of data.

Composite aggregations are obviously the way to go for paginating aggregation responses, but I can't seem to get the sorting to work for my use case.

```auto
"users": {
    "composite": {
        "size": 200,
        "sources": [
            { "user_ids": { "terms": { "field": "user_id" } } }
        ]

```

The first composite aggregation I tried only supports sorting the results by the values of sources (i.e. the user\_id values).

```auto
"users": {
    "composite": {
        "size": 200,
        "sources": [
            { "time": { "terms": { "field": "timestamp", "order": "desc" } } },
            { "user_ids": { "terms": { "field": "user_id" } } }
        ]

```

I've also attempted to include the `timestamp` in `sources`, but I don't think this is appropriate either. This does produce a sorted list of results in each query, but my understanding is that due to the nature of composite aggregations not looking at every document, the results are simply sorted locally (i.e. the results are sorted, but that doesn't mean the first bucket I receive for the first page actually contains the most recent timestamp in the entire index). Additionally, I will receive "duplicate" user\_id results. Each unique timestamp + user\_id pair will appear in my composite buckets response, meaning I'd need to keep track of previously displayed user\_ids in my application.

Is there any way to sort my aggregation results by `timestamp` and support pagination without maintaining some state in my application? I want to avoid querying every single `user_id` + their max `timestamp` and sorting in the application.

Any advice would be greatly appreciated.

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood1/32/101255_2.png) [@Mark\_Harwood1](https://discuss.elastic.co/u/Mark_Harwood1)\
**Post date:** [July 26, 2023, 3:15pm UTC](https://discuss.elastic.co/t/pagination-sorted-aggregations-efficiently-retrieve-sorted-list-of-values/339325/2 "2023-07-26T15:15:05Z")

</div>

Reasoning about “last known state” of entities is a classic scenario for an entity-centric index maintained using the “transforms” api.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 23, 2023, 3:15pm UTC](https://discuss.elastic.co/t/pagination-sorted-aggregations-efficiently-retrieve-sorted-list-of-values/339325/3 "2023-08-23T15:15:42Z")

</div>

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