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.