Table aggregation

Elastic Stack Kibana
1

Hello!

Here is the initial data:

POST someindex/_doc
{
  "name": "Amsterdam",
  "documents": {
    "locations": [
      {
        "category": "aaa",
        "url": "https://aaa1.com"
      },
      {
        "category": "bbb",
        "url": "https://bbb1.com"
      },
      {
        "category": "aaa",
        "url": "https://aaa11.com"
      },
      {
        "category": "ccc",
        "url": "https://ccc1.com"
      }
      ]
  }
}
POST someindex/_doc
{
  "naam": "Hague",
  "documents": {
    "locations": [
      {
        "category": "ccc",
        "url": "https://ccc2.com"
      }
      ]
  }
}
POST someindex/_doc
{
  "naam": "Utrecht",
  "documents": {
    "locations": [
      {
        "category": "aaa",
        "url": "https://aaa3.com"
      },
      {
        "category": "ddd",
        "url": "https://ddd3.com"
      }
      ]
  }
}

I want to show how many categories every city has

So, basically I would like to build a table like this:

	              aaa    bbb   ccc    ddd
Amsterdam	      2      1     1      0
Hague	          0      0     1      0
Utrecht	          1      0     0      1

For now, I was able to build a table representing if the specific category is present for the city, but cannot make it display the number of categories as I wish:

	              aaa    bbb   ccc    ddd
Amsterdam	      1      1     1      0
Hague	          0      0     1      0
Utrecht	          1      0     0      1

However, if I switch the metrics field from "count(records)" to "count(documents.locations.category)" Kibana visualize it in the following way:

	              aaa    bbb   ccc    ddd
Amsterdam	      3      3     3      0
Hague	          0      0     1      0
Utrecht	          2      0     0      2

I tried switching to explicit nested type in mapping but then Kibana does not allow to build me a visualization at all

So, is there a solution to my problem or it cannot be done?

Kibana is 8.15.3

Thank you!

2

Hi Nikita, I'd say this qualifies under the realm of nested fields problems. Unfortunately, nested fields are not well supported at this moment. We have an issue for it: Nested field support · Issue #1084 · elastic/kibana · GitHub