# Mean aggregation over cadinality aggregation, over previous composite aggregation

**URL:** <https://discuss.elastic.co/t/mean-aggregation-over-cadinality-aggregation-over-previous-composite-aggregation/294374>\
**Category:** Elasticsearch\
**Created:** [January 14, 2022, 7:31am UTC](https://discuss.elastic.co/t/mean-aggregation-over-cadinality-aggregation-over-previous-composite-aggregation/294374 "2022-01-14T07:31:43Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Silver137](https://avatars.discourse-cdn.com/v4/letter/s/839c29/32.png) [@Silver137](https://discuss.elastic.co/u/Silver137)\
**Post date:** [January 14, 2022, 7:31am UTC](https://discuss.elastic.co/t/mean-aggregation-over-cadinality-aggregation-over-previous-composite-aggregation/294374/1 "2022-01-14T07:31:43Z")

</div>

My use case require to obtain an average over previous cardinality aggregation, this aggregations works over the buckets generated by a composite aggregation "calendar histogram" that breaks the timeline in calendar days, due to I'm working over a long time span.

I don't know whats fits best to solve this case using Elasticsearch capabilities, sub-aggregation, pipeline-aggregation ... , the cardinality column is generated over the calendar buckets using sub-aggregation, I would like work over this output to obtanin an average.

```auto
aggregations: {
    "daily_product_names_for_specific_shop": {
        "composite": {
            "size": 10000,
            "sources": [
              {
                "date": {
                  "date_histogram": {
                    "field": "@timestamp",
                    "calendar_interval": "1d",
                    "format": "iso8601"
                  }
                }
              }
            ]
        },
        # Sub-aggregation over composite buckets
        "aggregations": {
           "different_products_amount": {
               "cardinality": {
                    "field": "ticket.p_name"
                }
           }
        # Maybe some stuff
        }
    }
}

```

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [January 14, 2022, 9:49am UTC](https://discuss.elastic.co/t/mean-aggregation-over-cadinality-aggregation-over-previous-composite-aggregation/294374/2 "2022-01-14T09:49:25Z")

</div>

In my opinion, average bucket aggregation of pipeline aggregation fits your purpose. If it doesn't work, please ask again.

> **[Average bucket aggregation | Elasticsearch Guide \[7.16\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-avg-bucket-aggregation.html)**

---

<div class="post-metadata">

**Author:** ![Silver137](https://avatars.discourse-cdn.com/v4/letter/s/839c29/32.png) [@Silver137](https://discuss.elastic.co/u/Silver137)\
**Post date:** [January 17, 2022, 7:49am UTC](https://discuss.elastic.co/t/mean-aggregation-over-cadinality-aggregation-over-previous-composite-aggregation/294374/3 "2022-01-17T07:49:02Z")

</div>

I have tried:

```auto
aggregations: {
    "daily_product_names_for_specific_shop": {
        "composite": {
            "size": 10000,
            "sources": [
              {
                "date": {
                  "date_histogram": {
                    "field": "@timestamp",
                    "calendar_interval": "1d",
                    "format": "iso8601"
                  }
                }
              }
            ]
        },
        # Sub-aggregation over composite buckets
        "aggregations": {
           "different_products_amount": {
               "cardinality": {
                    "field": "ticket.p_name"
                }
           },
           # The avg
           "mean": {
                "avg_bucket": {
                    "buckets_path": "different_products_amount",
                    "gap_policy": "skip",
                    "format": "#,##0.00;(#,##0.00)"
              }
        }
    }
}

```

But gets:

```auto
Validation Failed: 1: The first aggregation in buckets_path must be a multi-bucket aggregation for aggregation [mean] found :org.elasticsearch.search.aggregations.metrics.CardinalityAggregationBuilder for buckets path: different_products_amount;

```

Also I have already tried:

```auto
aggregations: {
    "daily_product_names_for_specific_shop": {
        "composite": {
            "size": 10000,
            "sources": [
              {
                "date": {
                  "date_histogram": {
                    "field": "@timestamp",
                    "calendar_interval": "1d",
                    "format": "iso8601"
                  }
                }
              }
            ]
        },
        # Sub-aggregation over composite buckets
        "aggregations": {
           "different_products_amount": {
               "cardinality": {
                    "field": "ticket.p_name"
                }
           }
        }
    },
   "mean": {
       "avg_bucket": {
                    "buckets_path": "daily_product_names_for_specific_shop>different_products_amount",
                    "gap_policy": "skip",
                    "format": "#,##0.00;(#,##0.00)"
       }
}

```

But the composite acts:

```auto
Validation Failed: 1: The first aggregation in buckets_path must be a multi-bucket aggregation for aggregation [mean] found :org.elasticsearch.search.aggregations.bucket.composite.CompositeAggregationBuilder for buckets path: daily_product_names_for_specific_shop>different_products_amount;

```

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [January 17, 2022, 10:52am UTC](https://discuss.elastic.co/t/mean-aggregation-over-cadinality-aggregation-over-previous-composite-aggregation/294374/4 "2022-01-17T10:52:21Z")

</div>

The latter query looks correct grammarticaly. Sorry, I didn't know pipeline aggregation can not be used with composit aggregation.

I found an issue explaining the reason.

> <https://github.com/elastic/elasticsearch/issues/32692>
>
> \<!--
> 
> \*\* Please read the guidelines below. \*\*
> 
> Issues that do not follow the…se guidelines are likely to be closed.
> 
> 1. GitHub is reserved for bug reports and feature requests. The best place to
> ask a general question is at the Elastic \[forums\](https://discuss.elastic.co).
> GitHub is not the place for general questions.
> 
> 2. Is this bug report or feature request for a supported OS? If not, it
> is likely to be closed. See https://www.elastic.co/support/matrix#show\_os
> 
> 3. Please fill out EITHER the feature request block or the bug report block
> below, and delete the other block.
> 
> \--\>
> 
> 
> 
> \*\*Elasticsearch version\*\* (\`bin/elasticsearch --version\`): 6.3.0 official docker image
> 
> \*\*Plugins installed\*\*: \[\]
> 
> \*\*JVM version\*\* (\`java -version\`): 10.0.1
> 
> \*\*OS version\*\* (\`uname -a\` if on a Unix-like system):
> Linux 389f11186e5b 4.9.93-linuxkit-aufs #1 SMP Wed Jun 6 16:55:56 UTC 2018 x86\_64 x86\_64 x86\_64 GNU/Linux
> 
> \*\*Description of the problem including expected versus actual behavior\*\*:
> Haven't seen the Git issue posted for this \[thread\](https://discuss.elastic.co/t/elasticsearch-6-2-composite-bucket-aggregation-with-sum-bucket-aggregation-and-pipeline-aggregations/142658), so I'm posting it to get the ball rolling since my team has encountered it as well.
> 
> Pipeline metrics aggregations do not recognize composite aggregations as multi-bucket. However, composite aggregations are multi-bucket, so this should work.
> 
> \*\*Steps to reproduce\*\*:
> 
> 1. Template mapping
> \`\`\`
> PUT \_template/template\_default
> {
> "mappings": {
> "\_doc": {
> "\_all": {
> "enabled": false
> },
> "dynamic": "strict",
> "properties": {
> "itemId": {
> "type": "keyword",
> "norms": false
> },
> "inputQty": {
> "type": "integer",
> "index": false
> },
> "orderQty": {
> "type": "integer",
> "index": false
> },
> "centerId": {
> "type": "keyword",
> "eager\_global\_ordinals": true,
> "norms": false
> },
> "submittedQty": {
> "type": "integer",
> "index": false
> },
> "confirmedQty": {
> "type": "integer",
> "index": false
> }
> }
> }
> }
> }
> \`\`\`
> 2. REST call
> \`\`\`
> POST items-0\*/\_search?ignore\_unavailable=true
> {
> "size": 0,
> "track\_total\_hits": false,
> "aggs" : {
> "myBuckets" : {
> "composite" : {
> "size" : 100000,
> "sources" : \[
> { "center\_name" : { "terms" : { "field" : "centerId"} } }
> \]
> },
> "aggs" : {
> "requested\_units" : { "sum": { "field" : "inputQty" } },
> "approved\_units" : { "sum": { "field" : "orderQty" } },
> "submitted\_quantity" : { "sum" : { "field" : "submittedQty"} },
> "confirmed\_quantity" : { "sum" : { "field" : "confirmedQty"} }
> }
> },
> "check\_pipeline\_agg": {
> "sum\_bucket": {
> "buckets\_path": "fc\_buckets\>requested\_units"
> }
> }
> }
> }
> \`\`\`
> 
> \*\*Provide logs (if relevant)\*\*:
> The error that comes back will be similar to:
> \`\`\`json
> {
> "error": {
> "root\_cause": \[
> {
> "type": "illegal\_argument\_exception",
> "reason": "The first aggregation in buckets\_path must be a multi-bucket aggregation for aggregation \[avg\_cardinality\] found :org.elasticsearch.search.aggregations.bucket.composite.CompositeAggregationBuilder for buckets path: composite\_buckets\>cardinality\_some\_field"
> }
> \],
> "type": "search\_phase\_execution\_exception",
> "reason": "all shards failed",
> "phase": "query",
> "grouped": true,
> "failed\_shards": \[
> {
> "shard": 0,
> "index": "my\_index",
> "node": "1\_8dwXRuT565uQg11iZ\_SA",
> "reason": {
> "type": "illegal\_argument\_exception",
> "reason": "The first aggregation in buckets\_path must be a multi-bucket aggregation for aggregation \[avg\_cardinality\] found :org.elasticsearch.search.aggregations.bucket.composite.CompositeAggregationBuilder for buckets path: composite\_buckets\>cardinality\_some\_field"
> }
> }
> \],
> "caused\_by": {
> "type": "illegal\_argument\_exception",
> "reason": "The first aggregation in buckets\_path must be a multi-bucket aggregation for aggregation \[avg\_cardinality\] found :org.elasticsearch.search.aggregations.bucket.composite.CompositeAggregationBuilder for buckets path: composite\_buckets\>cardinality\_some\_field",
> "caused\_by": {
> "type": "illegal\_argument\_exception",
> "reason": "The first aggregation in buckets\_path must be a multi-bucket aggregation for aggregation \[avg\_cardinality\] found :org.elasticsearch.search.aggregations.bucket.composite.CompositeAggregationBuilder for buckets path: composite\_buckets\>cardinality\_some\_field"
> }
> }
> },
> "status": 400
> }
> \`\`\`

Are there any destructive problems by using only datetime histogram aggregation without composite aggregation and discard unnecessary outputs?

---

<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:** [February 14, 2022, 10:52am UTC](https://discuss.elastic.co/t/mean-aggregation-over-cadinality-aggregation-over-previous-composite-aggregation/294374/5 "2022-02-14T10:52:24Z")

</div>

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