如何在MySQL中将结果表转换为JSON数组 [英] How to convert result table to JSON array in MySQL

问题描述

我想仅使用简单的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;

这篇关于如何在MySQL中将结果表转换为JSON数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！