Kibana - joining data tables on specific fields

[pbsf (Paulo Barros)]

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

[nickpeihl]

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.

[pbsf (Paulo Barros)]

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:

Both:

[nickpeihl]

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.

[pbsf (Paulo Barros)]

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.