How create json format with group-concat mysql?

With the newer versions of MySQL, you can use JSON_OBJECT function to achieve the desired result, like so:

GROUP_CONCAT(  JSON_OBJECT(    'name', name,    'phone', phone  )) AS list

To get the SQL response ready to be parsed as an array:

CONCAT(  '[',  GROUP_CONCAT(    JSON_OBJECT(      'name', name,      'phone', phone    )  ),  ']') AS list

This will give you a string like: [{name: 'ABC', phone: '111'}, {name: 'DEF', phone: '222'}] which can be JSON parsed. Hope this helps.

Try this query -

SELECT  email,  GROUP_CONCAT(CONCAT('{name:"', name, '", phone:"',phone,'"}')) listFROM  table1GROUP 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"} |+---------------+-------------------------------------------------------------+

I hope this finds the right eyes.

You can use:

For arrays (documentation):

JSON_ARRAYAGG(col_or_expr) as ...

For objects (documentation):

JSON_OBJECTAGG(key, value) as ...