title: Elastic search storing hierarchical data and querying it
body: <p>Let me break down the problem it will take some time.</p>
  
  <p>Consider that you have an entities <code>A, B, C</code> in your system.</p>
  
  <ul>
  <li><code>A</code> is the parent of everything</li>
  <li><code>B</code> is the child of <code>A</code></li>
  <li><code>C</code> can be child of <code>A</code> or <code>B</code>, Please note there are some more entities like <code>D,E,F</code> which are same as <code>C</code>. So lets consider <code>C</code> only for time being</li>
  </ul>
  
  <p>So basically its a tree alike structure like</p>
  
  <pre><code>   ```
       A
      / \
     /   \
    B     C(there are similar elements like D, E, F)
    |
    |
    C
     ```
  </code></pre>
  
  <p>Now we need are using <code>Elastic Search</code> as secondary DB to store this. In the data base the structure is completely different since <code>A, B, C</code> have dynamic fields, so they are different tables and we join them to get data, but from business prospective this is design.</p>
  
  <p>Now when we try to flat it and store in es for under set</p>
  
  <ul>
  <li><p>We have a entity A1 who has 2 children C1 and B1, B1 has further children C2</p>
  
  <h2>    A         B         C</h2>
  
  <p>1   A1       null       null
  2   A1       null       C1
  3   A1        B1        null
  4   A1        B1        C2</p></li>
  </ul>
  
  <p>Now what your can query</p>
  
  <ul>
  <li>use says he wants All columns of A,B,C where value of columns A is <code>A1</code>, so adding some null removing rules we can give him row number 2,3,4</li>
  <li><p>now the problem set , now user says he want all <code>A</code>s where value of A is <code>A1</code> , so basically we will return him all rows 1,2,3,4 or 2,3,4 so we will see values like</p>
  
  <h2>A</h2>
  
  <p>A1
  A1
  A1</p></li>
  </ul>
  
  <p>but logically he should see only one column <code>A1</code> since that is only unique value. As <code>ES</code> doesn't have the ability to <code>group by</code> things.</p>
  
  <p>So how we solved things.</p>
  
  <ul>
  <li>We solved this problem by creating multiple indices and one nested index</li>
  <li>So when we need to group by index we go to nested index and other index work as flat index</li>
  <li>so we have different index, like index for <code>A and B</code>, <code>A or B and C</code> . But we have more elements so it lead to creation of 5 indices.</li>
  <li>As data started increasing its becoming difficult to maintain 5 indices and indexing them from scratch takes too much time.</li>
  </ul>
  
  <p>So to solve this we started to look for other options and we are testing <code>cratedb</code>. But on the first place we are still trying to figure is there any way to do that in ES since need to use many feature of ES as <code>percolation</code>, <code>watcher</code> etc. Any clues on that?</p>
  
  <p>Please also note that we need to apply pagination also. That's why single nested index will not work</p>