# Metric aggregation on terms aggregation result set

**URL:** <https://discuss.elastic.co/t/metric-aggregation-on-terms-aggregation-result-set/212414>\
**Category:** Elasticsearch\
**Created:** [December 18, 2019, 10:25pm UTC](https://discuss.elastic.co/t/metric-aggregation-on-terms-aggregation-result-set/212414 "2019-12-18T22:25:01Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![alexfritz](https://avatars.discourse-cdn.com/v4/letter/a/ee7513/32.png) [@alexfritz](https://discuss.elastic.co/u/alexfritz)\
**Post date:** [December 18, 2019, 10:25pm UTC](https://discuss.elastic.co/t/metric-aggregation-on-terms-aggregation-result-set/212414/1 "2019-12-18T22:25:01Z")

</div>

Hello

maybe I don't understand Elasticsearch queries, but I have a problem.  
I have time series documents to catch events (@timestamp = event timestamp). These events contain fields like: startDate, endDate and duration. Some of these documents can have duplicates which I want to filter.

One document looks like this:  
`"_id": "mEF1Gm8BFjRRFbh91iLc", "_source": { "@timestamp": 1576676119000, "startDate": "2019-12-18T13:20:00Z", "name": "test1", "duration": 919, "endDate": "2019-12-18T13:35:19Z" },`

When I use terms aggregation on the startDate field, I will get all distinct values for this field. How can I get the result set with several fields for the whole distinct documents?

My goal is to get all distinct documents and perform metric aggregations on the result set, like sum up all duration fields.  
Is this even possible?

---

<div class="post-metadata">

**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\
**Post date:** [December 26, 2019, 12:57pm UTC](https://discuss.elastic.co/t/metric-aggregation-on-terms-aggregation-result-set/212414/2 "2019-12-26T12:57:58Z")

</div>

Sounds like a [Multi-field terms aggregation](https://github.com/elastic/elasticsearch/edit/7.5/docs/reference/aggregations/bucket/terms-aggregation.asciidoc) — either use a script (slower) or `copy_to` (needs to be set up with the mapping, won't backfill existing documents).

Within that aggregation you can then run another one to calculate the next step.

---

<div class="post-metadata">

**Author:** ![alexfritz](https://avatars.discourse-cdn.com/v4/letter/a/ee7513/32.png) [@alexfritz](https://discuss.elastic.co/u/alexfritz)\
**Post date:** [December 28, 2019, 2:03pm UTC](https://discuss.elastic.co/t/metric-aggregation-on-terms-aggregation-result-set/212414/3 "2019-12-28T14:03:16Z")

</div>

No, I don't think it's multi-field terms aggregation. I am looking for a solution to get all document fields based on a terms aggregation.  
If I am using copy\_to, I won't get the original fields in the result set to apply the aggregation on, right?

In SQL it would be something like:  
SELECT DISTINCT startDate, duration, name, endDate FROM table;

---

<div class="post-metadata">

**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\
**Post date:** [December 28, 2019, 2:30pm UTC](https://discuss.elastic.co/t/metric-aggregation-on-terms-aggregation-result-set/212414/4 "2019-12-28T14:30:41Z")

</div>

If you want the actual distinct documents, you set the top level `size` to whatever is the limit of documents you want to get back at most. Be sure to set it on the top level of the JSON document and not inside the terms aggregation because that has [a different purpose](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-size).

```
GET /_search
{
   "size": 1000, // Here
    "aggs" : {
        "products" : {
            "terms" : {
                "field" : "product",
                "size" : 1200 // This is the calculation per shard, not the returned docs
            }
        }
    }
}
```

---

<div class="post-metadata">

**Author:** ![alexfritz](https://avatars.discourse-cdn.com/v4/letter/a/ee7513/32.png) [@alexfritz](https://discuss.elastic.co/u/alexfritz)\
**Post date:** [December 29, 2019, 4:22pm UTC](https://discuss.elastic.co/t/metric-aggregation-on-terms-aggregation-result-set/212414/5 "2019-12-29T16:22:37Z")

</div>

This is not working. The result will be all documents (not the distinct). I think I might have found a solution by using a top\_hits aggregation. Is there a way to do an aggregation on a top\_hits result set?

```
{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "2019-12-18T10:00:00.000+01:00",
        "lt": "2019-12-18T20:00:00.000+01:00",
        "time_zone": "+01:00"
      }
    }
  },
  "aggs": {
    "buckets": {
      "terms": {
        "field": "startDate"
      },
      "aggs": {
        "chartdata": {
          "top_hits": {
            "_source": {
              "includes": [
                "startDate",
                "name",
                "duration",
                "endDate"
              ]
            },
            "size": 1
          }
        }
      }
    }
  }
}
```

---

<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:** [January 26, 2020, 4:22pm UTC](https://discuss.elastic.co/t/metric-aggregation-on-terms-aggregation-result-set/212414/6 "2020-01-26T16:22:39Z")

</div>

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