When to use nested object vs parent child relation

Hi Team,

I am migrating my relation database table to elastic search. I am stuck in making mapping of the document. I dont figure out which thing i use . Whether i should go to nested object or parent child relation.
I am giving some hints about my table.
We have multiple table which are joined to each other. For example

Table 1------one to many---> Table 2
Table 2 ---> One to one ----> Table 3
Table 3 ----> one to many -----> Table 4
Table 4-------> one to one ----> Table 5

If i am using parent child relationship, then in that case, if i need to load all the object by providing the table 1 id .It would be very difficult.

If i am using nested object, then it would be tightly coupled.

What i trying to think is :
merge two table i.e Table 1 and Table 2 and table 2 would be the nested object .
merge two table i.e Table 3 and Table 4 , Table 4 content would be nested object.

and i would make parent child relation between inner object of table 2 present in table 1 object with Table 3
PUT /my_index
"mappings": {
"properties": {
"table2Rel": {
"type": "nested",
"properties": {
"rel_type": { "type": "keyword" },
"table2_id": { "type": "integer" },
"eff_start_time": { "type": "date" },
"eff_end_time": { "type": "date" },
"join_field" : {
"table2Rel" : "table3"
This mapping would give me error , it stated that you cannot put join_field in inner object.

Please provide me suggestion how can i migrate the tables

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