# Reaggregate on nested aggregation results using Elasticsearch

**URL:** <https://discuss.elastic.co/t/reaggregate-on-nested-aggregation-results-using-elasticsearch/174842>\
**Category:** Elasticsearch\
**Created:** [April 1, 2019, 6:32pm UTC](https://discuss.elastic.co/t/reaggregate-on-nested-aggregation-results-using-elasticsearch/174842 "2019-04-01T18:32:19Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![mdeverdelhan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mdeverdelhan/32/43315_2.png) [@mdeverdelhan](https://discuss.elastic.co/u/mdeverdelhan)\
**Post date:** [April 1, 2019, 6:32pm UTC](https://discuss.elastic.co/t/reaggregate-on-nested-aggregation-results-using-elasticsearch/174842/1 "2019-04-01T18:32:19Z")

</div>

I want to compute some aggregations (using Elasticsearch 6.2) on products that have criteria. All the criteria are flattened and I want to reuse some aggregation results to reaggregate by a specific criterion.

Here is my index:

```auto
PUT my_index
{
 "mappings" : {
  "_doc" : {
   "properties" : {
    "contract": {
     "properties": {
      "products": {
       "type": "nested",
       "properties": {
        "productKey": {
         "type": "keyword"
        },
        "criteria": {
         "type": "nested",
         "properties": { "criterionKey": { "type": "keyword" }, "criterionValue": { "type": "keyword" } }
        }
       }
      }
     }
    }
   }
  }
 }
}
POST my_index/_doc
{
 "contract": {
  "products": [
   {
    "productKey": "PK_0001",
    "criteria": [
     { "criterionKey": "CK_AAAA", "criterionValue": "above_50" },
     { "criterionKey": "CK_AAAB", "criterionValue": "all" }
    ]
   }
  ]
 }
}
POST my_index/_doc
{
 "contract": {
  "products": [
   {
    "productKey": "PK_0001",
    "criteria": [
     { "criterionKey": "CK_AAAA", "criterionValue": "below_50" },
     { "criterionKey": "CK_AAAB", "criterionValue": "dep" }
    ]
   }
  ]
 }
}
POST my_index/_doc
{
 "contract": {
  "products": [
   {
    "productKey": "PK_0002",
    "criteria": [
     { "criterionKey": "CK_AAAA", "criterionValue": "below_50" },
     { "criterionKey": "CK_AAAB", "criterionValue": "dep" }
    ]
   }
  ]
 }
}

```

I am able to count the occurrences of all criterion values per product. To do so, I use the following aggregation request:

```auto
POST my_index/_doc/_search
{
 "size": 0,
 "aggs": {
  "agg_by_product": {
   "nested": {
    "path": "contract.products"
   },
   "aggs": {
    "agg_by_product_key": {
     "terms": {
      "field": "contract.products.productKey"
     },
     "aggs": {
      "agg_by_product_crit": {
       "nested": {
        "path": "contract.products.criteria"
       },
       "aggs": {
        "agg_by_product_crit_key": {
         "terms": {
          "field": "contract.products.criteria.criterionKey",
          "include": ["CK_AAAB", "CK_AAAA"]
         },
         "aggs": {
          "agg_by_product_crit_value": {
           "terms": {
            "field": "contract.products.criteria.criterionValue"
           }
          }
         }
        }
       }
      }
     }
    }
   }
  }
 }
}

```

It returns:

```auto
{
 // ...
 "aggregations": {
  "agg_by_product": {
   "doc_count": 3,
   "agg_by_product_key": {
    "buckets": [
     {
      "key": "PK_0001",
      "doc_count": 2,
      "agg_by_product_crit": {
       "doc_count": 8,
       "agg_by_product_crit_key": {
        "buckets": [
         {
          "key": "CK_AAAB",
          "doc_count": 2,
          "agg_by_product_crit_value": {
           "buckets": [
            {
             "key": "dep", "doc_count": 1
            },
            {
             "key": "all", "doc_count": 1
            }
           ]
          }
         },
         {
          "key": "CK_AAAA",
          "doc_count": 2,
          "agg_by_product_crit_value": {
           "buckets": [
            {
             "key": "below_50", "doc_count": 1
            },
            {
             "key": "above_50", "doc_count": 1
            }
           ]
          }
         }
        ]
       }
      }
     },
     {
      "key": "PK_0002",
      "doc_count": 1,
      "agg_by_product_crit": {
       "doc_count": 4,
       "agg_by_product_crit_key": {
        "buckets": [
         {
          "key": "CK_AAAB",
          "doc_count": 1,
          "agg_by_product_crit_value": {
           "buckets": [
            {
             "key": "dep", "doc_count": 1
            }
           ]
          }
         },
         {
          "key": "CK_AAAA",
          "doc_count": 1,
          "agg_by_product_crit_value": {
           "buckets": [
            {
             "key": "below_50", "doc_count": 1
            }
           ]
          }
         }
        ]
       }
      }
     }
    ]
   }
  }
 }
}

```

Now **I would like to aggregate by criterion values of a specified criterion key** , in order to get something like this:

```auto
{
 // ...
 "aggregations": {
  "agg_by_product": {
   "doc_count": 3,
   "agg_by_product_key": {
    "buckets": [
     {
      "key": "PK_0001",
      "doc_count": 2,
      "agg_by_product_crit": {
       "doc_count": 8,
       "agg_by_product_crit_key": {
        "buckets": [
         {
          "key": "CK_AAAB",
          "doc_count": 2,
          "agg_by_product_crit_value": {
           "buckets": [
            {
             "key": "dep",
             "doc_count": 1,
             "AGG_BY_SOMETHING": {
              "buckets": [
               {
                "key": "CK_AAAA",
                "doc_count": 1,
                "AGG_BY_SOMETHING_2": {
                 "buckets": [
                  {
                   "key": "below_50", "doc_count": 1
                  }
                 ]
                }
               }
              ]
             }
            },
            {
             "key": "all",
             "doc_count": 1,
             "AGG_BY_SOMETHING": {
              "buckets": [
               {
                "key": "CK_AAAA",
                "doc_count": 1,
                "AGG_BY_SOMETHING_2": {
                 "buckets": [
                  {
                   "key": "above_50", "doc_count": 1
                  }
                 ]
                }
               }
              ]
             }
            }
           ]
          }
         }
        ]
       }
      }
     },
     {
      "key": "PK_0002",
      "doc_count": 1,
      "agg_by_product_crit": {
       "doc_count": 4,
       "agg_by_product_crit_key": {
        "buckets": [
         {
          "key": "CK_AAAB",
          "doc_count": 1,
          "agg_by_product_crit_value": {
           "buckets": [
            {
             "key": "dep",
             "doc_count": 1,
             "AGG_BY_SOMETHING": {
              "buckets": [
               {
                "key": "CK_AAAA",
                "doc_count": 1,
                "AGG_BY_SOMETHING_2": {
                 "buckets": [
                  {
                   "key": "below_50",
                   "doc_count": 1
                  }
                 ]
                }
               }
              ]
             }
            }
           ]
          }
         }
        ]
       }
      }
     }
    ]
   }
  }
 }
}

```

What should be the corresponding aggregation request?

---

<div class="post-metadata">

**Author:** ![mdeverdelhan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mdeverdelhan/32/43315_2.png) [@mdeverdelhan](https://discuss.elastic.co/u/mdeverdelhan)\
**Post date:** [April 2, 2019, 1:40pm UTC](https://discuss.elastic.co/t/reaggregate-on-nested-aggregation-results-using-elasticsearch/174842/2 "2019-04-02T13:40:26Z")

</div>

Finally I found a solution using a `reverse_nested` aggregation. I described it here: [https://stackoverflow.com/a/55476186/914404](https://stackoverflow.com/a/55476186/914404)

---

<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 30, 2019, 1:40pm UTC](https://discuss.elastic.co/t/reaggregate-on-nested-aggregation-results-using-elasticsearch/174842/3 "2019-04-30T13:40:26Z")

</div>

Closed on April 30, 2019, 1:40pm UTC

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