# Average on keyword of a nested object in Kibana Lens? **URL:** \ **Category:** Kibana\ **Tags:** lens\ **Created:** [February 4, 2023, 5:01pm UTC](https://discuss.elastic.co/t/average-on-keyword-of-a-nested-object-in-kibana-lens/324716 "2023-02-04T17:01:09Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![learningelastic](https://avatars.discourse-cdn.com/v4/letter/l/958977/32.png) [@learningelastic](https://discuss.elastic.co/u/learningelastic)\ **Post date:** [February 4, 2023, 5:01pm UTC](https://discuss.elastic.co/t/average-on-keyword-of-a-nested-object-in-kibana-lens/324716/1 "2023-02-04T17:01:09Z")
I'm working with Kibana Lens and I don't understand the results I'm getting. I started by running these commands in Dev Tools on a completely empty index called `avg1` ```auto POST avg1/_doc { "order_items": [ {"product": {"name":"laptop","price": 1000}}, {"product": {"name":"keyboard","price": 10}} ] } POST avg1/_doc { "order_items": [ {"product": {"name":"laptop","price": 1000}} ] } ``` Then I made a Kibana Data View called `avg1`. Then I went to Kibana Lens to create a bar graph. I chose `Top 5 Values on order_items.product.name.keyword` for the X axis. I chose `Average on order_items.product.price` for the Y axis. I was expecting these results: ```auto laptop: 1000 ----> because (1000 laptop + 1000 laptop) / 2 laptops = 1000 keyboard: 10 ----> because 10 keyboard / 1 keyboard = 10 ``` Instead, I got these results: ```auto laptop: 670 keyboard: 505 ``` ![image](https://us1.discourse-cdn.com/elastic/original/3X/f/6/f63e1ba9baae54a3c43a7099f282f2f1c64564af.png) How was the 670 and 505 calculated? * * * OK! I see that `505 = (1000 + 10) / 2`. And I see that `670 = (1000 + 1000 + 10) /3`. What I don't understand is why these formulas were used instead of the classical definition of "average"? For example, why isn't the formula for average simply "sum of all items that are laptop divided by the number of instances of laptop". And "sum of all items that are keyboard divided by the number of instances of keyboard"? ---
**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\ **Post date:** [February 5, 2023, 12:30am UTC](https://discuss.elastic.co/t/average-on-keyword-of-a-nested-object-in-kibana-lens/324716/2 "2023-02-05T00:30:46Z")
You should have a button called "Inspect" pretty much in the top right corner. Can you get the underlying query and response for that visualization? That will probably make it a lot clearer. ---
**Author:** ![learningelastic](https://avatars.discourse-cdn.com/v4/letter/l/958977/32.png) [@learningelastic](https://discuss.elastic.co/u/learningelastic)\ **Post date:** [February 5, 2023, 12:51am UTC](https://discuss.elastic.co/t/average-on-keyword-of-a-nested-object-in-kibana-lens/324716/3 "2023-02-05T00:51:25Z")
Thanks, here's the request: ```auto { "aggs": { "0": { "terms": { "field": "order_items.product.name.keyword", "order": { "1": "desc" }, "size": 5, "shard_size": 25 }, "aggs": { "1": { "avg": { "field": "order_items.product.price" } } } } }, "size": 0, "fields": [], "script_fields": {}, "stored_fields": [ "*" ], "runtime_mappings": {}, "_source": { "excludes": [] }, "query": { "bool": { "must": [], "filter": [], "should": [], "must_not": [] } } } ``` And here's the response: ```auto { "id": "Fko1ZWk0ZGZVUlcyTjdlZlpGN3BkMmcfUnVINk81WW9TSmloYkpsS0VLNEM5QToxMjgxMzA1Mg==", "rawResponse": { "took": 1, "timed_out": false, "_shards": { "total": 1, "successful": 1, "skipped": 0, "failed": 0 }, "hits": { "total": 2, "max_score": null, "hits": [] }, "aggregations": { "0": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "1": { "value": 670 }, "key": "laptop", "doc_count": 2 }, { "1": { "value": 505 }, "key": "keyboard", "doc_count": 1 } ] } } }, "isPartial": false, "isRunning": false, "total": 1, "loaded": 1, "isRestored": false } ``` I will try to improve my understanding of sub-aggregations, because that might clarify things ---
**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\ **Post date:** [February 5, 2023, 1:12am UTC](https://discuss.elastic.co/t/average-on-keyword-of-a-nested-object-in-kibana-lens/324716/4 "2023-02-05T01:12:47Z")
You can copy the request and work on it in the dev tools. And it looks like you're running into the "nested" problem — how arrays of subdocuments get flattened out. [Nested aggregation | Elasticsearch Guide [8.6] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-nested-aggregation.html) might be worth a look there. ---
**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\ **Post date:** [February 5, 2023, 1:51am UTC](https://discuss.elastic.co/t/average-on-keyword-of-a-nested-object-in-kibana-lens/324716/5 "2023-02-05T01:51:57Z")
And I should have added: 1. Nested needs to be set up in the mapping (and has good background information there as well): [Nested field type | Elasticsearch Guide [8.6] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html) 2. Nested currently isn't supported for visualizations: [Nested field support in Visualize · Issue #58175 · elastic/kibana · GitHub](https://github.com/elastic/kibana/issues/58175) So your best approach, if you need this visualization, is probably a different structure for your documents. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 5, 2023, 1:52am UTC](https://discuss.elastic.co/t/average-on-keyword-of-a-nested-object-in-kibana-lens/324716/6 "2023-03-05T01:52:03Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.