如何使用group-concat mysql创建json格式?
(我使用MySQL)
示例1:
table1:
email | name | phone
-------------------------------------
[email protected] /cdn-cgi/l/email-protection | Ben | 6555333
[email protected] /cdn-cgi/l/email-protection | Tom | 2322452
[email protected] /cdn-cgi/l/email-protection | Dan | 8768768
[email protected] /cdn-cgi/l/email-protection | Joi | 3434356
就像没有给我格式的语法代码:
select email, group-concat(name,phone) as list from table1
group by email
我需要的输出:
email | list
------------------------------------------------
[email protected] /cdn-cgi/l/email-protection | {name:"Ben",phone:"6555333"},{name:"Joi",phone:"3434356"}
[email protected] /cdn-cgi/l/email-protection | {name:"Tom",phone:"2322452"},{name:"Dan",phone:"8768768"}
使用较新版本的 MySQL,您可以使用 JSON_OBJECT 函数来实现所需的结果,如下所示:
GROUP_CONCAT(
JSON_OBJECT(
'name', name,
'phone', phone
)
) AS list
要准备好将 SQL 响应解析为数组:
CONCAT(
'[',
GROUP_CONCAT(
JSON_OBJECT(
'name', name,
'phone', phone
)
),
']'
) AS list
这会给你一个像这样的字符串:[{name: 'ABC', phone: '111'}, {name: 'DEF', phone: '222'}]
可以用 JSON 解析。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)