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

Answer 1

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)