Get sum value from distinct aggregation query

Elastic Stack Elasticsearch
1

Actually, i am trying to implement the following MySQL query in elasticsearch.

SELECT SUM(FIELD_1) FROM (SELECT FIELD_1 FROM TABLE GROUP BY FIELD_2) AS UNIQUE_TABLE

I tried the following query in elasticsearch

GET index/_search
{
  "size": 0,
  "query": {}, 
  "aggs": {
    "unique_twitter": {
      "terms": {
        "size": 1000, 
        "field": "field_2"
      },
      "aggs": {
        "max": {
          "max": {
            "field": "field_1"
          }
        }
      }
    },
    "count": {
      "sum_bucket": {
        "buckets_path": "unique_twitter>max"
      }
    }
  }
}

The problem with the following query, I have to give the size of the first aggregation to make it work. I want it to work without specifying the size of the aggregation.
Thanks in advance !!

2

That's the only way I can think to do it. You can either set a very large value for the size of the terms agg (to guarantee you catch all values), or run a cardinality aggregation first to get the size of field_2 and then use that.

1 Like
3

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