title: SQL query to return nested array of objects in JSON for SQLite
body: <p>I have 2 simple tables in a SQLite db and a nodejs, express api endpoint that should get results by student and have the subjects as a nested array of objects.</p>
  <p>Tables:
  Student(id, name) and Subject(id, name, studentId)</p>
  <p>This is what I need to result to look like:</p>
  <pre><code>{
      &quot;id&quot;: 1,
      &quot;name&quot;: &quot;Student name&quot;,
      &quot;subjects&quot;: 
      [{
          &quot;id&quot;: 1,
          &quot;name&quot;: &quot;Subject 1&quot;
      },
      {
          &quot;id&quot;: 2,
          &quot;name&quot;: &quot;Subject 2&quot;
      }]
  }
  </code></pre>
  <p>How can I write a query to get this result?</p>