Kibana - Visualisation aggregation on Nested Object Issue

[charlz]

On creating Visualisation Aggregation of sum on property 'item.count' facing issue
grouping on quote.created_at
Result
Date ------------Count
2020-05-10 ----- 1
2020-03-05 ----- 2

 {
    "id":50,
    "created_at":"2019-05-10T00:00:00",
     "item":[
          {"id":5,"count":1,"created_at":"2020-03-05T00:00:00"},
          {"id":11, "count":1,"created_at":"2020-03-05T00:00:00"}
      ]
   },
  {
      "id":65,
       "created_at":"2019-05-01T00:00:00",
        "item":[
             {"id":15, "count":1,"created_at":"2020-05-10T00:00:00"},
             {"id":19, "count":1,"created_at":"2020-03-05T00:00:00"}
         ]
  } 

Expected Result
Date ------------Count
2020-05-10 ----- 1
2020-03-05 ----- 3

[lukeelmers]

Hi @charlz,

Kibana visualizations do not currently support querying on nested fields, however there is a Github issue tracking the overall progress of nested fields support if you'd like to follow along.

As of 7.6 you can use nested fields in Discover, Index Patterns, KQL, & the Filter Bar, but visualizations are not enabled yet. There's also a dedicated issue tracking progress in visualize specifically.

[charlz]

Hi @lukeelmers

Yes, Nested objected is not supported currently, since I have not mapped the field ('item') as Nested Objected. the fields are available in the Kibana visualisations my questions is aggregation (sum) on the field (which of nested) must provide the result similar field (integer) since the field is available in visualisations.

[lukeelmers]

the fields are available in the Kibana visualisations my questions is aggregation (sum) on the field (which of nested) must provide the result similar field (integer) since the field is available in visualisations.

I'm not 100% sure I understand this question, so apologies if there is something I'm overlooking. But visualizations in Kibana don't aggregate on nested fields like that, regardless of how you set your mappings -- if you want to run aggregations on the data in the items list, you aren't going to get the results you are looking for.

My recommendation would be to restructure your data so that each item is a separate document which references the parent ID:

{"id":15, "count":1, "created_at":"2020-05-10T00:00:00", "parent_id": 65},
{"id":19, "count":1, "created_at":"2020-03-05T00:00:00", "parent_id": 65},
{"id":11, "count":1, "created_at":"2020-03-05T00:00:00", "parent_id": 50},
{"id":5, "count":1, "created_at":"2020-03-05T00:00:00", "parent_id": 50}

Then doing the same sum aggregation should return the expected results. This will also give you greater long-term flexibility in your visualizations.

[charlz]

@lukeelmers
Thanks, Is it your recommendation to have restructure the document with "join" (parent-child) in single Index?

I would also like to have data structure to relation kind-of document instead of single document,
Could you give more in-sight, since I'm using Elastic-search version 7.2 which does not have mappings. How does Routing works?
I was checking into https://www.elastic.co/guide/en/elasticsearch/reference/7.2/parent-join.html for reference.
My use case does suit for "Join" the one-to-many relationship where one entity significantly outnumbers the other entity.

But does Kibana support Parent/Child? If not what would be other options?

Because in my use case few filters for Kibana Dashboard of child (item) are based on the parent (root), eg (properties - state, type, active)

Count number of Child Item filtered by parent state=1 and active =1 and few other scenarios

 {
    "id":50,"created_at":"2019-05-10T00:00:00","state":1,"type":"Blue","active":1,
     "item":[
          {"id":5,"count":1,"created_at":"2020-03-05T00:00:00"},
          {"id":11, "count":1,"created_at":"2020-03-05T00:00:00"}
      ]
   },
  {
      "id":65,"created_at":"2019-05-01T00:00:00","state":3,"type":"Green","active":0,
        "item":[
             {"id":15, "count":1,"created_at":"2020-05-10T00:00:00"},
             {"id":19, "count":1,"created_at":"2020-03-05T00:00:00"}
         ]
  }

After restructuring the doc

{"id":50,"created_at":"2019-05-10T00:00:00","state":1,"type":"Blue","active":1},
{"id":65,"created_at":"2019-05-01T00:00:00","state":3,"type":"Green","active":0},
{"id":5,"count":1,"created_at":"2020-03-05T00:00:00","parent_id":50},
{"id":11, "count":1,"created_at":"2020-03-05T00:00:00","parent_id":50},
{"id":15, "count":1,"created_at":"2020-05-10T00:00:00","parent_id":65},
{"id":19, "count":1,"created_at":"2020-03-05T00:00:00","parent_id":65}

[charlz]

can anyone guide me on this scenario, right way of structuring the data?

[charlz]

can anyone guide me on this scenario, right way of structuring the data?