gpt4 book ai didi

MySQL JSON 聚合函数

转载 作者:行者123 更新时间:2023-11-29 11:03:01 34 4
gpt4 key购买 nike

MySQL 从 5.7 版开始就提供了一些基本的 JSON 支持,但是我一直在查看文档,但找不到任何 JSON 可聚合函数。

MySQL 5.7+ 中有 JSON 聚合函数吗?

例如,如果您有查询:

SELECT id, GROUP_CONCAT(name) FROM given_names GROUP BY id

你会得到如下结果:

id | name
1 | Jon,Smith
2 | Hubert,Blaine,Wolfeschlegelsteinhausenbergerdorff
....

有没有等效的方法来获取 JSON 数组?

我关心的是正确转义字符串,因为当您使用GROUP_CONCAT时,如果字符串包含分隔符,则之后几乎不可能区分各个部分。

最佳答案

从 MySQL 5.7.22 开始,可以使用 JSON_ARRAYAGGJSON_OBJECTAGG 。取自文档的示例:

JSON_ARRAYAGG

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+--------+
| o_id | attribute | value |
+------+-----------+--------+
| 2 | color | red |
| 2 | fabric | silk |
| 3 | color | green |
| 3 | shape | square |
+------+-----------+--------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes
FROM t3 GROUP BY o_id;
+------+---------------------+
| o_id | attributes |
+------+---------------------+
| 2 | ["color", "fabric"] |
| 3 | ["color", "shape"] |
+------+---------------------+
2 rows in set (0.00 sec)

JSON_OBJECTAGG

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+--------+
| o_id | attribute | value |
+------+-----------+--------+
| 2 | color | red |
| 2 | fabric | silk |
| 3 | color | green |
| 3 | shape | square |
+------+-----------+--------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value) |
+------+---------------------------------------+
| 2 | {"color": "red", "fabric": "silk"} |
| 3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)

关于MySQL JSON 聚合函数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/41895351/

34 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com