MySQL JSON值分组合并

MySQL是一个关系型数据库管理系统，但自从5.7版本开始，它支持了 JSON 数据类型和一系列的 JSON 函数，使 MySQL 可以底层支持 JSON 数据解析。但有时我们可能需要在 SQL 中将 JSON 值进行分组和合并，比如根据一个列中的 JSON 数组，将其中的值进行分组，筛选出符合条件的值并合并为一个新的 JSON 数组。在这篇文章中，我们将探讨在 MySQL 中如何对JSON值进行分组合并。

阅读更多：MySQL 教程

准备示例数据

首先让我们创建一个在MySQL中的 JSON 列，用于演示如何进行分组合并操作。

CREATE TABLE `test_json` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `json_value` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test_json` (`id`, `name`, `json_value`) VALUES
(1, 'John', '{"hobby": ["swim","basketball","music"]}' ),
(2, 'Lucy', '{"hobby": ["swim","reading","movie"]}' ),
(3, 'Linda', '{"hobby": ["movie","music"]}' ),
(4, 'David', '{"hobby": ["reading","music"]}' ),
(5, 'Tom', '{"hobby": ["swim","reading"]}' );

这里我们创建了一张 test_json 表，其中包含了四个字段：id 作为主键，name 作为名称，json_value 中存储了 JSON 值。

将 JSON 值拆分为行

在将 JSON 值进行分组操作之前，我们需要将 JSON 值拆分为行。可以使用 MySQL 内置的 JSON_TABLE 函数将 JSON 对象或数组转换为关系表。下面是一个简单的示例：

SELECT j.hobby
FROM test_json t, JSON_TABLE(t.json_value, ' $.hobby[*]' COLUMNS (hobby VARCHAR(50) PATH '$ ')) j;

这个语句使用了 JSON_TABLE 函数和 COLUMNS 子句，将 test_json 表中的 json_value 字段拆分为行，其中 JSON_TABLE 函数将 JSON 值转换为关系表，COLUMNS 用于定义列名和数据类型，. PATH 用于定义每列所需要取的 JSON 值。结果如下：

+-----------+
| hobby     |
+-----------+
| swim      |
| basketball|
| music     |
+-----------+

+-----------+
| hobby     |
+-----------+
| swim      |
| reading   |
| movie     |
+-----------+

+-----------+
| hobby     |
+-----------+
| movie     |
| music     |
+-----------+

+-----------+
| hobby     |
+-----------+
| reading   |
| music     |
+-----------+

+-----------+
| hobby     |
+-----------+
| swim      |
| reading   |
+-----------+

这样，我们就可以对这些数据进行操作。

将 JSON 值进行分组

使用上面提到的 JSON_TABLE 函数，我们可以在 MySQL 中对 JSON 值进行分组。比如，如果我们需要按照爱好将名字进行分组，并计算每个分组有多少人，可以执行如下语句：

SELECT j.hobby AS hobby, COUNT(*) AS count
FROM test_json t, JSON_TABLE(t.json_value, ' $.hobby[*]' COLUMNS (hobby VARCHAR(50) PATH '$ ')) j 
GROUP BY j.hobby;

这个语句首先使用了前文提到的 JSON_TABLE 函数将 JSON 数组（即爱好）拆分为多行，并将拆分出的爱好分配到对应的人名上。然后使用 GROUP BY 语句将爱好相同的人名进行分组，并计算每个分组的人数。结果如下：

+-----------+-------+
| hobby     | count |
+-----------| basketball| 1     |
| movie     | 2     |
| music     | 3     |
| reading   | 2     |
| swim      | 3     |
+-----------+-------+

可以看到，以上查询结果成功地将姓名按照爱好进行了分组，并统计了每个分组的人数。

将 JSON 值进行合并

使用上面提到的 JSON_TABLE 函数，我们还可以在 MySQL 中将 JSON 值进行合并。比如，我们需要筛选出爱好为“游泳”的人，并将其余爱好合并为一个新的数组，可以执行如下语句：

SELECT name, JSON_ARRAYAGG(j.hobby) AS other_hobby
FROM test_json t, JSON_TABLE(t.json_value, ' $.hobby[*]' COLUMNS (hobby VARCHAR(50) PATH '$ ')) j 
WHERE j.hobby != 'swim'
GROUP BY name;

这个语句首先使用了前文提到的 JSON_TABLE 函数将 JSON 数组（即爱好）拆分为多行，并将拆分出的爱好分配到对应的人名上。接着使用了 WHERE 语句筛选出了爱好为“游泳”的人名，并用 JSON_ARRAYAGG 函数将其余爱好合并为一个新的数组。最后使用 GROUP BY 语句，将结果按照姓名进行分组。结果如下：

+-------+----------------------------+
| name  | other_hobby                |
+-------+----------------------------+
| David | ["reading", "music"]       |
| Linda | ["movie", "music"]         |
| Lucy  | ["reading", "movie"]       |
| John  | ["basketball", "music"]    |
+-------+----------------------------+

可以看到，以上查询结果成功地将爱好为“游泳”的人名筛选出，并将其余爱好合并为一个新的数组。

总结

在本文中，我们探讨了如何在 MySQL 中对 JSON 值进行分组合并操作。首先我们通过 JSON_TABLE 函数将 JSON 值拆分为行，然后使用 GROUP BY 语句对数据进行分组，使用 JSON_ARRAYAGG 函数对数据进行合并。使用这些技巧，我们可以在 MySQL 中方便地处理 JSON 数据。