# Aggregation on previous aggregation's result

**URL:** <https://discuss.elastic.co/t/aggregation-on-previous-aggregations-result/207414>\
**Category:** Elasticsearch\
**Created:** [November 11, 2019, 7:53pm UTC](https://discuss.elastic.co/t/aggregation-on-previous-aggregations-result/207414 "2019-11-11T19:53:06Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![hi\_there](https://avatars.discourse-cdn.com/v4/letter/h/db5fbb/32.png) [@hi\_there](https://discuss.elastic.co/u/hi_there)\
**Post date:** [November 11, 2019, 7:53pm UTC](https://discuss.elastic.co/t/aggregation-on-previous-aggregations-result/207414/1 "2019-11-11T19:53:06Z")

</div>

Hi first time poster, probably won't be last =D.

I think Elasticsearch is cool, but I am stuck in trying to translate SQL to DSL and I was wondering if I could get some help.

Nutshell is the following (using random schema, but feature request is similar).

Assuming a table has the following columns:  
table\_i\_am

- name
- age
- country

With the data  
{ "name": "john\_doe", "age": 25, "country": "moon" }  
{ "name": "john\_doe", "age": 25, "country": "sun" }  
{ "name": "foo\_bar", "age": 18, "country": "moon" }  
{ "name": "foo\_bar", "age": 28, "country": "sun" }

With the SQL -\>

> select temp.name, count(1) as counter from (select name, age from table\_i\_am where name in ( 'john\_doe', 'foo\_bar',...) group by name, age) as temp group by temp.name limit 10

so the derived table in from is to group by name, age for duplicate of name, age, country into name, age and then outer group by with select is to count on the name.

Now I tried out POST /\_sql/translate for above query, but whereas the derived table was giving what I desired as a composite agg; the complete query wasn't. I figured it might be due to [composite agg is not currently compatible with pipeline aggregations](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html#_pipeline_aggregations), so I tried to get the derived table DSL and fiddle on my own using scripting agg.

However I hit an issue [bucket script aggregation only working on numeric](https://github.com/elastic/elasticsearch/issues/36642) and at this point I wasn't sure if I was doing something wrong or if it isn't supported so decided to reach out to the forum to get pointers.

```auto
"aggregations": {
  "by_scripting_group_by": {
    "terms": {
      "script": "doc['name'].value + '_' + doc['age'].value"
    }
  }
}

"buckets" : [
{
  "key" : "john_doe_25",
  "doc_count" : 2
},
{
  "key" : "foo_bar_18",
  "doc_count" : 1
},
{
  "key" : "foo_bar_28",
  "doc_count" : 1
}
]
buckets_path must reference either a number value or a single value numeric metric aggregation, got: [StringTerms] at aggregation [by_scripting_group_by]

```

Thanks a bunch in advance!

---

<div class="post-metadata">

**Author:** ![hi\_there](https://avatars.discourse-cdn.com/v4/letter/h/db5fbb/32.png) [@hi\_there](https://discuss.elastic.co/u/hi_there)\
**Post date:** [November 20, 2019, 10:34pm UTC](https://discuss.elastic.co/t/aggregation-on-previous-aggregations-result/207414/2 "2019-11-20T22:34:56Z")

</div>

Can I get some pointers?

---

<div class="post-metadata">

**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\
**Post date:** [November 22, 2019, 7:00pm UTC](https://discuss.elastic.co/t/aggregation-on-previous-aggregations-result/207414/3 "2019-11-22T19:00:19Z")

</div>

Most folks on this forum will be more familiar with the Elasticsearch query DSL than with SQL. I for one don't quite understand that SQL statement. Maybe you can describe what the desired output would look like? That may give someone an idea on how to implement that with the DSL.

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [November 25, 2019, 1:57pm UTC](https://discuss.elastic.co/t/aggregation-on-previous-aggregations-result/207414/4 "2019-11-25T13:57:33Z")

</div>

With ES SQL, subqueries are not quite supported. Simple things might work, but more complex subqueries (like yours) will probably not.

---

<div class="post-metadata">

**Author:** ![hi\_there](https://avatars.discourse-cdn.com/v4/letter/h/db5fbb/32.png) [@hi\_there](https://discuss.elastic.co/u/hi_there)\
**Post date:** [November 25, 2019, 3:53pm UTC](https://discuss.elastic.co/t/aggregation-on-previous-aggregations-result/207414/5 "2019-11-25T15:53:43Z")

</div>

Yeah my bad, I really wasn't clear in above example + **motive** of this post xD. In nutshell I just wanted to get some feedback from the **gurus** of the area, since tbh I haven't done such complicated aggregation. Previously did simple one level aggregation, since IMO ES shouldn't be relied upon for complicated levels of aggregation (pls correct me here if wrong o\_o).

Anywho, without further ado I tried to translate above SQL to DSL

Assume data

> {  
> "name\_field": "john\_doe",  
> "interesting\_field": "foo\_bar\_forever",  
> "misc\_field": "a"  
> },  
> {  
> "name\_field": "john\_doe",  
> "interesting\_field": "foo\_bar\_forever",  
> "misc\_field": "b"  
> },  
> {  
> "name\_field": "john\_doe",  
> "interesting\_field": "hello\_world",  
> "misc\_field": "c"  
> },  
> {  
> "name\_field": "jane\_doe",  
> "interesting\_field": "random",  
> "misc\_field": "d"  
> }

Composite aggregation

> "aggregations" : {  
> "groupby" : {  
> "composite" : {  
> "size" : 1000,  
> "sources" : [  
> {  
> "first\_group" : {  
> "terms" : {  
> "field" : "name\_field",  
> "missing\_bucket" : true,  
> "order" : "asc"  
> }  
> }  
> },  
> {  
> "second\_group" : {  
> "terms" : {  
> "field" : "interesting\_field",  
> "missing\_bucket" : true,  
> "order" : "asc"  
> }  
> }  
> }  
> ]  
> }  
> }  
> }

Results in -\>

> {  
> "key" : {  
> "name\_field" : "john\_doe",  
> "interesting\_field" : "foo\_bar\_forever"  
> },  
> "doc\_count" : 2  
> },  
> {  
> "key" : {  
> "name\_field" : "john\_doe",  
> "interesting\_field" : "hello\_world"  
> },  
> "doc\_count" : 1  
> },  
> {  
> "key" : {  
> "name\_field" : "jane\_doe",  
> "interesting\_field" : "random"  
> },  
> "doc\_count" : 1  
> }

So first would performs composite aggregation on those 2 fields. Then a follow up aggregation of count on the "name\_field" was initially being tinkered with (meaning desired below)

name\_field interesting\_field\_count  
john\_doe 2 (i.e. foo\_bar\_forever and hello\_world)  
jane\_doe 1 (i.e. random)

> {  
> "key" : {  
> "name\_field" : "john\_doe",  
> "interesting\_field\_count" : 2  
> }  
> },  
> {  
> "key" : {  
> "name\_field" : "jane\_doe",  
> "interesting\_field\_count" : 1  
> }  
> }

I was fiddling in trying to get the 2nd aggregation but found composite agg is not currently compatible with pipeline aggregations[https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html#\_pipeline\_aggregations](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html#_pipeline_aggregations). Then tried fiddling with scripting and use pipeline aggregation on it but found that bucket script aggregation only working on numeric[https://github.com/elastic/elasticsearch/issues/36642](https://github.com/elastic/elasticsearch/issues/36642).

Verified that BucketHelpers.java returns a double[https://github.com/elastic/elasticsearch/blob/40bcee72be76afed8041dc08f63f51214a1e6d0f/server/src/main/java/org/elasticsearch/search/aggregations/pipeline/BucketHelpers.java#L157](https://github.com/elastic/elasticsearch/blob/40bcee72be76afed8041dc08f63f51214a1e6d0f/server/src/main/java/org/elasticsearch/search/aggregations/pipeline/BucketHelpers.java#L157).

I wanted to know if above statements of non-support is valid. I mean yeah we can do count on the application code side or change in data model, but wanted 2nd opinion in case I am missing something.

Thanks, this community is awesome (not saying it just to get a better response O\_O)!

---

<div class="post-metadata">

**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\
**Post date:** [November 27, 2019, 3:32pm UTC](https://discuss.elastic.co/t/aggregation-on-previous-aggregations-result/207414/6 "2019-11-27T15:32:58Z")

</div>

If I understand you correctly, you are looking to get to that last code snippet? You do not need a pipeline aggregation for that. To get the unique value count of a field, you can use the [cardinality aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-cardinality-aggregation.html). Something like this would work:

```auto
{
  "size": 0,
  "aggregations": {
    "groupby": {
      "composite": {
        "size": 1000,
        "sources": [
          {
            "names": {
              "terms": {
                "field": "name_field"
              }
            }
          }
        ]
      },
      "aggs": {
        "interesting_field_count": {
          "cardinality": {
            "field": "interesting_field"
          }
        }
      }
    }
  }
}

```

It would return:

```auto
  "aggregations" : {
    "groupby" : {
      "after_key" : {
        "names" : "john_doe"
      },
      "buckets" : [
        {
          "key" : {
            "names" : "jane_doe"
          },
          "doc_count" : 1,
          "interesting_field_count" : {
            "value" : 1
          }
        },
        {
          "key" : {
            "names" : "john_doe"
          },
          "doc_count" : 3,
          "interesting_field_count" : {
            "value" : 2
          }
        }
      ]
    }
  }

```

---

<div class="post-metadata">

**Author:** ![hi\_there](https://avatars.discourse-cdn.com/v4/letter/h/db5fbb/32.png) [@hi\_there](https://discuss.elastic.co/u/hi_there)\
**Post date:** [November 27, 2019, 3:54pm UTC](https://discuss.elastic.co/t/aggregation-on-previous-aggregations-result/207414/7 "2019-11-27T15:54:45Z")

</div>

Wow I feel dumb, thanks for the above answer Abdon! I tried to translate SQL -\> DSL directly and that was my mistake. Should have just started from DSL upwards. This can be resolved/answered, thanks again!

---

<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:** [December 25, 2019, 3:54pm UTC](https://discuss.elastic.co/t/aggregation-on-previous-aggregations-result/207414/8 "2019-12-25T15:54:45Z")

</div>

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