Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL query with join to get nested array of objects

Tags:

sql

node.js

mysql

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, 
  (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.

like image 618
AppDeveloper Avatar asked May 28 '19 05:05

AppDeveloper


People also ask

How do I join a nested query in SQL?

SQL Joins and Subqueries. An SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. A subquery is a query that is nested inside a SELECT , INSERT , UPDATE , or DELETE statement, or inside another subquery.

Can you join on an array SQL?

This is the function to use if you want to concatenate all the values in an array field into one string value. You can specify an optional argument as a separator, and it can be any string. If you do not specify a separator, there will be nothing aded between the values.

Which is better nested query or join?

A general rule is that joins are faster in most cases (99%). The more data tables have, the subqueries are slower. The less data tables have, the subqueries have equivalent speed as joins.

How retrieve data from multiple tables in join?

(INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.


1 Answers

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"
      }
    ]
  }
]
like image 146
Amit Sakare Avatar answered Oct 10 '22 04:10

Amit Sakare