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

Question

我想使用仅限纯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解决方案。

- ronkot

---

GROUP_CONCAT和CONCAT的组合 - Paul Spiegel

我可以想到许多有用的情况。例如，从数据库查询一些JSON数据并将其传输到POST请求。 - ronkot

虽然有点晚，但我认为答案应该提到这一点，“json_object”仅适用于MySQL 5.7及更高版本。 - Anusha

@toolmakersteve 干杯；我已经等了两年那个小宝贝了。 - Strawberry

@Strawberry，我目前正在研究的用例是使用针对表B的查询结果更新表A中的JSON列。将数据格式化为JSON使您可以在单个查询中完成此操作。 - Jordan Dodson

Answer 1

新方案：

根据您的精彩评论建立，感谢您！

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

---

旧解决方案:

在 @Schwern 的帮助下，我设法提出了这个查询，它似乎可以工作！

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

- ronkot

---

如果你正在使用mysql客户端，你可以使用--json，这样就可以在任何查询上工作了。 - Schwern

由于group_concat_max_len的限制，输出可能会被截断。在MySQL中，group_concat_max_len是控制GROUP_CONCAT()函数返回结果的最大长度的参数。默认值为1024个字符。如果结果超过了该值，将会被截断。 - Laurent W.

为什么不使用 JSON_ARRAY(JSON_OBJECT('name', name, 'phone', phone)) 呢？ - DarckBlezzer

@DarckBlezzer的提案输出似乎是一个列表集合：[{"name": "Jack", "phone": "12345"}] [{"name": "John", "phone": "23455"}]（参见此示例）。 - ronkot

@GiacomoAlzetta 这是针对MySQL shell，即mysqlsh的，与MySQL客户端不同。https://dev.mysql.com/doc/mysql-shell/8.0/en/mysqlsh.html#option_mysqlsh_json - Schwern

Answer 2

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

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

这不会将它们放入数组中，也不会在它们之间放置逗号。您需要在获取它们的代码中执行此操作。

- Schwern

---

谢谢，已经非常接近了！我还在尝试如何从这些对象构建一个数组。 - ronkot

我遇到了 ERROR 1305 (42000): FUNCTION mydb.JSON_OBJECT does not exist 的错误。我该如何确保这个函数存在？ - Anthony Kong

@AnthonyKong，你使用的是哪个版本？json_object只能在5.7及以上版本中使用。 - Shadow Fiend

我更喜欢这个选项，因为它将每个条目作为自己的行返回。有了这个选项，您可以流式传输结果。 - Sean256

Answer 3

如果你和我一样被困在 MySQL 5.6 上，那么可以试试以下方法：

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

- Brendan

---

感谢提供适用于旧版MySQL的解决方案。 - dmSherazi

应该在字符串本身中转义任何引号。 - Garr Godfrey

这对我有用，但确实像@GarrGodfrey所说，您需要手动转义反斜杠（在添加更多反斜杠之前先执行此操作），引号，换行符/回车等。 - asontu

此外，即使只有一个值为null，group_concat()也会返回null。因此，在上面的示例中，如果phone为null但name不是，则整个查询将返回null。因此，如果您想要空字符串，请确保放置',"phone":"', ifnull(phone, ''), '"}'，如果您想要null在JSON中，请放置`',"phone":', ifnull(concat('"', phone, '"'), 'null'), '}'。最后，group_concat()默认具有1024个字符的最大长度。您可以使用set @@group_concat_max_len = 100000;将其设置得更高。 - asontu

Answer 4

JSON有两个名为json_arrayagg和json_objectagg的“group by”函数。

这个问题可以用以下方法解决:

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

这需要 MySQL 5.7 或更高版本。

- mikeryder

Answer 5

如果您需要一个嵌套的JSON数组对象，可以按下面的方法使用JSON_OBJECT和json_arrayagg进行连接：

{
    "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
        }
    ]
}

你也可以这样做：

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

- Juliano Costa

---

我遇到了错误 错误代码：1582。在调用本地函数“json_object”时参数计数不正确查询： SELECT v.*，p.*，（select json_arrayagg(json_object(size, sku, quantity)) from variant where variation_id = v.id and product_id = p.id) as variant FROM variation v join product p on v.product_id = p.id; - rajkanani

Answer 6

对于大多数情况，我使用DataGreap，但对于大表格，它无法工作。

我的GIST shell脚本

- Igor Stetsyuk

---

您的回答可以通过提供更多支持性信息来改进。请[编辑]以添加更多细节，比如引用或文档，以便其他人确认您的答案正确无误。您可以在帮助中心找到有关如何撰写良好回答的更多信息。 - Community