如何在MySQL中将结果表转换为JSON数组

问题描述：

我想仅使用简单的MySQL命令将结果表转换为MySQL中的JSON数组.例如查询

I'd like to convert result table to JSON array in MySQL using preferably only plain MySQL commands. For example with query

SELECT name, phone FROM person;

| name | phone |
| Jack | 12345 |
| John | 23455 |

预期的JSON输出为

[
  {
    "name": "Jack",
    "phone": 12345
  },
  {
    "name": "John",
    "phone": 23455
  }
]

有没有办法在普通的MySQL中做到这一点?

Is there way to do that in plain MySQL?

有一些答案如何使用例如 MySQL和PHP ，但我找不到纯MySQL解决方案.

There are some answers how to do this with e.g. MySQL and PHP, but I couldn't find pure MySQL solution.

答

新解决方案:

使用您的精彩评论来构建，谢谢！

Built using Your great comments, thanks!

SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'phone', phone)) from Person;

旧解决方案:

Old solution:

在@Schwern的帮助下，我设法提出了这个查询，该查询似乎起作用了！

With help from @Schwern I managed to put up this query, which seems to work!

SELECT CONCAT(
    '[', 
    GROUP_CONCAT(JSON_OBJECT('name', name, 'phone', phone)),
    ']'
) 
FROM person;