第二次更新

我不喜欢这样，感觉太像蛮力"了，我想还有一种更优雅的方式...

Second Update

I don't like this it feels too much like "brute force", and I guess there is a more elegant way ...

select concat('{ "users": [', group_concat(json.hobbies), '] }') as hobbies
from
(
    select concat('{"firstname": "',u.firstname,'", "hobbies": [', group_concat(json_object('id',h.id,'name',h.hobby),''), ']}') hobbies
    from users u
        left join hobbies h on u.id = h.user
    group by u.id
    order by u.firstname
) as json

输出

{ "users": [{"firstname": "Felix", "hobbies": [{"id": 1, "name": "cooking"},{"id": 2, "name": "cat"}]},{"firstname": "Michael", "hobbies": [{"id": 3, "name": "piano"}]},{"firstname": "Tobias", "hobbies": [{"id": null, "name": null}]}] }

---

首次更新

---

First Update

我已经失去了使用MySQL JSON的能力(目前).我能得到的最接近的是以下内容，我猜可能仍然有用:

I've lost my battle with MySQL JSON (for now). The closest I could get was the following, which I guess might still be of some use:

select u.firstname, group_concat(json_object('id',h.id,'name',h.hobby),'') hobbies
from users u
left join hobbies h on u.id = h.user
group by u.id
order by u.firstname

输出

+-----------+-------------------------------------------------------+
| firstname | hobbies                                               | 
+-----------+-------------------------------------------------------+
| Felix     | {"id": 1, "name": "cooking"},{"id": 2, "name": "cat"} |
| Michael   | {"id": 3, "name": "piano"}                            |
| Tobias    | {"id": null, "name": null}                            | 
+-----------+-------------------------------------------------------+    

---

原始答案

---

Original Answer

不确定JSON，对于SQL端而言是否足够?

Not sure about JSON, will this suffice for the SQL side?

select
    u.id,
    u.firstname,
    group_concat(h.hobby ORDER BY h.ID SEPARATOR ',') as hobbies
from
    my_users u
    LEFT JOIN hobbies h ON
    u.ID = h.user
group by
    u.id,
    u.firstname

输出

+----+-----------+-------------+
| id | firstname |   hobbies   |
+----+-----------+-------------+    
|  1 | Felix     | cooking,cat |
|  2 | Michael   | piano       |
|  3 | Tobias    | NULL        |
+----+-----------+-------------+