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

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

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

SELECT name, phone FROM person;

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

the expected JSON output would be

期望的JSON输出将是

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

Is there way to do that in plain MySQL?

在普通的MySQL中有这样的方法吗?

EDIT:

编辑:

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

有一些解决方法，比如MySQL和PHP，但是我找不到纯粹的MySQL解决方案。

3 个解决方案

#1

You can use json_object to get rows as JSON objects.

您可以使用json_object获取作为JSON对象的行。

SELECT json_object('name', name, 'phone', phone)
FROM person;

This won't put them in an array, or put commas between them. You'll have to do that in the code which is fetching them.

这不会把它们放到数组中，也不会在它们之间加上逗号。你必须在取回它们的代码中这样做。

#2

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

在@Schwern的帮助下，我成功地提出了这个查询，它似乎是有效的!

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

#3

If you're running your queries in console (as you say in one of the comments) then install MySql Shell and you'll be able to output queries' results as json.

如果您正在控制台(如您在其中一条注释中所述)运行查询，那么安装MySql Shell，您将能够将查询的结果输出为json。

#1