# Metric aggregations on custom fields that use flattened field type **URL:** \ **Category:** Elasticsearch\ **Created:** [March 25, 2022, 7:59pm UTC](https://discuss.elastic.co/t/metric-aggregations-on-custom-fields-that-use-flattened-field-type/300724 "2022-03-25T19:59:31Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![amkoehler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/amkoehler/32/57559_2.png) [@amkoehler](https://discuss.elastic.co/u/amkoehler)\ **Post date:** [March 25, 2022, 7:59pm UTC](https://discuss.elastic.co/t/metric-aggregations-on-custom-fields-that-use-flattened-field-type/300724/1 "2022-03-25T19:59:31Z")
Hello, I am looking for a way to index our documents in such a way that gives me the ability to run metric aggregations on custom number fields. Here's an example data structure we are indexing: ```auto { name: 'My Element', fields: { UBI1h9vH1836n5JqNaGb: 'a string value', 0L4ctTlt3agO1BLyI1WK: 'another string value', 0wzYL8hIKUNEu5uIPt3V: 42, 165N8Urzd8QOMgki898o: 10.25, }, } ``` We concatenate all custom text fields into an `_allText` property, which is a text mapping and allows for full text search across those custom text fields. I mention this because this was a great solution for working around the limitations of `flattened` while still providing full text search capabilities on those values. We have `fields` as a flattened field type to prevent mappings explosion, since across our index we have 1000s of custom user fields. ```auto { // 'text' name: 'My Element', // 'flattened' fields: { UBI1h9vH1836n5JqNaGb: 'a string value!', 0L4ctTlt3agO1BLyI1WK: 'another string value', 0wzYL8hIKUNEu5uIPt3V: 42, 165N8Urzd8QOMgki898o: 1021 }, // 'text' (concatenated values from all custom string fields) _allText: 'a string value! another string value', } ``` What I would like to do next is index our documents in such a way that allows for aggregations on those custom number fields. The `flattened` field type does not allow metric aggregations. In this specific example I would like to do metric aggregations on those numeric 42 and 1021 values. For example, a `sum` operation that totals up every `fields.0wzYL8hIKUNEu5uIPt3V` in a query containing 100 documents. Any ideas on how I should approach this? ---
**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\ **Post date:** [March 28, 2022, 8:37am UTC](https://discuss.elastic.co/t/metric-aggregations-on-custom-fields-that-use-flattened-field-type/300724/2 "2022-03-28T08:37:21Z")
There is an open issue for supporting numerics in flattened field type. See [Support for a fully numeric flattened field · Issue #61550 · elastic/elasticsearch · GitHub](https://github.com/elastic/elasticsearch/issues/61550) I don't have any concrete idea to fix this than to maybe try out `nested` and structure the numbered values like this: ```auto field: [ { key: 0wzYL8hIKUNEu5uIPt3V, value: 42 }, { key: 165N8Urzd8QOMgki898o, value: 1021 } ] ``` I have not fully thought this through, but maybe you are able to retrieve the same numeric infos you need using nested filters in the aggs. Might be worth a try. ---
**Author:** ![amkoehler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/amkoehler/32/57559_2.png) [@amkoehler](https://discuss.elastic.co/u/amkoehler)\ **Post date:** [March 28, 2022, 2:35pm UTC](https://discuss.elastic.co/t/metric-aggregations-on-custom-fields-that-use-flattened-field-type/300724/3 "2022-03-28T14:35:56Z")
I hadn't thought to try nested. I'll do some testing to see if that could work. Will keep an eye on the GH issue as well. Thanks @spinscale this is very helpful. ---
**Author:** ![amkoehler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/amkoehler/32/57559_2.png) [@amkoehler](https://discuss.elastic.co/u/amkoehler)\ **Post date:** [March 29, 2022, 9:34pm UTC](https://discuss.elastic.co/t/metric-aggregations-on-custom-fields-that-use-flattened-field-type/300724/4 "2022-03-29T21:34:12Z")
Hey @spinscale have another thought on this. Would a painless script work here too? I've never used those before but the examples make a lot of references to metric aggregations: [Painless examples for transforms | Elasticsearch Guide [8.1] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-painless-examples.html). If I need to support dynamic IDs, seems like a script might work, but I am not aware of painless' limitations. Side note - I put together a very basic example of nested aggregation and its working well so far. ---
**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\ **Post date:** [March 30, 2022, 7:49am UTC](https://discuss.elastic.co/t/metric-aggregations-on-custom-fields-that-use-flattened-field-type/300724/5 "2022-03-30T07:49:21Z")
Hey, indeed, a scripted metric aggregation should work, as it allows you to create arbitrary data from each document and do all the calculations by doing the proper filtering via painless. Another relatively new feature are [runtime fields](https://www.elastic.co/guide/en/elasticsearch/reference/8.1/runtime.html) - but I suppose that is not dynamic enough in your case with arbitrary keys/values. One thing to keep in mind: When using aggregations the data is extracted from the index data structure, not from the `_source`, so you would probably still end up with some mapping? --Alex ---
**Author:** ![amkoehler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/amkoehler/32/57559_2.png) [@amkoehler](https://discuss.elastic.co/u/amkoehler)\ **Post date:** [March 30, 2022, 2:52pm UTC](https://discuss.elastic.co/t/metric-aggregations-on-custom-fields-that-use-flattened-field-type/300724/6 "2022-03-30T14:52:32Z")
Not ready to dive into painless yet but good to know it may be an option. That's a good thing to keep in mind with the additional mappings. Adding a new nested mapping containing the key/value pairs is definitely acceptable for us. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 27, 2022, 2:52pm UTC](https://discuss.elastic.co/t/metric-aggregations-on-custom-fields-that-use-flattened-field-type/300724/7 "2022-04-27T14:52:44Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.