Average on keyword of a nested object in Kibana Lens?

[learningelastic]

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

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:

laptop: 1000  ----> because (1000 laptop + 1000 laptop) / 2 laptops = 1000
keyboard: 10 ----> because 10 keyboard / 1 keyboard = 10

Instead, I got these results:

laptop: 670
keyboard: 505

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"?

[xeraa]

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.

[learningelastic]

Thanks, here's the request:

{
  "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:

{
  "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

[xeraa]

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 might be worth a look there.

[xeraa]

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
2. Nested currently isn't supported for visualizations: Nested field support in Visualize · Issue #58175 · elastic/kibana · GitHub

So your best approach, if you need this visualization, is probably a different structure for your documents.