# Aggregation of data from "many to many" relational database tables

**URL:** <https://discuss.elastic.co/t/aggregation-of-data-from-many-to-many-relational-database-tables/219665>\
**Category:** Elasticsearch\
**Created:** [February 17, 2020, 5:56pm UTC](https://discuss.elastic.co/t/aggregation-of-data-from-many-to-many-relational-database-tables/219665 "2020-02-17T17:56:16Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![tales-info](https://avatars.discourse-cdn.com/v4/letter/t/ecccb3/32.png) [@tales-info](https://discuss.elastic.co/u/tales-info)\
**Post date:** [February 17, 2020, 5:56pm UTC](https://discuss.elastic.co/t/aggregation-of-data-from-many-to-many-relational-database-tables/219665/1 "2020-02-17T17:56:16Z")

</div>

I have the following data from "many to many" tables in a relational database:

```
Data in Relational Database
=======================================================================
Table User
   user max_formation
1: user 1 doctorate degree
2: user 2 master degree
3: user 3 graduate degree
4: user 4 graduate degree

Table Publication
   publication journal_title keywords
1: title of publication 1 journal 1 keyword 1, keyword 2
2: title of publication 2 journal 2 keyword 1, keyword 3
3: title of publication 3 journal 3 keyword 3, keyword 4

Data entered in the elasticsearch
=======================================================================    
   user max_formation publication journal_title keywords
-------------------------------------------------------------------------------------------------------
1: user 1 doctorate degree title of publication 1 journal 1 keyword 1, keyword 2
2: user 1 doctorate degree title of publication 2 journal 2 keyword 1, keyword 3
3: user 2 master degree title of publication 1 journal 1 keyword 1, keyword 2
4: user 3 graduate degree title of publication 3 journal 3 keyword 3, keyword 4
5: user 4 graduate degree title of publication 1 journal 1 keyword 1, keyword 2

```

Through this data, I would like to make aggregations (facets), but I don't know how to solve the following problem: As I entered the data in elasticsearch, user and publication data were duplicated. That is, there would have to be a way to ignore repeated docs for facets of user fields and ignore repeated docs for facets of publications.  
Below is an example of the "expected / obtained" results through the example above.

```
FACETS [item: expected_result/result]
=======================================================================

users
------------------
user 1 : 2/2
user 2 : 1/1
user 3 : 1/1
user 4 : 1/1

max_formation
------------------
doctorate degree : 1/2
master degree : 1/1
graduate degree : 2/2

journal_title
------------------
journal 1 : 1/3
journal 2 : 1/1
journal 3 : 1/1

keywords
------------------
keyword 1 : 2/4
keyword 2 : 1/3
keyword 3 : 2/2
keyword 4 : 1/1

```

Is there any way to generate these facets in elasticsearch with this data? If so, I would like your help, I have already researched a lot and haven't figured out how to do it. Otherwise, how can I model my data in this problem to obtain the expected results? I appreciate the attention.

---

<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:** [March 16, 2020, 6:04pm UTC](https://discuss.elastic.co/t/aggregation-of-data-from-many-to-many-relational-database-tables/219665/2 "2020-03-16T18:04:35Z")

</div>

Closed on March 16, 2020, 6:04pm UTC

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