title: SQL query with join to get nested array of objects
body: <p>Summary: I'll start with a JSON schema to describe the expectation. Notice the roles with a nested array of objects and I'm looking for a "Smart query" that can fetch it one single query.</p>
  
  <pre><code>{
      "id": 1,
      "first": "John",
      "roles": [ // Expectation -&gt; array of objects
          {
              "id": 1,
              "name": "admin"
          },
          {
              "id": 2,
              "name": "accounts"
          }
      ]
  }
  </code></pre>
  
  <p>user</p>
  
  <pre><code>+----+-------+
  | id | first |
  +----+-------+
  |  1 | John  |
  |  2 | Jane  |
  +----+-------+
  </code></pre>
  
  <p>role</p>
  
  <pre><code>+----+----------+
  | id |   name   |
  +----+----------+
  |  1 | admin    |
  |  2 | accounts |
  |  3 | sales    |
  +----+----------+
  </code></pre>
  
  <p>user_role</p>
  
  <pre><code>+---------+---------+
  | user_id | role_id |
  +---------+---------+
  |       1 |       1 |
  |       1 |       2 |
  |       2 |       2 |
  |       2 |       3 |
  +---------+---------+
  </code></pre>
  
  <hr>
  
  <p>Attempt 01</p>
  
  <p>In a naive approach I'd run two sql queries in my nodejs code, with the help of <code>multipleStatements:true</code> in connection string. <a href="http://www.technicalkeeda.com/nodejs-tutorials/nodejs-mysql-multiple-statement-queries/" rel="noreferrer">Info</a>.</p>
  
  <pre><code>User.getUser = function(id) {
      const sql = "SELECT id, first FROM user WHERE id = ?; \
          SELECT role_id AS id, role.name from user_role \
          INNER JOIN role ON user_role.role_id = role.id WHERE user_id = ?";
  
      db.query(sql, [id, id], function(error, result){
          const data = result[0][0]; // first query result
          data.roles = result[1];  // second query result, join in code.
          console.log(data);
      });
  };
  </code></pre>
  
  <p><strong>Problem</strong>: Above code produces the expected JSON schema but it takes two queries, I was able to narrow it down in a smallest possible unit of code because of multiple statements but I don't have such luxury in other languages like Java or maybe C# for instance, there I've to create two functions and two sql queries. so I'm looking for a single query solution.</p>
  
  <hr>
  
  <p>Attempt 02</p>
  
  <p>In an earlier attempt With the help of SO community, I was able to get close to the following using single query but it can only help to produce the array of string (not array of objects).</p>
  
  <pre><code>User.getUser = function(id) {
      const sql = "SELECT user.id, user.first, GROUP_CONCAT(role.name) AS roles FROM user \
          INNER JOIN user_role ON user.id = user_role.user_id \
          INNER JOIN role ON user_role.role_id = role.id \
          WHERE user.id = ? \
          GROUP BY user.id";
      db.query(sql, id, function (error, result) {
          const data = {
              id: result[0].id, first: result[0].first,
              roles: result[0].roles.split(",") // manual split to create array
          };
          console.log(data);
      });
  };
  </code></pre>
  
  <p>Attempt 02 Result</p>
  
  <pre><code>{
      "id": 1,
      "first": "John",
      "roles": [ // array of string
          "admin",
          "accounts"
      ]
  }
  </code></pre>
  
  <p>it's such a common requirement to produce array of objects so wondering there must be something in SQL that I'm not aware of. Is there a way to better achieve this with the help of an optimum query. </p>
  
  <p>Or let me know that there's no such solution, this is it and this is how it's done in production code out there with two queries.</p>
  
  <hr>
  
  <p><strong>Attempt 03</strong></p>
  
  <p>use <code>role.id</code> instead of <code>role.name</code> in <code>GROUP_CONCAT(role.id)</code>, that way you can get hold of some id's and then use another subquery to get associated role names, just thinking...</p>
  
  <p>SQL (doesn't work but just to throw something out there for some thought)</p>
  
  <pre><code>SELECT 
    user.id,  user.first,  
    GROUP_CONCAT(role.id) AS role_ids, 
    (SELECT id, name FROM role WHERE id IN role_ids) AS roles
  FROM user 
  INNER JOIN user_role ON user.id = user_role.user_id 
  INNER JOIN role ON user_role.role_id = role.id 
  WHERE user.id = 1
  GROUP BY user.id;
  </code></pre>
  
  <hr>
  
  <p><strong>Edit</strong></p>
  
  <p>Based on Amit's answer, I've learned that there's such <a href="https://docs.microsoft.com/en-us/sql/relational-databases/json/format-json-output-automatically-with-auto-mode-sql-server?view=sql-server-2017" rel="noreferrer">solution</a> in SQL Server using <code>JSON AUTO</code>. Yes this is something I'm looking for in MySQL. </p>
  
  <p>To articulate precisely.</p>
  
  <blockquote>
    <p>When you join tables, columns in the first table are generated as
    properties of the root object. Columns in the second table are
    generated as properties of a nested object.</p>
  </blockquote>