Creating a pie with aggregation and sub-aggregation on the same field

[m3000]

Hi,
I have an index containing documents representing a products. Each product includes a field called "category" with multiple values.
I would like to display a pie chart splited by each category, and also display "sub-categories" using sub aggregation on the same field. Is there a way to remove the "main" category from the sub aggregation?
for example:
If I have 3 categories {A, B, C}:
sub aggregation on bucket A should include only {B, C}
sub aggregation on bucket B should include only {A, C}
sub aggregation on bucket C should include only {A, B}

Thanks.

[LeeDr (Lee Drengenberg)]

Are you saying that the category field contains either a category or a sub-category? If so, I don't think you're going to get good results with that data.

You could try using the Filters aggregation type where you would create a filter for each product in the first aggregation, and then Add sub-bucket and Split slices and use another Filters aggregation for the sub-category. Something like this;

But I think this would only work properly if category and sub-category were two separate fields and each doc in your index had both those fields set properly.

It might be possible to use 2 scripted fields to create a new category and sub-category field but I think you're have to script the name of every category and sub-category which might not scale very well.

Is there something that indicates if it's a category or sub-category?

Regards,
Lee