# How to aggregate by doc\_count?

**URL:** <https://discuss.elastic.co/t/how-to-aggregate-by-doc-count/212106>\
**Category:** Elasticsearch\
**Created:** [December 17, 2019, 7:25am UTC](https://discuss.elastic.co/t/how-to-aggregate-by-doc-count/212106 "2019-12-17T07:25:12Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sergio\_Blanco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sergio_blanco/32/44739_2.png) [@Sergio\_Blanco](https://discuss.elastic.co/u/Sergio_Blanco)\
**Post date:** [December 17, 2019, 7:25am UTC](https://discuss.elastic.co/t/how-to-aggregate-by-doc-count/212106/1 "2019-12-17T07:25:12Z")

</div>

I want to aggregate the result of other aggregation using Elasticsearch. I have created the first aggregation I need:

```auto
    "size":0,
    "query": {
        "bool": {
          "filter": {
              "match" : {"type": "Posts"}
          },
          "filter": {
              "match" : {"PostTypeId": "1"}
          }
        }
    },
    "aggs" : {
        "by_user": {
          "terms": {
            "field": "OwnerUserId"
          }
        }
    }

```

This query takes all the documents of type _post_ that are questions ( _PostTypeId = 1_ ). Then, it aggregates by _OwnerUserId_ , which counts the number of question posts of each user, giving the following result:

```auto
{'took': 0,
 'timed_out': False,
 '_shards': {'total': 1, 'successful': 1, 'skipped': 0, 'failed': 0},
 'hits': {'total': {'value': 10000, 'relation': 'gte'},
  'max_score': None,
  'hits': []},
 'aggregations': {'by_user': {'doc_count_error_upper_bound': 0,
   'sum_other_doc_count': 31053,
   'buckets': [{'key': '2230', 'doc_count': 223},
    {'key': '', 'doc_count': 177},
    {'key': '38304', 'doc_count': 158},
    {'key': '5997', 'doc_count': 144},
    {'key': '4048', 'doc_count': 130},
    {'key': '25813', 'doc_count': 119},
    {'key': '27826', 'doc_count': 119},
    {'key': '2633', 'doc_count': 115},
    {'key': '19919', 'doc_count': 114},
    {'key': '13938', 'doc_count': 111}]}}}

```

Now I want to do another aggregation over the results of the previous one: aggregate by _doc\_count_ , I mean grouping and counting the equal number of question posts. For the previous result, my desired result would be:

```auto
{'buckets': [{'key': '223', 'doc_count': 1},
    {'key': '177', 'doc_count': 1},
    {'key': '158', 'doc_count': 1},
    {'key': '144', 'doc_count': 1},
    {'key': '130', 'doc_count': 1},
    {'key': '119', 'doc_count': 2},
    {'key': '115', 'doc_count': 1},
    {'key': '114', 'doc_count': 1},
    {'key': '111', 'doc_count': 1}]}

```

---

<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:** [December 17, 2019, 9:30am UTC](https://discuss.elastic.co/t/how-to-aggregate-by-doc-count/212106/2 "2019-12-17T09:30:25Z")

</div>

Hi Sergio,  
What you're asking to do is very hard in a distributed index because it relies on first joining up a lot of remote data for each OwnerUserId. If you have many unique IDs then physical constraints like network speeds and the amount of memory that can be used in a single request mean this is too much computation to attempt in a single query.  
The alternative is to physically organise the data so that related items are closer to hand. Using the new [transforms](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html) api you can convert your event-centric data (users posting things) to an entity-centric index (summaries of each user's behaviour). Using this index makes analysis of user behaviours faster and simpler (and in some cases simply "possible")

---

<div class="post-metadata">

**Author:** ![Sergio\_Blanco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sergio_blanco/32/44739_2.png) [@Sergio\_Blanco](https://discuss.elastic.co/u/Sergio_Blanco)\
**Post date:** [December 17, 2019, 9:59am UTC](https://discuss.elastic.co/t/how-to-aggregate-by-doc-count/212106/3 "2019-12-17T09:59:03Z")

</div>

But I do not mind about efficiency as I am doing some research. I only want to know if there exists any syntax that allows me to aggregate by doc\_count even if it is not efficient. @Mark_Harwood

---

<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:** [December 17, 2019, 10:03am UTC](https://discuss.elastic.co/t/how-to-aggregate-by-doc-count/212106/4 "2019-12-17T10:03:35Z")

</div>

The transforms API is about efficiency but it makes querying simpler too. It uses the aggregations framework to make the summary docs so does give you that "aggregations on an aggregation" solution.  
Stage 1 is to aggregate the total posts for each user with the transform API.  
Stage 2 is to do a `histogram` aggregation on the "total\_posts" field in the users index you created in step 1.

---

<div class="post-metadata">

**Author:** ![Sergio\_Blanco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sergio_blanco/32/44739_2.png) [@Sergio\_Blanco](https://discuss.elastic.co/u/Sergio_Blanco)\
**Post date:** [December 17, 2019, 12:00pm UTC](https://discuss.elastic.co/t/how-to-aggregate-by-doc-count/212106/5 "2019-12-17T12:00:53Z")

</div>

Can you show me how this query would be? I cannot find many examples of the transforms API and I cannot figure the query out. @Mark_Harwood

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [December 17, 2019, 3:18pm UTC](https://discuss.elastic.co/t/how-to-aggregate-by-doc-count/212106/6 "2019-12-17T15:18:05Z")

</div>

Hi Sergio

Examples can be found [here](https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-examples.html).

For your use case, something like this should work:

```auto
PUT _transform/transform_1
{
  "source": {
    "index": "YOUR_SOURCE_INDEX",
    "query": {YOUR_FILTER_QUERY}
  },
  "dest": {
    "index": "YOUR_DEST_INDEX"
  },
  "pivot": {
    "group_by": {
      "OwnerUserId": {
        "terms": {
          "field": "OwnerUserId"
        }
      }
    },
    "aggregations": {
      "count": {
        "value_count": {
          "field": "OwnerUserId"
        }
      }
    }
  }
}

```

You probably want to make this a [continuous transform](https://www.elastic.co/guide/en/elasticsearch/reference/7.5/put-transform.html) in order to update the destination index automatically for new data. Checkout the `sync` parameter. The mentioned examples also contain some continuous transforms, another good resource can be found [here](https://www.elastic.co/guide/en/elasticsearch/reference/7.5/ecommerce-transforms.html).

As @Mark_Harwood pointed out already, the created `YOUR_DEST_INDEX` can be queried for further analysis and should solve your usecase.

---

<div class="post-metadata">

**Author:** ![Sergio\_Blanco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sergio_blanco/32/44739_2.png) [@Sergio\_Blanco](https://discuss.elastic.co/u/Sergio_Blanco)\
**Post date:** [December 17, 2019, 4:10pm UTC](https://discuss.elastic.co/t/how-to-aggregate-by-doc-count/212106/7 "2019-12-17T16:10:31Z")

</div>

Thank you, that solved my usecase.

However, is it true that there is no syntax available to directly access the _doc\_count_ field returned by the aggregation I showed in the query of the first post comment, using a second nested _terms_ aggregation? @Mark_Harwood @Hendrik_Muhs

---

<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:** [December 17, 2019, 4:13pm UTC](https://discuss.elastic.co/t/how-to-aggregate-by-doc-count/212106/8 "2019-12-17T16:13:48Z")

</div>

Generally speaking - if something isn't scalable, we don't offer it and instead build something like the transforms API to make it scalable

---

<div class="post-metadata">

**Author:** ![Sergio\_Blanco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sergio_blanco/32/44739_2.png) [@Sergio\_Blanco](https://discuss.elastic.co/u/Sergio_Blanco)\
**Post date:** [December 17, 2019, 4:15pm UTC](https://discuss.elastic.co/t/how-to-aggregate-by-doc-count/212106/9 "2019-12-17T16:15:22Z")

</div>

I understand.

Thank you so much for your time and extremely detailed answers !!

---

<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 14, 2020, 4:15pm UTC](https://discuss.elastic.co/t/how-to-aggregate-by-doc-count/212106/10 "2020-01-14T16:15:30Z")

</div>

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