Data table with a complex Top Hit

[sanek64]

Hi all,
I am trying to use a Top Hit aggregation in my Data table. As i understand it will provide me with the latest value of a metric. So, that is what i want, but i dont want the lastest value of the single metric, but for the multiple-value metric.
Imagine, that i have a car shop, where i store and trying to sell my cars. Every period of time i am indexing the "state" of my shop, which is described by the number (field called balance) of cars of a particular engine power (another field) i have now in my store. I always dived my Balance of cars by Engine Power, they are unique (only those that are in a gson: 50, 70, 100, 150, 200 )

Store index

  {
	"_index": "store",
	"_type": "cars",
	"_id": "a323af6a-ee2c-4791-ab18-34e3f18ed92e",
	"_version": 1,
	"_score": null,
	"_source": {
		"carKey": {
			"id": "a323af6a-ee2c-4791-ab18-34e3f18ed92e",
			"datetime": 1511268670386
		},
		"carState": [
			{
				"assembly": {
					"engine power": 50
				},
				"balance": 60
			},
			{
				"assembly": {
					"engine power": 70
				},
				"balance": 176
			},
			{
				"assembly": {
					"engine power": 100
				},
				"balance": 40
			},
			{
				"assembly": {
					"engine power": 150
				},
				"balance": 20
			},
			{
				"assembly": {
					"engine power": 200
				},
				"balance": 80
			}
		]
	}
}

I want to get the latest state of my store, so i need a Data Table where i could see the latest car Balance of a particular Engine power (it is changed, because i may sell some cars of this power). It should be smthg like this:

I have tryed to make Data Table visualisation: choose Top Hit aggregation, choose Balance as a field of this aggregation, aggregate with Max and sort on datetime (time of a state snapshot). As a bucket I choose a Term Split Aggregation, with a metric field Engine Power choosing the right order and size. I got the fine representation of my data (like in the excel picture), but the Wrong result. it seemed to be no connections between a bucket and the aggregation, engine power and Balance were not connected at all. it was the one value for all of the Engine Powers or different wrong results (when i was trying to change the config).

Please, provide me the right configuration of the Data Table Visualisation to obtain the appropriate result in my case. It would be wonderfull if you provide pictures to. Thanks.

[ppisljar (Peter Pisljar)]

doing a term split on the engine power will give you top N most often occurring terms for engine power in the whole time range you are looking at ... if you set the size of terms high enough so it includes all the engine powers, that should work ok (showing you all the engine powers in the first column), also you probably want to set order to ascending and order by you should leave at 'term'.

then as a metric, you should select top hit, set the hit number to 1 (as you are only interested in the latest entry for each engine power) and sort by date time field. As you only select 1 entry, the aggregate with should have no effect (only comes to play if size is more than 1)

[sanek64]

Thank you, for your fast answer.
But as i sad previously, it seems there is no connection between a Term bucket and Top Hit.

I made a Bucket like this (I have 24 Engine Powers):

Then i made Top Hit like this:

And my result was:

I renamed my metrics in case of security, but this does not matter.
You may see, that for all of the Engine Power there is the same Top Hit Latest Balance - that is not true. After looking at my data i noticed that 67.188 is the max value for the Latest Balance in a Top Hit document (the latest state of my shop). So, this configuration provide me with the latest Balance, but does not separate it to the Engine Powers. In other words, i need the the Latest State of my Shop (relation Engine Power - Latest Balance) represented as a table.

[ppisljar (Peter Pisljar)]

wow, thats really weird.

could you open up the spy panel (little arrow on the bottom of your table) and copy-paste the request and response (you can select that in a dropdown where it will say table by default)

also, which version of kibana are you running ?

[sanek64]

I agree with you.
According to my Index Store in the first message, where of course i renamed my real fields in case of security, but i assure you that it mathes the real structure, I get the following request:

[sanek64]

Request

     {
      "size": 0,
      "_source": {
        "excludes": []
      },
      "aggs": {
        "2": {
          "terms": {
            "field": "carState.assembly.enginepower",
            "size": 24,
            "order": {
              "_term": "asc"
            }
          },
          "aggs": {
            "1": {
              "top_hits": {
                "docvalue_fields": [
                  "carState.balance"
                ],
                "_source": "carState.balance",
                "size": 1,
                "sort": [
                  {
                    "carKey.datetime": {
                      "order": "desc"
                    }
                  }
                ]
              }
            }
          }
        }
      },
      "stored_fields": [
        "*"
      ],
      "script_fields": {},
      "docvalue_fields": [
        "carKey.datetime"
      ],
      "query": {
        "bool": {
          "must": [
            {
              "match_all": {}
            },
            {
              "match_phrase": {
                "filter_1_name": {
                  "query": "filter_1_value"
                }
              }
            },
            {
              "match_phrase": {
                "filter_2_name": {
                  "query": "filter_2_value"
                }
              }
            },
            {
              "range": {
                "carKey.datetime": {
                  "gte": 1511079625416,
                  "lte": 1511080074846,
                  "format": "epoch_millis"
                }
              }
            }
          ],
          "filter": [],
          "should": [],
          "must_not": []
        }
      }
    }

