# Aggregation over an aggregation

**URL:** <https://discuss.elastic.co/t/aggregation-over-an-aggregation/123784>\
**Category:** Elasticsearch\
**Created:** [March 13, 2018, 5:08pm UTC](https://discuss.elastic.co/t/aggregation-over-an-aggregation/123784 "2018-03-13T17:08:20Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jgrenat](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jgrenat/32/28742_2.png) [@jgrenat](https://discuss.elastic.co/u/jgrenat)\
**Post date:** [March 13, 2018, 5:08pm UTC](https://discuss.elastic.co/t/aggregation-over-an-aggregation/123784/1 "2018-03-13T17:08:20Z")

</div>

Hello,

I have some entries with the following form:

```
{
  "categoryId": "categoryName",
  "elementId": "elementId",
  "state": "STATE"
}

```

The state can either be "INIT", "RUN", "DONE", ''ERROR" (in this order for the first three)

I'd like to know for each category at which state is each element. For exemple, for the following (categoryId, elementId, state) tuples:

```
("category1", "element1", "INIT")
("category1", "element1", "RUN")
("category1", "element2", "INIT")
("category2", "element1", "INIT")
("category2", "element2", "RUN")

```

I'd like to get the following (categoryId, state, count) tuples:

```
("category1", "INIT", 1)
("category1", "RUN", 1)
("category2", "RUN", 1)

```

For now, I found how to get the last state for each of my element (for exemple, an element inside a category that have two entries INIT and RUN need to be recognized as RUN only), but I don't know how to aggregate these results afterwards. This is my request:

```
{
  "query": {
    "bool": {
      "should": [
        {
          "term": {
            "state": {"value": "INIT", "boost": 10}
          }
        },
        {
          "term": {
            "state": {"value": "RUN", "boost": 20}
          }
        },
        {
          "term": {
            "state": {"value": "DONE", "boost": 30}
          }
        },
        {
          "term": {
            "state": {"value": "ERROR", "boost": 40}
          }
        }
      ]
    }
  },
  "size": 0,
  "aggs": {
    "states": {
      "terms": {"field": "categoryId"},
      "aggs": {
        "elements": {
          "terms": {"field": "elementId"},
          "aggs": {
            "state": {
              "top_hits": {"size": 1}
            }
          }
        }
      }
    }
  }
}

```

I've tried to play with a pipeline aggregation, but I'm not sure how to do that. Is there any solution to aggregate these states now to have a category-wide number of elements by status?

I'm not sure if my problem is understandable, don't hesitate to ask me more if that's the case!

Thanks,

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [March 14, 2018, 10:19am UTC](https://discuss.elastic.co/t/aggregation-over-an-aggregation/123784/2 "2018-03-14T10:19:36Z")

</div>

Elasticsearch doesn't support aggregations that require the handling of a document to depend on the value on another document in general. So I'm afraid there is no way to do it in Elasticsearch directly. You might need to do some client-side logic to first figure out the current state for each category, and then another request to aggregate documents for each of these (category,state) pairs.

---

<div class="post-metadata">

**Author:** ![jgrenat](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jgrenat/32/28742_2.png) [@jgrenat](https://discuss.elastic.co/u/jgrenat)\
**Post date:** [March 14, 2018, 10:35am UTC](https://discuss.elastic.co/t/aggregation-over-an-aggregation/123784/3 "2018-03-14T10:35:41Z")

</div>

This is what I feared, but this doesn't seem possible on the client side either because of the amount of entries involved :-/

---

<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:** [April 11, 2018, 10:35am UTC](https://discuss.elastic.co/t/aggregation-over-an-aggregation/123784/4 "2018-04-11T10:35:50Z")

</div>

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