# Normalized pivot tables

**URL:** <https://discuss.elastic.co/t/normalized-pivot-tables/176948>\
**Category:** Elasticsearch\
**Created:** [April 15, 2019, 5:43pm UTC](https://discuss.elastic.co/t/normalized-pivot-tables/176948 "2019-04-15T17:43:08Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kossinator](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kossinator/32/44190_2.png) [@Kossinator](https://discuss.elastic.co/u/Kossinator)\
**Post date:** [April 15, 2019, 5:43pm UTC](https://discuss.elastic.co/t/normalized-pivot-tables/176948/1 "2019-04-15T17:43:08Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Kossinator](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kossinator/32/44190_2.png) [@Kossinator](https://discuss.elastic.co/u/Kossinator)\
**Post date:** [April 17, 2019, 10:13am UTC](https://discuss.elastic.co/t/normalized-pivot-tables/176948/2 "2019-04-17T10:13:45Z")

</div>

Any ideas?

---

<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:** [May 15, 2019, 10:13am UTC](https://discuss.elastic.co/t/normalized-pivot-tables/176948/3 "2019-05-15T10:13:56Z")

</div>

Closed on May 15, 2019, 10:13am UTC

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