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

我希望在 MySQL 中只使用普通的 MySQL 命令将结果表转换为 JSON 数组。例如，查询

SELECT name, phone FROM person;


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

预期的 JSON 输出将是

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

在普通的 MySQL 中有办法做到这一点吗？

编辑:

有一些答案如何做到这一点，例如 MySQL 和 PHP，但我无法找到纯 MySQL 解决方案。

155188 次浏览
小开

You can use json_object to get rows as JSON objects.

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.

小开
最佳答案

New solution:

Built using Your great comments, thanks!

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

Old solution:

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;
小开

There are two "group by" functions for JSON called json_arrayagg, json_objectagg.

This problem can be solved with:

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

This requires MySQL 5.7+.

小开

If you're stuck on MySQL 5.6 like me, try this:

SELECT
CONCAT(
'[',
GROUP_CONCAT(
CONCAT(
'{"name":"', name, '"',
',"phone":"', phone, '"}'
)
),
']'
) as json
FROM person
小开

If you need a nested JSON Array Object, you can join JSON_OBJECT with json_arrayagg as below:

{
"nome": "Moon",
"resumo": "This is a resume.",
"dt_inicial": "2018-09-01",
"v.dt_final": null,
"data": [
{
"unidade": "unit_1",
"id_unidade": 9310
},
{
"unidade": "unit_2",
"id_unidade": 11290
},
{
"unidade": "unit_3",
"id_unidade": 13544
},
{
"unidade": "unit_4",
"id_unidade": 13608
}
]
}

You can also do it like this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_lst_caso`(
IN `codigo` int,
IN `cod_base` int)
BEGIN


DECLARE json TEXT DEFAULT '';


SELECT JSON_OBJECT(
'nome', v.nome,
'dt_inicial', v.dt_inicial,
'v.dt_final', v.dt_final,
'resumo', v.resumo,
'data', ( select json_arrayagg(json_object(
'id_unidade',`tb_unidades`.`id_unidade`,
'unidade',`tb_unidades`.`unidade`))
from tb_caso_unidade
INNER JOIN tb_unidades ON tb_caso_unidade.cod_unidade = tb_unidades.id_unidade
WHERE tb_caso_unidade.cod_caso = codigo)
) INTO json
FROM v_caso AS v
WHERE v.codigo = codigo and v.cod_base = cod_base;
    

SELECT json;
    

END

提交答案