SQL query with join to get nested array of objects

Question

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.

{
    "id": 1,
    "first": "John",
    "roles": [ // Expectation -> array of objects
        {
            "id": 1,
            "name": "admin"
        },
        {
            "id": 2,
            "name": "accounts"
        }
    ]
}

user

+----+-------+
| id | first |
+----+-------+
|  1 | John  |
|  2 | Jane  |
+----+-------+

role

+----+----------+
| id |   name   |
+----+----------+
|  1 | admin    |
|  2 | accounts |
|  3 | sales    |
+----+----------+

user_role

+---------+---------+
| user_id | role_id |
+---------+---------+
|       1 |       1 |
|       1 |       2 |
|       2 |       2 |
|       2 |       3 |
+---------+---------+

---

Attempt 01

In a naive approach I'd run two sql queries in my nodejs code, with the help of multipleStatements:true in connection string. Info.

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);
    });
};

Problem: 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.

---

Attempt 02

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).

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);
    });
};

Attempt 02 Result

{
    "id": 1,
    "first": "John",
    "roles": [ // array of string
        "admin",
        "accounts"
    ]
}

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.

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.

---

Attempt 03

use role.id instead of role.name in GROUP_CONCAT(role.id), that way you can get hold of some id's and then use another subquery to get associated role names, just thinking...

SQL (doesn't work but just to throw something out there for some thought)

SELECT 
  user.id,  user.first,  
  GROUP_CONCAT(role.id) AS role_ids, // split(",") it in code
  (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;

---

Edit

Based on Amit's answer, I've learned that there's such solution in SQL Server using JSON AUTO. Yes this is something I'm looking for in MySQL.

To articulate precisely.

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.

Answer 1

Came here with a similar question and I'm surprised there's not a typical way of doing this. For what it's worth, here's a helper function I created for these things; nesting arrays from joined table data.

You'll notice it passes the array by reference, and includes options for what you want the parent array key to be, the title of the element that you want to be nested, the child's array key, the values you want to place in that nested array, and any renaming you want to do in the child array. The renaming is important because there might be a field, like, "id", that you need to use a pseudonym for in the query.

/**
 * Takes an array of objects created by a LEFT JOIN and splits it into its parent and
 * child elements, creating a parent/child tree via reference, with each being arrays of arrays.
 *
 * @param $array array The array of joined objects
 * @param $parentKey string The key ID of the parent
 * @param $childTitle string The name of child element to place the child elements below
 * @param $childKey string The key ID of the children
 * @param $childElements array The keys of the remaining elements that should go in the child and out of the parent
 * @param $childKeyRenames array Optional associative array of any child keys that need to be renamed
 */
function nest_arrays(&$array, $parentKey, $childTitle, $childKey, $childElements, $childKeyRenames = []) {
    $newArray = [];
    foreach ($array as $element) {
        // if the parent array hasn't been created yet, create it along with new child key within the array
        if (!isset($newArray[$element[$parentKey]])) {
            $newArray[$element[$parentKey]] = $element;
        } // done creating original parent with proper keys
        // obtain child array
        $childArray = [];
        // if child records are available (not always the case since the left join could return no children)
        // create them
        if (strlen($element[$childKey])) {
            foreach ($childElements as $key) {
                $childArray[$key] = $element[$key];
            }
            $newArray[$element[$parentKey]][$childTitle][] = $childArray;
        } else {
            $newArray[$element[$parentKey]][$childTitle] = [];
        }
        // remove child elements from parent object
        foreach ($newArray[$element[$parentKey]] as $elementKey => $value) {
            if (in_array($elementKey, $childElements)) {
                unset($newArray[$element[$parentKey]][$elementKey]);
            }
        }
    }
    // set collection to new array, re-indexed
    $array = array_values($newArray);
    // rename children if appropriate
    foreach ($childKeyRenames as $previousName => $newName) {
        foreach ($array as $parentKey => $parentArray) {
            foreach ($parentArray[$childTitle] as $childIndex=>$childArray) {
                $array[$parentKey][$childTitle][$childIndex][$newName] = $childArray[$previousName];
                unset($array[$parentKey][$childTitle][$childIndex][$previousName]);
            }
        }
    }
} // end function nest_objects

As an example, imagine you have stories, and each story can can have multiple users attached to it through a relation table. You don't do anything fancy w/ the query; just do the standard join of stories to users. In this case, both my stories and my users tables have an "id", so I used AS user_id in the query to return the user's id as user_id to start.

Then, utilize the nest_arrays function like this:

nest_arrays(
  $stories, 
  'id',
  'users',
  'user_id',
  ['name', 'user_id'], 
  ['user_id' => 'id']
);

Here you can see that the $childKeyRenames will translate user_id back into id in the child array.

This will return a nested array that looks something like this, perfect for sending to the API.

[
  {
    "title": "Hello",
    "transcript": "Hello",
    "users": [
      {
        "id": "9ac429ed-bb8e-4fd9-b02f-1b2a67d06c13",
        "name": "Tester Man"
      }
    ]
  },
  {
    "title": "Goodbye",
    "transcript": "Goodbye",
    "users": [
      {
        "id": "9ac429ed-bb8e-4fd9-b02f-1b2a67d06c13",
        "name": "Tester Man"
      },
      {
        "id": "8ac429ed-bb8e-4fd9-b02f-1b2a67d06c13",
        "name": "Tester Woman"
      }
    ]
  }
]

Hope this helps someone in a similar situation.

Answer 2

Though it is an old question, just thought might help others looking for the same issue. The below script should output the json schema you have been looking for.

SELECT roles, user.* from `user_table` AS user
            INNER JOIN `roles_table` AS roles
            ON user.id=roles.id 

Answer 3

User this Join Query

FOR JSON AUTO will return JSON for your query result

SELECT U.UserID, U.Name, Roles.RoleID, Roles.RoleName  
FROM [dbo].[User] as U 
INNER JOIN [dbo].UserRole as UR ON UR.UserID=U.UserID 
INNER JOIN [dbo].RoleMaster as Roles ON Roles.RoleID=UR.RoleMasterID
FOR JSON AUTO

out put of above query is

[
  {
    "UserID": 1,
    "Name": "XYZ",
    "Roles": [
      {
        "RoleID": 1,
        "RoleName": "Admin"
      }
    ]
  },
  {
    "UserID": 2,
    "Name": "PQR",
    "Roles": [
      {
        "RoleID": 1,
        "RoleName": "Admin"
      },
      {
        "RoleID": 2,
        "RoleName": "User"
      }
    ]
  },
  {
    "UserID": 3,
    "Name": "ABC",
    "Roles": [
      {
        "RoleID": 1,
        "RoleName": "Admin"
      }
    ]
  }
]