How create json format with group-concat mysql?

Question

How create json format with group-concat mysql?

(I use MySQL)

Example1:

table1:

email            |    name  |   phone ------------------------------------- [email protected]    | Ben      | 6555333 [email protected]    | Tom      | 2322452 [email protected]    | Dan      | 8768768 [email protected]    | 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 ------------------------------------------------ [email protected] |  {name:"Ben",phone:"6555333"},{name:"Joi",phone:"3434356"} [email protected] |  {name:"Tom",phone:"2322452"},{name:"Dan",phone:"8768768"} 

Thanks

Answer 1

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.

Answer 2

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"} | +---------------+-------------------------------------------------------------+