# Aggregate + sort + paginate on nested documents

**URL:** <https://discuss.elastic.co/t/aggregate-sort-paginate-on-nested-documents/314715>\
**Category:** Elasticsearch\
**Created:** [September 19, 2022, 4:53pm UTC](https://discuss.elastic.co/t/aggregate-sort-paginate-on-nested-documents/314715 "2022-09-19T16:53:06Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![t0mer](https://avatars.discourse-cdn.com/v4/letter/t/fbc32d/32.png) [@t0mer](https://discuss.elastic.co/u/t0mer)\
**Post date:** [September 19, 2022, 4:53pm UTC](https://discuss.elastic.co/t/aggregate-sort-paginate-on-nested-documents/314715/1 "2022-09-19T16:53:06Z")

</div>

Hi,  
I'm managing a product index, with product sales and other KPIs under a nested field.  
Trying to sort based on nested aggregation, and paginate - with no success.  
Below is a simplified version of my mapping, for the sake of the example -

```auto
{
                "product_type":
                {
                    "type": "keyword"
                },
                "family":
                {
                    "type": "keyword"
                },
                "rootdomain":
                {
                    "type": "keyword"
                },
                "kpis":
                {
                    "type": "nested",
                    "properties":
                    {
                        "sales_1d":
                        {
                            "type": "float"
                        },
                        "timestamp":
                        {
                            "type": "date",
                            "format": "strict_date_optional_time_nanos"
                        },
                        "views_1d":
                        {
                            "type": "float"
                        }
                    }
                }
            }

```

My aggregation is similar to the one below-

```auto
{
  "aggs": {
    "group_by_family": {
      "aggs": {
        "nested_aggregation": {
          "aggs": {
            "range_filtered": {
              "aggs": {
                "sales_1d": {
                  "sum": {
                    "field": "kpis.sales_1d"
                  }
                },
                "views_1d": {
                  "sum": {
                    "field": "kpis.views_1d"
                  }
                },
                "reverse_nesting": {
                  "aggs": {
                    "docs": {
                      "top_hits": {
                        "size": 1,
                        "sort": [
                          {
                            "_id": {
                              "order": "asc"
                            }
                          }
                        ],
                        "_source": {
                          "includes": [
                            "_id",
                            "family",
                            "rootdomain",
                            "product_type"
                          ]
                        }
                      }
                    }
                  },
                  "reverse_nested": {}
                }
              },
              "filter": {
                "range": {
                  "kpis.timestamp": {
                    "format": "basic_date_time_no_millis",
                    "gte": "20220721T000000Z",
                    "lte": "20220918T235959Z"
                  }
                }
              }
            }
          },
          "nested": {
            "path": "kpis"
          }
        }
      },
      "terms": {
        "field": "family",
        "size": 10
      }
    }
  },
  "query": {
  //some query to filter by product-type and rootdomain  
},
  "size": 0
}

```

I'm aware that I can add an `order` clause to term aggregation to order the aggregated results.  
My target though is to paginate the aggregated results - meaning I want to retrieve and order  
1-10 best-selling products, and later retrieve 11-20 best-selling products and so on.  
I've tried using bucket sort under `range_filtered` but I'm getting an error -

`class org.elasticsearch.search.aggregations.bucket.filter.InternalFilter cannot be cast to class org.elasticsearch.search.aggregations.InternalMultiBucketAggregation`

I'm not sure how to proceed from here, is this possible? if not, is there any workaround?

Thanks.

---

<div class="post-metadata">

**Author:** ![t0mer](https://avatars.discourse-cdn.com/v4/letter/t/fbc32d/32.png) [@t0mer](https://discuss.elastic.co/u/t0mer)\
**Post date:** [October 11, 2022, 7:44am UTC](https://discuss.elastic.co/t/aggregate-sort-paginate-on-nested-documents/314715/2 "2022-10-11T07:44:19Z")

</div>

Any help?

---

<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:** [November 8, 2022, 7:44am UTC](https://discuss.elastic.co/t/aggregate-sort-paginate-on-nested-documents/314715/3 "2022-11-08T07:44:32Z")

</div>

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