如何使用 group-concat mysql 创建 json 格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12511933/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How create json format with group-concat mysql?
提问by Yosef
How create json format with group-concat mysql?
如何使用 group-concat mysql 创建 json 格式？
(I use MySQL)
（我使用 MySQL）
Example1:
示例 1：
table1:
表格1：
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
谢谢
回答by Devart
Try this query -
试试这个查询 -
SELECT
email,
GROUP_CONCAT(CONCAT('{name:"', name, '", phone:"',phone,'"}')) list
FROM
table1
GROUP BY
email;
JSON format result -
JSON 格式结果 -
+---------------+-------------------------------------------------------------+
| email | list |
+---------------+-------------------------------------------------------------+
| [email protected] | {name:"Ben", phone:"6555333"},{name:"Joi", phone:"3434356"} |
| [email protected] | {name:"Tom", phone:"2322452"},{name:"Dan", phone:"8768768"} |
+---------------+-------------------------------------------------------------+
回答by myusuf
With the newer versions of MySQL, you can use JSON_OBJECT function to achieve the desired result, like so:
对于较新版本的 MySQL，您可以使用 JSON_OBJECT 函数来实现所需的结果，如下所示：
GROUP_CONCAT(
JSON_OBJECT(
'name', name,
'phone', phone
)
) AS list
To get the SQL response ready to be parsed as an array:
要准备好将 SQL 响应解析为数组：
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.
这将为您提供一个字符串，例如：[{name: 'ABC', phone: '111'}, {name: 'DEF', phone: '222'}]可以解析为 JSON。希望这可以帮助。
回答by alexkorn
Devart's answer above is great, but K2xL's question is valid. The answer I found was to hexadecimal-encode the name column using HEX(), which ensures that it will create valid JSON. Then in the application, convert the hexadecimal back into the string.
Devart 上面的回答很好，但 K2xL 的问题是有效的。我找到的答案是使用 HEX() 对 name 列进行十六进制编码，以确保它将创建有效的 JSON。然后在应用程序中，将十六进制转换回字符串。
(Sorry for the self-promotion, but) I wrote a little blog post about this with a little more detail: http://www.alexkorn.com/blog/2015/05/hand-rolling-valid-json-in-mysql-using-group_concat/
（抱歉自我宣传，但是）我写了一篇关于此的博客文章，并提供了更多细节：http: //www.alexkorn.com/blog/2015/05/hand-rolling-valid-json-in- mysql-using-group_concat/
[Edit for Oriol] Here's an example:
[为 Oriol 编辑] 这是一个例子：
SELECT email,
CONCAT(
'[',
COALESCE(
GROUP_CONCAT(
CONCAT(
'{',
'\"name\": \"', HEX(name), '\", ',
'\"phone\": \"', HEX(phone), '\"',
'}')
ORDER BY name ASC
SEPARATOR ','),
''),
']') AS bData
FROM table
GROUP BY email
Also note I've added a COALESCE in case there are no items for that email.
另请注意，我添加了 COALESCE，以防该电子邮件没有项目。
回答by Jeffrey the Giraffe
I hope this finds the right eyes.
我希望这能找到合适的眼睛。
You can use:
您可以使用：
For arrays(documentation):
对于数组（文档）：
JSON_ARRAYAGG(col_or_expr) as ...
For objects(documentation):
对于对象（文档）：
JSON_OBJECTAGG(key, value) as ...
回答by Madacol
For Mysql 5.7.22+
对于 MySQL 5.7.22+
SELECT
email,
JSON_ARRAYAGG(
JSON_OBJECT(
'name', name,
'phone', phone
)
) AS list
FROM table1
GROUP BY email;
Result:
结果：
+---------------+-------------------------------------------------------------------+
| email | list |
+---------------+-------------------------------------------------------------------+
| [email protected] | [{"name":"Ben", "phone":6555333},{"name":"Joi", "phone":3434356}] |
| [email protected] | [{"name":"Tom", "phone":2322452},{"name":"Dan", "phone":8768768}] |
+---------------+-------------------------------------------------------------------+
The only difference is that column listis now Json-valid, so you can parse directly as Json
唯一的区别是列list现在是Json-valid，因此您可以直接解析为Json
回答by Jonathan Harford
Going off of @Devart's answer... if the field contains linebreaks or double quotation marks, the result will not be valid JSON.
离开@Devart 的回答......如果该字段包含换行符或双引号，则结果将不是有效的 JSON。
So, if we know the "phone" field occasionally contains double-quotes and linebreaks, our SQL would look like:
因此，如果我们知道“电话”字段偶尔包含双引号和换行符，我们的 SQL 将如下所示：
SELECT
email,
CONCAT(
'[',
GROUP_CONCAT(CONCAT(
'{name:"',
name,
'", phone:"',
REPLACE(REPLACE(phone, '"', '\\"'),'\n','\\n'),
'"}'
)),
']'
) AS list
FROM table1 GROUP BY email;
If Ben phone has a quote in the middle of it, and Joi's has a newline, the SQL would give (valid JSON) results like:
如果 Ben 电话中间有引号，而 Joi 有一个换行符，则 SQL 将给出（有效的 JSON）结果，例如：
[{name:"Ben", phone:"655\"5333"},{name:"Joi", phone:"343\n4356"}]
回答by Sundar G
Use like this
像这样使用
SELECT email,concat('{name:"',ur_name_column,'",phone:"',ur_phone_column,'"}') as list FROM table1 GROUP BY email;
Cheers
干杯
