# How to aggregate data across multiple fields?

**URL:** <https://discuss.elastic.co/t/how-to-aggregate-data-across-multiple-fields/318943>\
**Category:** Kibana\
**Created:** [November 15, 2022, 9:20am UTC](https://discuss.elastic.co/t/how-to-aggregate-data-across-multiple-fields/318943 "2022-11-15T09:20:06Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![tinrik](https://avatars.discourse-cdn.com/v4/letter/t/d78d45/32.png) [@tinrik](https://discuss.elastic.co/u/tinrik)\
**Post date:** [November 15, 2022, 9:20am UTC](https://discuss.elastic.co/t/how-to-aggregate-data-across-multiple-fields/318943/1 "2022-11-15T09:20:06Z")

</div>

Hi!

I'm fairly new to Kibana. I'm trying to perform data aggregation across multiple fields, but don't seem to be approaching the problem the right way. Example use case:

I have data about house prices. E.g. house "a" costs 10, house "b" costs 20, house "c" costs 5, house "d" costs 50. Example data point:

```auto
{
  "house": "a",
  "cost": 10,
}

```

I also have data about ownership, e.g. person "p1" owns houses "[a, b]", person "p2" owns houses "[c, d]". Example data point:

```auto
{
  person: "p1",
  houses: ["a", "b"],
}

```

Goal: I want to visualize a pie chart that shows the aggregated house cost for each person. I.e. "p1" should get a value of 10 + 20 = 30, and "p2" should get a value of 5 + 50 = 55.

This currently doesn't work with Pie Chart visualization because there's no link between "person" and "cost".

I don't want to pre-calculate this aggregation before pushing the data to Kibana - my hope is that Kibana can aggregate the data on the fly.

- Can I solve this problem in Kibana, or do I need to pre-compute these values before pushing the data?
- How can I "link" the data from the 2 different types of datapoints? Is there a better way to lay out the data?

Thanks for the help!

---

<div class="post-metadata">

**Author:** ![devon.thomson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/devon.thomson/32/85468_2.png) [@devon.thomson](https://discuss.elastic.co/u/devon.thomson)\
**Post date:** [November 15, 2022, 9:51pm UTC](https://discuss.elastic.co/t/how-to-aggregate-data-across-multiple-fields/318943/2 "2022-11-15T21:51:04Z")

</div>

This seems like normalized data. Unfortunately, Elasticsearch doesn't currently support joins which you would normally use to do something like this in a normalized context. Thankfully, you can get pretty far in ES, by pre-joining or _denormalizing_ your data at ingestion time.

That is probably not the answer that you'd like to hear, but once the data is denormalized, the whole power of [elastic query DSL](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl.html) will be available to build out any aggregation you want.

So this example data you've written out would look a little different denormalized. Since the way you've described it looks like a one to many relationship - one person owns many houses - each document would be a house, and the person would appear in multiple rows:

```nohighlight
{ "person": "p1", " house": "a", "price": 10 },
{ "person": "p1", " house": "b", "price": 20 },
{ "person": "p2", " house": "c", "price": 5 },
{ "person": "p2", " house": "d", "price": 50 }

```

At ingestion time, you'd also have to think through complicated situations, for instance: when one house is owned by multiple people, is the cost of the house shared or pro-rated between each person?

After the data is denormalized, the aggregation to get the data you're looking for becomes easy and extremely efficient. You would use a [terms](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html) aggregation, with a [sum](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-sum-aggregation.html) aggregation inside.

---

<div class="post-metadata">

**Author:** ![tinrik](https://avatars.discourse-cdn.com/v4/letter/t/d78d45/32.png) [@tinrik](https://discuss.elastic.co/u/tinrik)\
**Post date:** [November 16, 2022, 11:05am UTC](https://discuss.elastic.co/t/how-to-aggregate-data-across-multiple-fields/318943/3 "2022-11-16T11:05:07Z")

</div>

Thanks for the detailed explanation! I see what you mean now by normalized data.

How would the data look like if there's a N:M relationship between house and person? I.e. multiple persons can own the same house.

---

<div class="post-metadata">

**Author:** ![tinrik](https://avatars.discourse-cdn.com/v4/letter/t/d78d45/32.png) [@tinrik](https://discuss.elastic.co/u/tinrik)\
**Post date:** [November 16, 2022, 12:41pm UTC](https://discuss.elastic.co/t/how-to-aggregate-data-across-multiple-fields/318943/4 "2022-11-16T12:41:32Z")

</div>

I suppose it's just about duplicating the data, i.e.

```auto
{ "person": "p1", "house": "a", "price": 10},
{ "person": "p2", "house": "a", "price": 10}

```

Would that make sense? Sounds like the amount of data can grow exponentially if more parameters are introduced though, e.g. size of the house, number of rooms, etc.

---

<div class="post-metadata">

**Author:** ![devon.thomson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/devon.thomson/32/85468_2.png) [@devon.thomson](https://discuss.elastic.co/u/devon.thomson)\
**Post date:** [November 16, 2022, 3:14pm UTC](https://discuss.elastic.co/t/how-to-aggregate-data-across-multiple-fields/318943/5 "2022-11-16T15:14:59Z")

</div>

What happens when the data has a `many to many` relationship is something you'd have to think through on a case by case basis.

Duplicating the data like you've suggested is a good approach in general, but might not be truthful, because if you aggregate house data, now all of a sudden `house a` looks like it's worth 20, when it's really worth 10.

If you were to take the proration approach, you'd divide the house price by the number of people who own it, so the data would look like:

```nohighlight
{ "person": "p1", "house": "a", "price": 5 },
{ "person": "p2", "house": "a", "price": 5 }

```

This keeps the price of the house accurate, and might be a better reflection of reality depending on the data. Likely a house doesn't double in price as soon as two people share ownership over it.

Unfortunately, the data does grow exponentially with each many-to-many relationship you **join** into the data. That is a downside of the denormalization process, but the examples you've given wouldn't actually create such a data explosion. If you introduced those two fields the data would look like:

```nohighlight
{ "person": "p1", " house": "a", "price": 10 , "roomCount": 5, "sizeInSqFt": 2500 },
{ "person": "p1", " house": "b", "price": 20, "roomCount": 2, "sizeInSqFt": 4500 },
{ "person": "p2", " house": "c", "price": 5, "roomCount": 1, "sizeInSqFt": 100 },
{ "person": "p2", " house": "d", "price": 50 , "roomCount": 5, "sizeInSqFt": 2000 }

```

Notice how the number of rows is the same, because the _house_ is already the most granular level of the data. When you would get an explosion of data is if you wanted to **join** in a third data set with a many-to-many relationship to the original tables.

You can think of denormalization as a trade off. Yes, you are creating more documents, and duplicating your data, but you are also making it _way easier_ for the computer to search through it and do math on it.

I'd recommend [more reading](https://rubygarage.org/blog/database-denormalization-with-examples) on the subject, as it's honestly fascinating! I can speak from experience that if the denormalization process is done right it can result in an extremely fast and powerful experience in Elasticsearch.

---

<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:** [December 14, 2022, 2:03pm UTC](https://discuss.elastic.co/t/how-to-aggregate-data-across-multiple-fields/318943/6 "2022-12-14T14:03:08Z")

</div>



---

<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:** [January 11, 2023, 2:03pm UTC](https://discuss.elastic.co/t/how-to-aggregate-data-across-multiple-fields/318943/7 "2023-01-11T14:03:13Z")

</div>

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