title: How can I retrieve the output of an INNER JOIN in an array?
body: <p>I have this SQL query</p>
  
  <pre><code> '(SELECT “A”.”id" AS “aId”, “A”.”name”, “A”.”updatedAt" AS “aUpdatedDate”, “B”.”name” AS “b.name”, “B”.”status” AS “b.status”,  FROM “A” INNER JOIN “B” ON “A”.”id" = “B”.”a_id” WHERE EXISTS (SELECT “status” FROM “B” WHERE “B”.”a_id” = “A”.”id" AND “B”.”status" IN (‘pending’, ‘failed’))) OFFSET ? LIMIT ?’;
  </code></pre>
  
  <p>This returns the data needed but the problem is I'd love to get the data in this format. This is the expected output:</p>
  
  <pre><code>{
      aid: 1,
      name: 'nameOne',
      aUpdatedDate: '2018-09-10',
      Bs: [
          {
               name: 'b-one',
               status: 'completed'
          },
          {
               name: 'b-two',
               status: 'pending'
          }
      ]
  }
  </code></pre>
  
  <p>but instead the output separates each instance of B which in turns duplicates the A's that are returned. The actual output returned is like this</p>
  
  <pre><code>{
      aid: 1,
      name: 'nameOne',
      aUpdatedDate: '2018-09-10',
      'b.name': 'b-one',
      'b.status': 'completed'
  },
  {
      aid: 1,
      name: 'nameOne',
      aUpdatedDate: '2018-09-10',
      'b.name': 'b-two',
      'b.status': 'pending'
  },
  </code></pre>
  
  <p>For a bit of context, you can check out the <a href="https://stackoverflow.com/questions/53923159/is-there-a-way-to-query-a-table-with-a-where-clause-on-a-related-table-but-still?noredirect=1#comment94688155_53923159">StackOverflow</a> link which explains why I'm using a <code>WHERE EXISTS</code> clause and for a bit of background on the initial problem.</p>