几个小时以来，我一直试图弄清楚这一点。但没有运气。
这有效，但不是我真正想要的
现在怎么样了：
{"text":"My first report","comment":"Great Report","display_name":"Xavier"},
{"text":"My First report","comment":"Do you call this a report?","display_name":"Logan"}
我希望它如何：
{"text":"My first report","comments":[{comment: "Great Report","display_name":"Xavier"}, {comment: "Do you call this a report?","display_name":"Logan"}],
当前设置
Report
ID | User_ID | TEXT |
15 | 3 My first report
Users
ID | DISPLAY_NAME |
1 | Xavier
2 | Logan
3 | Cyclops
现在的情况：
Report_Comments
ID | User_ID | Report_ID | TEXT as comment |
3 | 1 15 Great Report
3 | 2 15 Bad Report
我的代码：
SELECT r.text,
Group_concat(r.user_id) AS User_ID,
Group_concat(u.display_name) AS User_Name,
r.id,
Group_concat(c.text) AS comment
FROM report r
LEFT JOIN users u
ON u.id = r.user_id
LEFT JOIN report_comments c
ON c.report_id = r.id
WHERE r.user_id = :userId
GROUP BY r.id,
r.text
使用JSON_ARRAYAGG和 JSON_OBJECT，我们只需一个连接就可以实现这一点。
重现你的情况：
CREATE TABLE reports (id INT, user_id INT, title VARCHAR(60), PRIMARY KEY (id));
CREATE TABLE users (id INT, name VARCHAR(30), PRIMARY KEY (id));
CREATE TABLE comments (id INT, user_id INT, report_id INT, comment VARCHAR(100), PRIMARY KEY (id));
INSERT INTO users VALUES (1, 'Xavier'), (2, 'Logan'), (3, 'Cyclops');
INSERT INTO reports VALUES (10, 1, 'My First Report');
INSERT INTO comments VALUES (100, 1, 10, 'bad report'), (200, 1, 10, 'good report');
我们现在可以运行一个SELECT连接表的报告和评论，按报告的 id 分组。JSON_OBJECT我们使用该函数创建 JSON 文档。使用JSON_ARRAYAGG，我们聚合为“评论”。
SELECT JSON_OBJECT('text', r.title, 'comments',
JSON_ARRAYAGG(JSON_OBJECT('comment', c.comment, 'name', u.name))) as report_comments
FROM reports AS r JOIN comments c on r.id = c.report_id
JOIN users u on c.user_id = u.id
GROUP BY r.id;
结果：
+-------------------------------------------------------------------------------------------------------------------------------------+
| report_comments |
+-------------------------------------------------------------------------------------------------------------------------------------+
| {"text": "My First Report", "comments": [{"name": "Xavier", "comment": "bad report"}, {"name": "Logan", "comment": "good report"}]} |
+-------------------------------------------------------------------------------------------------------------------------------------+
结果使用 JSON_PRETTY 而\G不是;执行查询：
*************************** 1. row ***************************
report_comments: {
"text": "My First Report",
"comments": [
{
"name": "Xavier",
"comment": "bad report"
},
{
"name": "Logan",
"comment": "good report"
}
]
}
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句