How to split values from a column in a table in Kibana?

[whalien52]

Hi guys,

I am trying to visualize a Data Table where there is a field called transactiondata which is a column in the table and the column contains values like Key1:Value1.

What I need to do is I need to display only value Part for each key.
Suppose there are 10 different values for each key, I only want to see the values for key1 in a table format.
So, I have tried using filters in bucket aggregation when I am trying to write the query in filter field which is transactiondata: Key1:Value2, it is giving me error.
Please help me on this. I am looking forward to you guys.

[jen-huang]

Hi, in your visualization buckets, open Advanced and try adding Key1:.* to the Include field:

This should filter this field down to just values that start with Key1:. Include and Exclude fields expect regex expressions: https://www.elastic.co/guide/en/elasticsearch/reference/6.2/search-aggregations-bucket-terms-aggregation.html#_filtering_values_3

[christophilus (Chris Davies)]

You could also do this with scripted fields:

// I created a scripted field "mykey" that looked like this
doc['transactiondata.keyword'].value.splitOnToken(':')[0]

// And I created a scripted field "myvalue" that looked like this
doc['transactiondata.keyword'].value.splitOnToken(':')[0]

Then, I created a data table with a filter: mykey:key1 and just displayed a terms aggregation on myvalue.

[whalien52]

Thanks Jen. I will try this and let you know.
Thanks for helping.

[whalien52]

Thanks Chris for helping. I will try this and let you know.
Btw by Creating a datatable, did you mean Data Table visualization where you added the filter and used terms aggregation?

[whalien52]

Thanks Chris It worked. I just had to change the array index.

[whalien52]

Hi, I didnt get the desired output.. it is showing me the entire line and also didnt get the output in a table format.