Hello experts,
I met two problems when I was exploring Elasticsearch.
The situation is that I have several (four, exactly) types in the only index. Each of those types have relation with another. Like a primary key for one table and a foreign key for another. As far as I know, there are some ways to "JOIN" those tables together. So the information in all types can be available in the same query.
There are four tables for sure; first table is the basic information of people, how tall they are, the health condition of them, the education of them etc.; the second table is the address of people, province, city, street etc.; the third table is the properties that people own, how many houses, when did they buy etc.; the fourth table is about the company that people work for, where is the company, which department etc. The size of the data is tens of millions of rows.The primary key (people ID) is in the first table (NOT NULL, UNIQUE). The other three tables have foreign key (people ID, just NOT NULL). The query comes with different combination of random number of fields of all tables every time. For example, "people's height bigger than 180cm, people's salary lower than 2000 per year and the number of houses that people own is more than 3".
Like this one "https://www.elastic.co/guide/en/elasticsearch/guide/current/application-joins.html", this is kind of using code to loop all rows in one type and insert information from another into that one.
Like this one "https://www.elastic.co/guide/en/elasticsearch/guide/current/denormalization.html", this is to get advantage of speed of query at the cost of redundant.
Like this one "https://www.elastic.co/guide/en/elasticsearch/guide/current/parent-child.html", this is to arrange those tables into a parent and kid relationship.
Like this one "https://www.elastic.co/guide/en/elasticsearch/guide/current/nested-mapping.html", this is to insert object A into object B.
Cause time is tight, I can't really afford to try each of those methods to see which one suits my situation best. So please, experts of Elasticsearch, can you kindly tell me the best choice? As far as I know, the "inner object" and "nested" methods are not suitable for me.
The second problem is that as I "JOIN" all tables, how can I arrange the order of the results according to the "_score", which is the key feature to list the results when query from one table? The only method that comes to my mind is to add the "_score" from each table together. It is kind of lame, isn't it?
Hope to receive replies from you talented people!
Feiran