title: How to make nested JSON response from database tables
body: <p>I have 2 tables.</p>
  <p><code>DATA</code></p>
  <pre><code>+------+--------+---------------------+------------+
  | ID   | Name   | Desc                | Type       |
  +------+--------+---------------------+------------+
  | 1001 | Motor  | Main motor 50 volt  | Mechanical |
  | 1002 | Nut    | 25 mm dia           | Mechanical |
  | 1003 | Bolt   | Hexa bolt           | Mechanical |
  | 1004 | Engine | 750cc liquid cooled | Mechanical |
  | 1005 | Oil    | 1 liter             | NA         |
  | 1006 | Filter | Airfilter           | NA         |
  | 1007 | AC     | 1000w               | Electrical |
  +------+--------+---------------------+------------+
  </code></pre>
  <p><code>Relationship</code></p>
  <pre><code>+-----------+-----------+----------+
  | Parent_id | Relation  | Child_id |
  +-----------+-----------+----------+
  | 1001      | Accessory | 1002     |
  | 1001      | Accessory | 1003     |
  | 1001      | Service   | 1005     |
  | 1001      | Service   | 1006     |
  | 1004      | Accessory | 1002     |
  | 1004      | Accessory | 1003     |
  | 1004      | Service   | 1005     |
  +-----------+-----------+----------+
  </code></pre>
  <p>I want to provide the below response when user searches for <code>1001</code></p>
  <pre><code>{
      &quot;id&quot;: &quot;1001&quot;,
      &quot;name&quot;: &quot;Motor&quot;,
      &quot;desc&quot;: &quot;Main motor 50 volt&quot;,
      &quot;Accessory&quot;: [{
              &quot;id&quot;: &quot;1002&quot;,
              &quot;name&quot;: &quot;Nut&quot;
          },
          {
              &quot;id&quot;: &quot;1003&quot;,
              &quot;name&quot;: &quot;Bolt&quot;
          }
      ],
      &quot;Service&quot;: [{
              &quot;id&quot;: &quot;1005&quot;,
              &quot;name&quot;: &quot;Oil&quot;
          },
          {
              &quot;id&quot;: &quot;1006&quot;,
              &quot;name&quot;: &quot;Filter&quot;
          }
      ]
  }
  </code></pre>
  <p>Here <code>Accessory</code> and <code>Service</code> are from <code>relationship</code> table column <code>relation</code>. I don't have any idea about spring boot. With help of google, i have done the below code. This is giving me in one level json. But I'm not able to find how to create this nested JSON. Please provide some suggestion.</p>
  <p><strong>Repository:</strong></p>
  <pre class="lang-java prettyprint-override"><code>@Repository
  public interface MyDataRepo extends JpaRepository&lt;Items, String&gt; {
  
      @Query(value=&quot;SELECT D.id,D.name,D.desc,R.relation,R.child_id as childid,DC.name as childname
                    FROM DATA D 
                    JOIN RELATIONSHIP R ON D.ID=R.PARENT_ID 
                    JOIN DATA DC ON DC.id=R.CHILD_ID
                    WHERE D.ID=?1&quot;,nativeQuery=true)
      List&lt;Data&gt; findAllCategory(String id);
  
      public static interface Data {
        String getid();
        String getname();
        String getdesc();
        String getrelation();
        String getchildid();
        String getchildname();
      }
  }
  </code></pre>
  <p><strong>Service:</strong></p>
  <pre class="lang-java prettyprint-override"><code>public List&lt;Data&gt; getMyData(String id) {
      return repo.findAllCategory(id);
  }
  </code></pre>
  <p><strong>Controller:</strong></p>
  <pre class="lang-java prettyprint-override"><code>@GetMapping(&quot;/getData/{id}&quot;)
  public ResponseEntity&lt;List&lt;Data&gt;&gt; retrieveData(@PathVariable String id) {
      List&lt;Data&gt; stud = service.getMyData(id);
      return ResponseEntity.ok().body(stud);
  }
  </code></pre>