# Kibana - joining data tables on specific fields

**URL:** <https://discuss.elastic.co/t/kibana-joining-data-tables-on-specific-fields/205262>\
**Category:** Kibana\
**Created:** [October 25, 2019, 1:16pm UTC](https://discuss.elastic.co/t/kibana-joining-data-tables-on-specific-fields/205262 "2019-10-25T13:16:51Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![pbsf](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pbsf/32/56616_2.png) [@pbsf](https://discuss.elastic.co/u/pbsf)\
**Post date:** [October 25, 2019, 1:16pm UTC](https://discuss.elastic.co/t/kibana-joining-data-tables-on-specific-fields/205262/1 "2019-10-25T13:16:51Z")

</div>

I have two metrics on Kibana that share a field.

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

```

```auto
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

---

<div class="post-metadata">

**Author:** ![nickpeihl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nickpeihl/32/112622_2.png) [@nickpeihl](https://discuss.elastic.co/u/nickpeihl)\
**Post date:** [October 25, 2019, 4:36pm UTC](https://discuss.elastic.co/t/kibana-joining-data-tables-on-specific-fields/205262/2 "2019-10-25T16:36:11Z")

</div>

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)](https://us1.discourse-cdn.com/elastic/original/3X/3/6/365e2f01151be47a37fe196accf6af3344149934.png)

---

<div class="post-metadata">

**Author:** ![pbsf](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pbsf/32/56616_2.png) [@pbsf](https://discuss.elastic.co/u/pbsf)\
**Post date:** [October 25, 2019, 5:24pm UTC](https://discuss.elastic.co/t/kibana-joining-data-tables-on-specific-fields/205262/3 "2019-10-25T17:24:07Z")

</div>

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](https://us1.discourse-cdn.com/elastic/original/3X/0/e/0e4802188959b872369b0b49410584b603abe88e.png)

 ![netamount%20-%20Copia](https://us1.discourse-cdn.com/elastic/original/3X/f/3/f384032bd0c3f8c9c8b6f49cd40a30c8925da493.png)

Both:

 ![both%20-%20Copia](https://us1.discourse-cdn.com/elastic/original/3X/8/0/803cfde5e6d46a3967c6fe023721a745ce89cf1c.png)

---

<div class="post-metadata">

**Author:** ![nickpeihl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nickpeihl/32/112622_2.png) [@nickpeihl](https://discuss.elastic.co/u/nickpeihl)\
**Post date:** [October 25, 2019, 8:35pm UTC](https://discuss.elastic.co/t/kibana-joining-data-tables-on-specific-fields/205262/4 "2019-10-25T20:35:02Z")

</div>

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)](https://us1.discourse-cdn.com/elastic/original/3X/6/e/6e191a62ea1483c8ba579bea48354ca2e259517f.png)

---

<div class="post-metadata">

**Author:** ![pbsf](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pbsf/32/56616_2.png) [@pbsf](https://discuss.elastic.co/u/pbsf)\
**Post date:** [October 25, 2019, 8:59pm UTC](https://discuss.elastic.co/t/kibana-joining-data-tables-on-specific-fields/205262/5 "2019-10-25T20:59:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 22, 2019, 8:59pm UTC](https://discuss.elastic.co/t/kibana-joining-data-tables-on-specific-fields/205262/6 "2019-11-22T20:59:57Z")

</div>

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