Single mysql select on join tables to a json hierarchical fragment, how?

Question

What would be the best and most elegant way to retrieve from a single MySQL select query all the information from two joined tables, but in a hierarchical way?

I have those two tables:

-----------------         ------------------
| Table COMPANY |         | Table EMPLOYEE |
-----------------         ------------------
| id            |         | id             |
| companyName   |         | companyId      |
-----------------         | employeeName   |
                          ------------------

(for each company, many employees)

and I want to output the following hierarchical JSON fragment:

[
    {"id": 1,
     "companyName": "Company A",
     "employees": [
         {"id": 1, "employeeName": "Employee 1"},
         {"id": 2, "employeeName": "Employee 2"}
    ]},
    {"id": 2,
     "companyName": "Company B",
     "employees": [
         {"id": 3, "employeeName": "Employee 3"},
         {"id": 4, "employeeName": "Employee 4"}
    ]}
]

"Solution" 1:

Do a full select on the joined tables, and write some code after to create the json fragment:

select * from company, employee where employee.companyId = company.id;

Problem: I'm left with a lot of ugly foreach-type code to create the json fragment

"Solution" 2:

Group by company all employees in a json string:

select company.id, company.name,
   concat('[',
      group_concat('{"id": ', employee.id, ',
         "employeeName": "', employee.employeeName,'"}'), ']') as employees
from company, employee where company.id = employee.companyId
group by company.id

Here, "employees" is already a json fragment, I only have to jsonify the company.id and name. But to build the json fragment in the mysql seems very bad practice.

Any insight, or other solution?

Many thanks

"I'm left with a lot of ugly foreach-type code to create the json fragment" Which programming language are you using to convert the MySQL result to JSON ? — Carlos Quijano, Oct 26 '12 at 03:16
I'm using java, with Apache Common DbUtils for database access and Google Gson for JSON. — Ludo Ma Non Troppo, Oct 26 '12 at 10:10

score 2 · Accepted Answer · answered Oct 30 '12 at 11:55

I would probably use a query like your solution 1, just add on a " sort by company.id" and then do one loop through the table, building up a data structure, for each row, if the company is the same as the previous one, push the employee data into the company, else, define the new company and start using that for pushing in the employee data.

Like Carlos Quijano I do not see any foreach mess here. (but having the sort by makes it much easier to handle)

score 1 · Answer 2 · answered Nov 20 '12 at 01:15

Because you are required to run a single MySQL query and don't want to mess with ugly for-each-type code then you should probably return a no-hierarchical JSON result, you can put some elegance to your code by serializing the query results to a collection with DbUtils query.

QueryRunner run = new QueryRunner(dataSource);

// Use the BeanListHandler implementation to convert all
// ResultSet rows into a List of Employees JavaBeans.
ResultSetHandler<List<Employee>> handler = new BeanListHandler<Employee>(Employee.class);

// Execute the SQL statement and return the results in a List of
// Person objects generated by the BeanListHandler.
List<Employee> employees = run.query("SELECT * FROM EMPLOYEE ORDER BY COMPANY_ID", handler);

But if you are required to generate a hierarchical JSON result, I agree with MortenSickel answer below, reduce that collection with a typed foreach and finally JSON encode with GSON, I think there's absolutely nothing wrong with that.

Single mysql select on join tables to a json hierarchical fragment, how?

"Solution" 1:

"Solution" 2:

2 Answers2