mysql concat json,如何使用group-concat mysql创建json格式？

How create json format with group-concat mysql?

(I use MySQL)

Example1:

table1:

email | name | phone

-------------------------------------

my1@gmail.com | Ben | 6555333

my2@gmail.com | Tom | 2322452

my2@gmail.com | Dan | 8768768

my1@gmail.com | Joi | 3434356

like syntax code that not give me the format:

select email, group-concat(name,phone) as list from table1

group by email

output that I need:

email | list

------------------------------------------------

my1@gmail.com | {name:"Ben",phone:"6555333"},{name:"Joi",phone:"3434356"}

my2@gmail.com | {name:"Tom",phone:"2322452"},{name:"Dan",phone:"8768768"}

Thanks

解决方案

Try this query -

SELECT

email,

GROUP_CONCAT(CONCAT('{name:"', name, '", phone:"',phone,'"}')) list

FROM

table1

GROUP BY

email;

JSON format result -

+---------------+-------------------------------------------------------------+

| email | list |

+---------------+-------------------------------------------------------------+

| my1@gmail.com | {name:"Ben", phone:"6555333"},{name:"Joi", phone:"3434356"} |

| my2@gmail.com | {name:"Tom", phone:"2322452"},{name:"Dan", phone:"8768768"} |

+---------------+-------------------------------------------------------------+