mysql - 如何将 mysql 查询 "group by statement"结果转换为 json 结构？

Question

我有一个带有 group by 语句的查询，我想将该数据转换为适当的 json 对象

查询:

SELECT c.name, count(u.id) 
FROM users AS u 
INNER JOIN contries AS c ON u.country_id = c.id 
GROUP BY c.id

预期结果:

{canada: {count: 1565}, colombia: {count:15645612}}

Answer 1

您可以添加一个聚合级别并使用 json_objectagg() (从 MySQL 5.7 开始可用):

select json_objectagg(name, js_count) js
from (
    select c.name, json_object('count', count(u.id)) js_count
    from users as u 
    inner join contries as c on u.country_id = c.id 
    group by c.id, c.name
) t