# Aggregate over top hits ElasticSearch

**URL:** <https://discuss.elastic.co/t/aggregate-over-top-hits-elasticsearch/182371>\
**Category:** Elasticsearch\
**Created:** [May 23, 2019, 7:51am UTC](https://discuss.elastic.co/t/aggregate-over-top-hits-elasticsearch/182371 "2019-05-23T07:51:05Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![haych1702](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/haych1702/32/37427_2.png) [@haych1702](https://discuss.elastic.co/u/haych1702)\
**Post date:** [May 23, 2019, 7:51am UTC](https://discuss.elastic.co/t/aggregate-over-top-hits-elasticsearch/182371/1 "2019-05-23T07:51:06Z")

</div>

My documents are structured in the following way:

```auto
{
   "chefInfo": {
      "id": int,
      "employed": String
      ... Some more recipe information ...
   }
   "recipe": {
      ... Some recipe information ...
   }
}

```

If a chef has multiple recipes, the nested `chefInfo` block will be identical in each document. My problem is that I want to do an aggregation of a field in the `chefInfo` part of the document. However, this doesn't take into account for the fact that the `chefInfo` block is a duplicated over multiple documents.

So, if the chef with the id of 1 is on 5 recipes and I am aggregating on the `employed` field then this particular chef, will represent 5 of the counts in the aggregation, whereas, I want them to only count a single one.

I thought about doing a `top_hits` aggregation on the chef\_id and then I wanted to do a sub-aggregation over all of the buckets but I can't work out how to do the counts over the results of all the buckets.

Is it possible what I want to do?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [May 23, 2019, 11:18am UTC](https://discuss.elastic.co/t/aggregate-over-top-hits-elasticsearch/182371/2 "2019-05-23T11:18:32Z")

</div>

If you have a single shard you can use the [diversified\_sampler](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-diversified-sampler-aggregation.html) aggregation.

Diversify on the `chefInfo.id` field and set `max_docs_per_value` to 1. Any nested aggregations positioned underneath that will consider at most one doc per chef (per shard).  
You may need a large `size` setting to consider all chefs.

---

<div class="post-metadata">

**Author:** ![haych1702](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/haych1702/32/37427_2.png) [@haych1702](https://discuss.elastic.co/u/haych1702)\
**Post date:** [May 23, 2019, 11:58am UTC](https://discuss.elastic.co/t/aggregate-over-top-hits-elasticsearch/182371/3 "2019-05-23T11:58:13Z")

</div>

We use multiple shards.

I have had some feedback that a cardinality aggregation might do the job. What do you think about using them?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [May 23, 2019, 12:03pm UTC](https://discuss.elastic.co/t/aggregate-over-top-hits-elasticsearch/182371/4 "2019-05-23T12:03:35Z")

</div>

What are you trying to aggregate on the chefs?  
If it’s “total years in the biz” then summing values with the ‘sum’ agg will be wrong because of duplicates.  
If it”s counting the schools they trained in then ‘cardinality’ agg will be unaffected by duplicates.

---

<div class="post-metadata">

**Author:** ![haych1702](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/haych1702/32/37427_2.png) [@haych1702](https://discuss.elastic.co/u/haych1702)\
**Post date:** [May 23, 2019, 2:24pm UTC](https://discuss.elastic.co/t/aggregate-over-top-hits-elasticsearch/182371/5 "2019-05-23T14:24:35Z")

</div>

So if there were 3 recipes:

```auto
[
  {
    "chef": {
      "id": 1,
      "status": "Employed"
    },
    "recipe": {
      "name": "toast"
    }
  },
  {
    "chef": {
      "id": 1,
      "status": "Employed"
    },
    "recipe": {
      "name": "eggs"
    }
  }
  {
    "chef": {
      "id": 2,
      "status": "Unemployed"
    },
    "recipe": {
      "name": "sausages"
    }
  }
]

```

I want to be able to aggregate on status. I should get the following result:

```auto
{
   "employed": 1,
   "unemployed": 1
}

```

Because the first two recipes belong to the same chef and employed should only count once.

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [May 23, 2019, 2:27pm UTC](https://discuss.elastic.co/t/aggregate-over-top-hits-elasticsearch/182371/6 "2019-05-23T14:27:59Z")

</div>

OK. So that would be a `terms` agg on the `chef.status` field with a child `cardinality` agg on the `chef.id` field (assuming chefs don't change status after a particularly bad recipe)

---

<div class="post-metadata">

**Author:** ![haych1702](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/haych1702/32/37427_2.png) [@haych1702](https://discuss.elastic.co/u/haych1702)\
**Post date:** [May 24, 2019, 7:12am UTC](https://discuss.elastic.co/t/aggregate-over-top-hits-elasticsearch/182371/7 "2019-05-24T07:12:33Z")

</div>

That is great. Thank you. 😀

---

<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:** [June 21, 2019, 7:12am UTC](https://discuss.elastic.co/t/aggregate-over-top-hits-elasticsearch/182371/8 "2019-06-21T07:12:34Z")

</div>

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