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