You may see two additional match_phrase there, but they are my filters and they dont matter.

[sanek64]

Response

 {
         "took": 29,
          "timed_out": false,
         "_shards": {
             "total": 5,
            "successful": 5,
            "skipped": 0,
            "failed": 0
          },
           "hits": {
             "total": 8,
             "max_score": 0,
             "hits": []
           },
          "aggregations": {
             "2": {
              "doc_count_error_upper_bound": 0,
               "sum_other_doc_count": 0,
             "buckets": [
                {
                  "1": {
                     "hits": {
                      "total": 8,
                       "max_score": null,
                      "hits": [
                        {
                           "_index": "store",
                           "_type": "cars",
                           "_id": "ebebcbba-21c5-4809-9b29-847c082db449",
                           "_score": null,
                           "_source": {
                            "carState": [
                               {
                                 "balance": 43.24294040753292
                              },
                              {
                                "balance": 43.90465082939961
                               },
                               {
                                 "balance": 44.5663612512663
                               },
                               {
                                 "balance": 45.007501532510766
                              },
                               {
                                 "balance": 45.669211954377445
                               },
                               {
                                 "balance": 46.11035223562191
                               },
                               {
                                 "balance": 46.77206265748859
                               },
                               {
                                 "balance": 47.213202938733055
                               },
                               {
                                 "balance": 47.87491336059975
                               },
                               {
                                 "balance": 48.316053641844206
                               },
                              {
                                 "balance": 48.97776406371089
                               },
                               {
                                 "balance": 49.41890434495535
                               },
                               {
                                "balance": 50.5217550480665
                               },
                               {
                                 "balance": 51.183465469933196
                              },
                               {
                                "balance": 56.645122640677506
                              },
                              {
                                "balance": 57.322315020718094
                             },
                             {
                                "balance": 57.773776607411804
                              },
                              {
                               "balance": 46.04755420271283
                             },
                             {
                              "balance": 59.87363048420702
                               },
                               {
                                "balance": 60.10005442797198
                            },
                              {
                                "balance": 60.55290231550188
                               },
                              {
                                "balance": 50.750412538287144
                               },
                               {
                               "balance": 58.39245099990136
                              },
                              {
                                 "balance": 54.8163422774007
                              }
                            ]
                          },
                         "fields": {
                             "carState.balance": [
                               43.24293899536133,
                               43.9046516418457,
                               44.56636047363281,
                               45.00749969482422,
                               45.669212341308594,
                               46.04755401611328,
                               46.1103515625,
                               46.772064208984375,
                               47.21320343017578,
                               47.87491226196289,
                               48.31605529785156,
                               48.97776412963867,
                               49.41890335083008,
                               50.52175521850586,
                               50.75041198730469,
                               51.18346405029297,
                               54.816341400146484,
                               56.64512252807617,
                               57.32231521606445,
                               57.77377700805664,
                               58.392452239990234,
                               59.87363052368164,
                               60.10005569458008,
                               60.55290222167969
                             ]
                           },
                           "sort": [
                             1511079869780
                           ]
                         }
                       ]
                     }
                   },
                   "key": 0,
                   "doc_count": 8
                 },
                 {
                  the same 24 bodies with a different keyes (200, 500, 1000,...5000000)
                 }    
              ]
            }
           },
           "status": 200
}

[sanek64]

Also there is my Response. Please, be noticed, that i have 24 Engine Power from 0 to 5000000 and in the Response i got similar 24 json bodies (there is an array in "Carstate" field, so i skipped all 24 same bodies and provide you only the first with the key 0 (Engine Power = 0) and the Last(Engine power = 5000000) with a key = 5000000.

So in a _source carState you may see the right Balances - they are the latest balances for each volume. And for course for every 24 Engine Power there will be the same things. But then there is "fields" -> "carState.Balance" structure in which all of the my appropriate latest balances sorting (i dont know for what) and then it is choisen the max of this sorted balances (that is 67.188). Of course these operations are provided for every bucket (for 24 Engine powers) and of course there is the same result of 67.188
Where am i wrong?

P.S. my kibana version is 6.0.0
P.P.S. Sorry, i have to divide my answer in a several messages, because i cant export my Req/Resp properly, they are to long.