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

Question

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

（我使用MySQL）

范例1：

表格1：

email            |    name  |   phone
-------------------------------------
[e-mail]    | Ben      | 6555333
[e-mail]    | Tom      | 2322452
[e-mail]    | Dan      | 8768768
[e-mail]    | Joi      | 3434356

像语法代码那样的格式：

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

我需要的输出：

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

谢谢

Answer 1

试试这个查询-

SELECT
  email,
  GROUP_CONCAT(CONCAT('{name:"', name, '", phone:"',phone,'"}')) list
FROM
  table1
GROUP BY
  email;

JSON格式结果-

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