Elasticsearch group by, having and pagenation

I have a SQL that would like to query the same result set in ES. The result set would need to pagination. So I've tried Composite aggregation, it works fine for pagination and order. But I have no idea how to filter with the Having Statement .

SELECT student_id,COUNT(paid_amount) paid_total 
  FROM paid_records 
  GROUP BY student_id 
    HAVING paid_total > 100 
  ORDER BY paid_total desc OFFSET 20 LIMIT 10;

Aggs Approach, this approach can't pagination:

{
  "size": 0,
  "aggs": {
    "groupby_student": {
      "terms": {
        "field": "student_id"
      },
      "aggs": {
        "paid_price_sum": {
          "sum": {
            "field": "total_paid_price"
          }
        },
        "paid_price_sum_filter": {
          "bucket_selector": {
            "buckets_path": {
              "priceSum": "paid_price_sum"
            },
            "script": "params.priceSum >= 100"
          }
        },
        "paid_price_sort": {
          "bucket_sort": {
            "sort": {
              "paid_price_sum": "desc"
            },
            "size": 10
          }
        }
      }
    }
  }
}

Composite Agg approach, the result set is not correct, with the paid_price_sum_filter it just filters out the current page.

{
  "size": 0,
  "aggs": {
    "groupBy": {
      "composite": {
        "size": 10,
        "after": {"paid_count": "5c8a1e4eede4790001b1d74b"},
        "sources": [
          {
            "paid_count": {
              "terms": {
                "field": "student_id"
              }
            }
          }
        ]
      },
      "aggs": {
        "paid_price_sum": {
          "sum": {
            "field": "total_paid_price"
          }
        },
        "paid_price_sum_filter": {
          "bucket_selector": {
            "buckets_path": {
              "paid_price_sum": "paid_price_sum"
            },
            "script": "params.paid_price_sum > 100"
          }
        }
      }
    }
  }
}

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