Mysql5.7将字段Json数组值进行列转行

Mysql 5.7将字段Json数组值进行列转行

问题

实际开发过程中，我们有可能会需要将某列 JSON数组 格式存储的值，拆分成行存储。比如来看下面一个例子：

有一张“分组信息表”，结构如下：

CREATE TABLE `group_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) NOT NULL COMMENT '组名',
  `member_info_json` text NOT NULL COMMENT '成员信息',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='分组信息表'

表“group_info”里有字段“member_info_json”是记录组内成员信息的，我先插入两条数据来看下结构

INSERT INTO `group_info`(`id`, `name`, `member_info_json`) VALUES (1, '小组1', '[{\"name\":\"张三\",\"age\":18,\"city\":\"北京\"},{\"name\":\"李四\",\"age\":20,\"city\":\"上海,杭州\"}]');
INSERT INTO `group_info`(`id`, `name`, `member_info_json`) VALUES (2, '小组2', '[{\"name\":\"王五\",\"age\":23,\"city\":\"西安\"}]');

可以看出每个成员都是有“name”、“age”、“city”三个属性的，那现在的要求就是 需要将上面两条数据的共计三个成员 变成三行记录，这样就可以用insert select的语法插入到另一张表中存储。

Mysql 8是有JSON_TABLE()函数可以用来处理，那在Mysql 5.7中是否有办法实现？

实现SQL

先给出SQL实现（注意：本例只对固定元素格式的JSON数组有效）：

SELECT 
	TRIM(BOTH '"' FROM JSON_EXTRACT(t1.name_s, CONCAT('$[',h.help_topic_id,']'))) AS `name`,
	JSON_EXTRACT(t1.age_s, CONCAT('$[',h.help_topic_id,']')) AS `age`,
	TRIM(BOTH '"' FROM JSON_EXTRACT(t1.city_s, CONCAT('$[',h.help_topic_id,']'))) AS `city`
FROM (
	SELECT 
		JSON_EXTRACT( member_info_json, '$[*].name' ) AS name_s,
		JSON_EXTRACT(member_info_json, '$[*].age' ) AS age_s,
		JSON_EXTRACT(member_info_json, '$[*].city' ) AS city_s 
	FROM group_info
) AS t1
JOIN mysql.help_topic AS h ON h.help_topic_id < JSON_LENGTH(t1.name_s);

结果：

+--------+------+---------------+
| name   | age  | city          |
+--------+------+---------------+
| 张三   | 18   | 北京          |
| 李四   | 20   | 上海,杭州     |
| 王五   | 23   | 西安          |
+--------+------+---------------+
3 rows in set (0.00 sec)

实现思路

初看之下，上述SQL有点复杂，下面就从里到外解释下每步做的事：

首先，最终肯定是要按 JSON 数组中元素的属性当做列存储结果，几个属性对应几列。比如本文例子，需要提取“name”、“age”、“city”这几个列。那我先不考虑行数，先将 JSON 数组中的每个元素的每个属性值按属性分列整合到一起展示。这里就可以使用函数 JSON_EXTRACT() 提取数组元素中每个属性值集合了。效果如下：

mysql> SELECT
    -> JSON_EXTRACT(member_info_json, '$[*].name' ) AS name_s,
    -> JSON_EXTRACT(member_info_json, '$[*].age' ) AS age_s,
    -> JSON_EXTRACT(member_info_json, '$[*].city' ) AS city_s
    -> FROM group_info;
+----------------------+----------+-----------------------------+
| name_s               | age_s    | city_s                      |
+----------------------+----------+-----------------------------+
| ["张三", "李四"]     | [18, 20] | ["北京", "上海,杭州"]       |
| ["王五"]             | [23]     | ["西安"]                    |
+----------------------+----------+-----------------------------+
2 rows in set (0.00 sec)

可以看出，上述结果中第一行有两个组成员，理论上应该要将两个成员的数据拆成对应的两行，分别存储值才对。

那我一步一步考虑，首先怎么实现一行转多行？简单！JOIN 一张多行的表就可以实现。

然后具体需要转成几行？例子中可以看出是和 JSON 数组中元素的个数相关，两个元素转成两行，三个元素转三行，以此类推。

OK，那得先确定元素个数。这里就可以用到 JSON_LENGTH() 函数获取 JSON 文档中的元素数量。

最后是关于 JOIN 表扩展多行的操作，我总不能不限制JOIN条件吧？这样JOIN的那种辅助表有几行，我就会有“元素个数×辅助表行数”的行数了。所以连接条件一定要配合元素的个数进行控制。

转念一想，那我只需找到一张含有int类型字段且值连续自增的表当辅助表不就可以了。这样只要和辅助表的连接条件是“辅助表int类型字段值 小于等于 数组元素个数”，这样就可以成功转成到对应元素个数的行数了。这里我推荐可以直接使用 mysql.help_topic 表，它有个特点就是它的字段help_topic_id值是逐1自增的，不过起始值是从0开始的。

mysql.help_topic是MySQL数据库系统中的一个系统表，用于存储关于MySQL服务器的帮助主题的相关信息。它包含了MySQL文档中的各个主题的详细说明。

既然如此，就拿 mysql.help_topic 进行表连接，这里直接用“name_s”数组的个数参与连接条件就可以了。SQL进一步实现如下：

mysql> SELECT
    -> h.help_topic_id,
    -> t1.name_s,
    -> t1.age_s,
    -> t1.city_s
    -> FROM (
    -> SELECT
    -> JSON_EXTRACT( member_info_json, '$[*].name' ) AS name_s,
    -> JSON_EXTRACT(member_info_json, '$[*].age' ) AS age_s,
    -> JSON_EXTRACT(member_info_json, '$[*].city' ) AS city_s
    -> FROM group_info
    -> ) AS t1
    -> JOIN mysql.help_topic AS h ON h.help_topic_id < JSON_LENGTH(t1.name_s);
+---------------+----------------------+----------+-----------------------------+
| help_topic_id | name_s               | age_s    | city_s                      |
+---------------+----------------------+----------+-----------------------------+
|             0 | ["张三", "李四"]     | [18, 20] | ["北京", "上海,杭州"]       |
|             1 | ["张三", "李四"]     | [18, 20] | ["北京", "上海,杭州"]       |
|             0 | ["王五"]             | [23]     | ["西安"]                    |
+---------------+----------------------+----------+-----------------------------+
3 rows in set (0.00 sec)

根据上一步的结果，接下来要做就是把每个数组的元素对应拆分开来就行了，即本例中“小组1”数据的第一行的“name_s”，“age_s”，“city_s”都取第一个元素值，第二行的“name_s”，“age_s”，“city_s”都取第二个元素值，以此类推。那怎么获取json数组的元素呢？还是可以使用 JSON_EXTRACT() 函数，例如获取数组的第一个元素就可以写成：

mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[0]');
+--------------------------------------------+
| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
+--------------------------------------------+
| 10                                         |
+--------------------------------------------+

至于每一行获取第几个元素那就更显而易见了，直接用上连接的 mysql.help_topic 表中的 help_topic_id 字段作为数组下标，最终就变成：

mysql> SELECT
    -> h.help_topic_id,
    -> JSON_EXTRACT(t1.name_s, CONCAT('$[',h.help_topic_id,']')) AS `name`,
    -> JSON_EXTRACT(t1.age_s, CONCAT('$[',h.help_topic_id,']')) AS `age`,
    -> JSON_EXTRACT(t1.city_s, CONCAT('$[',h.help_topic_id,']')) AS `city`
    -> FROM (
    -> SELECT
    -> JSON_EXTRACT( member_info_json, '$[*].name' ) AS name_s,
    -> JSON_EXTRACT(member_info_json, '$[*].age' ) AS age_s,
    -> JSON_EXTRACT(member_info_json, '$[*].city' ) AS city_s
    -> FROM group_info
    -> ) AS t1
    -> JOIN mysql.help_topic AS h ON h.help_topic_id < JSON_LENGTH(t1.name_s);
+---------------+----------+------+-----------------+
| help_topic_id | name     | age  | city            |
+---------------+----------+------+-----------------+
|             0 | "张三"   | 18   | "北京"          |
|             1 | "李四"   | 20   | "上海,杭州"     |
|             0 | "王五"   | 23   | "西安"          |
+---------------+----------+------+-----------------+
3 rows in set (0.00 sec)

最后，只需要将前后带双引号“"”的值用 TRIM() 函数去除下双引号即可。

后记

不同的业务对应的实现会有所区别，本文只提供一个简单示例作为思路，具体业务的实现还是要结合实际需求。

若你在Mysql 5.7下有更优的实现方式，欢迎留言赐教！