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

问题描述

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

How create json format with group-concat mysql?

(我使用 MySQL)

(I use MySQL)

示例 1:

表 1:

email            |    name  |   phone
-------------------------------------
my1@gmail.com    | Ben      | 6555333
my2@gmail.com    | Tom      | 2322452
my2@gmail.com    | Dan      | 8768768
my1@gmail.com    | Joi      | 3434356

像没有给我格式的语法代码:

like syntax code that not give me the format:

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

我需要的输出:

email         |    list
------------------------------------------------
my1@gmail.com |  {name:"Ben",phone:"6555333"},{name:"Joi",phone:"3434356"}
my2@gmail.com |  {name:"Tom",phone:"2322452"},{name:"Dan",phone:"8768768"}

谢谢

推荐答案

试试这个查询 -

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

JSON 格式结果 -

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

这篇关于如何使用 group-concat mysql 创建 json 格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！