How to merge two search values two a single one

Elastic Stack Kibana
1

Hi All,

I have one improvement task.

For example i have one keyword in kibana called fruits

so i need to search the different fruits names say apple and orange and got two count values say A and B for each respective fruit.

but in visualization i don't want the results to be visualized as

apple 5
orange 6

instead i want to merge the count of the these 2 search values to a new one called
fruits and it will show the count as 11.

it should come like

fruits 11

in Kibana visualization instead of the previous one shown above.

but I want to merge the values of both to a new value say C,

How to do that using Kibana scripted field or making changes in the logstash configuration?

2

Hello @Matish_Bhuyan

If we were on Elasticsearch DSL, we could use the value count aggregation.

Demo data

PUT discuss/_doc/1
{
  "timestamp" : "2020-01-19T03:26:21.326Z",
  "fruits": "banana"
}
PUT discuss/_doc/2
{
  "timestamp" : "2020-01-19T03:26:21.326Z",
  "fruits": "banana"
}
PUT discuss/_doc/3
{
  "timestamp" : "2020-01-19T03:26:21.326Z",
  "fruits": "apple"
}
PUT discuss/_doc/4
{
  "timestamp" : "2020-01-19T03:26:21.326Z",
  "fruits": ["apple", "banana", "orange"]
}

Query

GET discuss/_search
{
  "aggs": {
    "total": {
      "value_count": {
        "field": "fruits.keyword"
      }
    }
  }
}

Kibana

On Kibana, the value_count aggregation is exposed only in TSVB.

I've tried to put in place an example, but it seems there's a problem (at least on 7.7.0).

image
image4362×766 134 KB

image
image4250×380 33.1 KB

image
image4378×886 157 KB

The fruits.keyword field doesn't show up in Field.

It seems we have an open issue to track the support for value_count in traditional visualizations

But as I see the value_count in TSVB, I would expect to work... Sorry to ping @lukeelmers - Is this expected?

3

Hi Luca,

Thanks a lot for the response and the example,

But we have a fruit keyword which is created and the only thing is we are populating different value to get the total count.

My issue is this -- I have one keyword named as service where i get two different values.

image
image1143×244 10.8 KB

this is how my data table looks now

But i want a filter value in kibana or logstash that both the service.keyword output as you see should be merged to a new , Which will be called as -- > FIRST NID CARD and also the count of both the values should be added to it.

4

and i am using the elastic search version 7.2

And i can't upgrade to the recent version which is 7.7

5

thanks Luca

I have resolved the issue now by adding this in the filter section of logstash config

if [keyword.value] in ["x", "y"]
{
mutate {
update => { "keyword.value" => "z" }
}

if [groceries] in ["apple", "orange"]
{
mutate {
update => { "groceries" => "fruits" }
}

so if now the individual apple and orange search value is updated to fruits
and also their individual count value too comes to the fruits count value.

so before update my dashboard looks like
apple 5
orange 6

After update the filter option my dashboard is
fruits 11

6

@Luca_Belluccini If you are running into issues with value count in TSVB then it sounds like a potential bug... currently TSVB is the one place we expect value count aggs to work in Kibana.

There is an Aggs Support in Kibana meta issue which is helpful in understanding where you can expect to find support for various agg types: https://github.com/elastic/kibana/issues/58628

1 Like
7

Thank you Luke

I've opened:

@Matish_Bhuyan , while your question helped us to identify a Kibana issue (Thanks :smiley: )

If I understood correctly from the first question, you have/had documents such as:

PUT discuss/_doc/1
{
  "timestamp" : "2020-01-19T03:26:21.326Z",
  "fruits": "banana"
}
PUT discuss/_doc/2
{
  "timestamp" : "2020-01-19T03:26:21.326Z",
  "fruits": "banana"
}
PUT discuss/_doc/3
{
  "timestamp" : "2020-01-19T03:26:21.326Z",
  "fruits": "apple"
}
PUT discuss/_doc/4
{
  "timestamp" : "2020-01-19T03:26:21.326Z",
  "fruits": ["apple", "banana", "orange"]
}

And you wanted to get 3 as count, instead of obtaining the count, split by terms.
The correct way to handle this, without modifying the structure of the document with Logstash, would be to use value_count aggregation.

Once the bug I just opened will be fixed, you'll be able to select service.keyword and it would output the count of values, without splitting them in the different values.

If you take the time to run the query:

GET discuss/_search
{
  "aggs": {
    "total": {
      "value_count": {
        "field": "fruits.keyword"
      }
    }
  }
}

You will see we obtain 6 (given the demo data I've shared).

That said, you are free to solve the issue using Logstash, but you have to know all the different values you might have in the field, which is not always possible.

8

Yes ,Thanks @Luca_Belluccini lot for the other option to fix the issue through the lucene syntax.

9

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.