How create json format with group-concat mysql?

For Mysql 5.7.22+

    SELECT
        email,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'name', name,
                'phone', phone
            )
        ) AS list
    FROM table1
    GROUP BY email;

Result:

+---------------+-------------------------------------------------------------------+
| email         | list                                                              |
+---------------+-------------------------------------------------------------------+
| [email protected] | [{"name":"Ben", "phone":6555333},{"name":"Joi", "phone":3434356}] |
| [email protected] | [{"name":"Tom", "phone":2322452},{"name":"Dan", "phone":8768768}] |
+---------------+-------------------------------------------------------------------+

The only difference is that column list is now Json-valid, so you can parse directly as Json

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 ...

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,'"}')) list
FROM
  table1
GROUP BY
  email;

JSON format result -

+---------------+-------------------------------------------------------------+
| email         | list                                                        |
+---------------+-------------------------------------------------------------+
| [email protected] | {name:"Ben", phone:"6555333"},{name:"Joi", phone:"3434356"} |
| [email protected] | {name:"Tom", phone:"2322452"},{name:"Dan", phone:"8768768"} |
+---------------+-------------------------------------------------------------+