How to filter with pipeline aggregations in all aggregated results

Elastic Stack Elasticsearch
1

Hi! I'm working on a project with elasticseach. I'am stuck with the below problem. I can't figure this out no matter what I have tried so far.
Firstly, I would like to group by "keyword" in buckets using the terms aggregation.
After that, I would like to calculate some metrics such as "sum" of the field "clicks" using the sum_aggregation
After that, I would like to filter the returned results with pipeline aggregation based on min max values selected from the user.
At the end, I want to sort them by "clicks" with descending order. Also to return only first 100 results to the client.

What I have done so far is the below query.

{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "date": {
              "gte": "2021-10-01",
              "lte": "2021-10-31"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "group_by_terms": {
      "terms": {
        "field": "keyword.keyword"
      },
      "aggs": {
        "sum_clicks": {
          "sum": {
            "script": {
              "lang": "painless",
              "inline": "doc['clicks'].size()== 0 ? 0 : doc['clicks'].value"
            }
          }
        },
        "sum_clicks_bucket_sort": {
          "bucket_sort": {
            "sort": [
              {
                "sum_clicks": {
                  "order": "desc"
                }
              }
            ],
            "size": 100
          }
        },
        "bucket_filter": {
          "bucket_selector": {
            "buckets_path": {
              "sum_clicks": "sum_clicks"
            },
            "script": "params.sum_clicks < 14 && params.sum_clicks > 0"
          }
        }
      }
    }
  }
}

So, although there are many aggregated results with "sum_clicks" > 0 and "sum_clicks" < 14 they are not returned by the query.
If I increase the min max then it will rerurn results. Another strange scenario is that if I remove the ordering it will return results. Maybe terms aggregation doens't include all results of all documents in Elasticsearch, that's why they can't be returned. I'am not sure about it. Any help will be appreciated! Thanks in advance!

2

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