MYSQL把两个字段组合成对象数组

Question

如图的u1和u2 用MYSQL查出来跟下方的格式一样的有办法吗 纯SQL不行的话可以加JS函数

Answer 1

mysql有GROUP_CONCAT

select id,name,CONCAT('[',temptb.tag,']') as tag from u1 left join(
SELECT
            u1id,GROUP_CONCAT('{"id":',u2.id,',"tag":"',u2.tag,'"}') as tag
        FROM
            u2 
group by u1id)temptb on temptb.u1id=u1.id