Kibana - joining data tables on specific fields

Elastic Stack Kibana
1

I have two metrics on Kibana that share a field.

metric.metric: "A"
metric.Id: {SomeNumber}
metric.Amount: {SomeNumber}

metric.metric: "B"
metric.Id: {SomeNumber}
metric.Capability: {SomeString}

I have visualizations for metrics A and B.

The data table visualization for metric A has columns: [Unique Id, SUM(Amount)]

The data table visualization for metric B has columns: [Unique Id, Capability]

Is it possible to create a data table that will contain columns [Unique Id, SUM(Amount), Capability]? I'm basically asking for a join operation on the Id column for these metrics.

Thanks,
Paulo

2

Hi Paulo,

I think you will want to add a sub bucket in your first data table visualization. This can be a Terms aggregation on the Capability field.

Here's an example using the sample eCommerce dataset in Kibana. I have added the customer gender column to the table as a sub-bucket of the country code.

Screenshot_2019-10-25%20Kibana(1)
Screenshot_2019-10-25 Kibana(1).png1280×1343 105 KB

3

Hi @nickpeihl, thanks for the quick answer!

That didn't work for me. It works if my document has all three fields: "Id", "Amount", "Capability". But I have documents that have both "Id" and "Amount", and documents that have both "Id" and "Capability".

When I follow your suggestion I get no results. I'm attaching images of when I have one bucket for each entity, and when I have two buckets:

keyslots%20-%20Copia
keyslots - Copia.png1096×738 38.9 KB

netamount%20-%20Copia
netamount - Copia.png1094×758 38.1 KB

Both:

both%20-%20Copia
both - Copia.png1096×738 33.8 KB

4

Hi Paulo,

Kibana does not support joins in that way. What is the cardinality of the capability field? Is it always one-to-one for the id field?

If so, maybe you can use an aggregation on the Capability field. Maybe something like Top Hit? See my example below.

Screenshot_2019-10-25%20Kibana(2)
Screenshot_2019-10-25 Kibana(2).png1280×1343 71.5 KB

5

The documents ("Id", "Capability") repeats a lot, but it would be fine to retrieve just the last occurrence of it for each "Id".

The documents ("Id", "Amount") also repeats a lot, and I need to perform a sum agreggation of all Amounts based on the Id. Here I need to sum all values of the documents.

I tried to use a Top Hit but it didn't work.

6

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