# Elastic query for multiple level (more than 3) parent child relation

**URL:** <https://discuss.elastic.co/t/elastic-query-for-multiple-level-more-than-3-parent-child-relation/51644>\
**Category:** Elasticsearch\
**Created:** [June 2, 2016, 5:09am UTC](https://discuss.elastic.co/t/elastic-query-for-multiple-level-more-than-3-parent-child-relation/51644 "2016-06-02T05:09:44Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mobasher-NetLinks](https://avatars.discourse-cdn.com/v4/letter/m/c77e96/32.png) [@Mobasher-NetLinks](https://discuss.elastic.co/u/Mobasher-NetLinks)\
**Post date:** [June 2, 2016, 5:09am UTC](https://discuss.elastic.co/t/elastic-query-for-multiple-level-more-than-3-parent-child-relation/51644/1 "2016-06-02T05:09:44Z")

</div>

Suppose I have the following structure:

```
"audit_ssn" : {          
  "properties" : {
    "session_id" : {
      "type" : "string"
     },
     "user":{
        "type" : "nested",
        "properties" : {
          "id": {
             "type" : "string"
          },
          "first_name" : {
             "type" : "string"
          }
        }
     }
  }
},
"audit_trn" : {        
  "_parent" : {
     "type" : "audit_ssn"
  },
  "properties" : {
     "session_id" : {
        "type" : "string"
     },
     "trans_seq_no" : {
        "type" : "string"
     },
     "task_id" : {
        "type" : "string"
     }
  }
},

"audit_table" : {       
  "_parent" : {
     "type" : "audit_trn"
  },
  "properties" : {
     "session_id" : {
        "type" : "string"
     },
     "trans_seq_no" : {
        "type" : "string"
     },
     "table_seq_no" : {
        "type" : "string"
     },
     "table_name" : {
        "type" : "string"
     }
  }
},
"audit_field" : {
   "_parent" : {
     "type" : "audit_table"
   },
   "properties" : {
     "session_id" : {
        "type" : "string"
     },
     "trans_seq_no" : {
        "type" : "string"
     },
     "table_seq_no" : {
        "type" : "string"
     },
     "field_id" : {
        "type" : "string"
     },
     "field_name" : {
        "type" : "string"  
     },
     "old_value" : {
       "type" : "string"
     },
     "new_value" : {
       "type" : "string"
     } 
   }
}

```

I have above structure for audit of my system, in each session, may occur more than one transaction, each transaction may change more than one table, and each table may have more than one field.

In indexing time after `audit_ssn` is indexed, i use from `parent:audit_ssn.id` for index of `audit_tr`, after that i use from `parent:audit_tr.id`, and `routing:audit_ssn.id` for index of `audit_table`, and at the end i use from `parent:audit_table.id`, and `routing:audit_ssn.id` for indexing of `audit_field`,

every thing is ok but when i search for `audit_table` and tell to the query that `has_child` no result is returned.

```
GET my_index/audit_table/_search 
{
   "query": {
      "has_child": {
         "type": "audit_field",
         "query": {
            "match_all": {}
         }
      }
   }  
}

```

What is wrong with me in index of fourth level child ? and any idea about this structure, and alternate ways.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [June 2, 2016, 7:45am UTC](https://discuss.elastic.co/t/elastic-query-for-multiple-level-more-than-3-parent-child-relation/51644/2 "2016-06-02T07:45:14Z")

</div>

Parent-child relationships can be quite useful when you have a parent that changes relatively frequently and you want to avoid updating large number of children. In this case it seems most entities are static and will not change, which makes me suspect you are using parent-child relationships trying to replicate a relational data model. Have you considered denormalising your data model in order to simplify it? This would allow you to alter your document structure to fit how you want to search it without having to use potentially more costly parent-child queries.

---

<div class="post-metadata">

**Author:** ![Mobasher-NetLinks](https://avatars.discourse-cdn.com/v4/letter/m/c77e96/32.png) [@Mobasher-NetLinks](https://discuss.elastic.co/u/Mobasher-NetLinks)\
**Post date:** [June 4, 2016, 4:40am UTC](https://discuss.elastic.co/t/elastic-query-for-multiple-level-more-than-3-parent-child-relation/51644/3 "2016-06-04T04:40:23Z")

</div>

Thanks! I will try to do it with denormalising.  
But I don't know why all the childs after 3rd level can't be accessed by `has_child` query ?

If i run this query, no result returned, but for all other types (`audit_ssn,audit_trn,`and `audit_table` ) it works.  
`GET promote_kmp/audit_table/_search { "query": { "has_child": { "type": "audit_field", "query": { "match_all": {} } } } }`

---

<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:** [July 5, 2017, 10:46pm UTC](https://discuss.elastic.co/t/elastic-query-for-multiple-level-more-than-3-parent-child-relation/51644/4 "2017-07-05T22:46:15Z")

</div>