Normalized pivot tables


#1

Good evening everyone.
I am currently looking for a performant way to create normalized pivot tables based on three different fields. We have several thousends / millions of documents with the following JSON structure in different indicies:

"_source" : {
     "count" : 1234,
     "dimensions" : {
        "foo" : "foo value",
        "bar" : "value bar",
        ...
    }
}

What we need is a simple pivot-table based on the top-10 values of "dimensions.foo" and "dimensions.bar" (one field per table axis). The top-10 values should be based on the sum of the "count" field.

Too fill the table with numbers we need the sum of the field "count" for each combination of both fields.

I tried several ways with combined Terms-Aggregators and Composite-Aggregators, but both of them dont match my requirements.

Any suggestions how to solve this problem with just one query? Its very important, that the table is based on the top-10 values per field. A combination of Terms-Aggregators dont work because each sub-bucket should contain the same top-10 values.


#2

Any ideas?