Sub-aggregate a multi-level nested composite aggregation

Question

I'm trying to set up a search query that should composite aggregate a collection by a multi-level nested field and give me some sub-aggregation metrics from this collection. I was able to fetch the composite aggregation with its buckets as expected but the sub-aggregation metrics come with 0 for all buckets. I'm not sure if I am failing to correctly point out what fields the sub-aggregation should consider or if it should be placed inside a different part of the query.

My collection looks similar to the following:

{
  id: '32ead132eq13w21',
  statistics: {
    clicks: 123,
    views: 456
  },
  categories: [{ //nested type
    name: 'color',
    tags: [{ //nested type
      slug: 'blue'
    },{
      slug: 'red'
    }]
  }]
}

Bellow you can find what I have tried so far. All buckets come with clicks sum as 0 even though all documents have a set clicks value.

GET /acounts-123321/_search
{
  "size": 0,
  "aggs": {
    "nested_categories": {
     "nested": {
        "path": "categories"
     },
     "aggs": {
           "nested_tags": {
             "nested": {
                "path": "categories.tags"
              },
              "aggs": {
                "group": {
                  "composite": {
                     "size": 100,
                     "sources": [
                       { "slug": { "terms" : { "field": "categories.tags.slug"} }}
                     ]
                   },
                   "aggregations": {
                     "clicks": {
                       "sum": {
                         "field": "statistics.clicks"
                       }
                     }
                   }
                }
              }
            }
          }
       }
  }
}

The response body I have so far:

{
  "took" : 6,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1304,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "nested_categories" : {
      "doc_count" : 1486,
      "nested_tags" : {
        "doc_count" : 1486,
        "group" : {
          "buckets" : [
            {
              "key" : {
                "slug" : "red"
              },
              "doc_count" : 268,
              "clicks" : {
                "value" : 0.0
              }
            }, {
              "key" : {
                "slug" : "blue"
              },
              "doc_count" : 122,
              "clicks" : {
                "value" : 0.0
            },
            .....
          ]
        }
      }
    }
  }
}

Answer 1

In order for this to work, all sources in the composite aggregation would need to be under the same nested context.

I've answered something similar a while ago. The asker needed to put the nested values onto the top level. You have the opposite challenge -- given that the stats.clicks field is on the top level, you'd need to duplicate it across each entry of the categories.tags which, I suspect, won't be feasible because you're likely updating these stats every now and then…

If you're OK with skipping the composite approach and using the terms agg without it, you could make the summation work by jumping back to the top level thru reverse_nested:

{
  "size": 0,
  "aggs": {
    "nested_tags": {
      "nested": {
        "path": "categories.tags"
      },
      "aggs": {
        "by_slug": {
          "terms": {
            "field": "categories.tags.slug",
            "size": 100
          },
          "aggs": {
            "back_to_parent": {
              "reverse_nested": {},
              "aggs": {
                "clicks": {
                  "sum": {
                    "field": "statistics.clicks"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

This'll work just as fine but won't offer pagination.

---

Clarification

If you needed a color filter, you could do:

{
  "size": 0,
  "aggs": {
    "categories_parent": {
      "nested": {
        "path": "categories"
      },
      "aggs": {
        "filtered_by_color": {
          "filter": {
            "term": {
              "categories.name": "color"
            }
          },
          "aggs": {
            "nested_tags": {
              "nested": {
                "path": "categories.tags"
              },
              "aggs": {
                "by_slug": {
                  "terms": {
                    "field": "categories.tags.slug",
                    "size": 100
                  },
                  "aggs": {
                    "back_to_parent": {
                      "reverse_nested": {},
                      "aggs": {
                        "clicks": {
                          "sum": {
                            "field": "statistics.clicks"
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}