mysql 解析 json,MySQL 解析普通JSON，解析JSON数组案例demo

MySQL 解析普通JSON，解析JSON数组案例demo

发布时间：2019-11-14作者：laosun阅读(2241)

首先呢创建一张表CREATE TABLE `t_json`  (  `id` int(11) NOT NULL AUTO_INCREMENT,  `json` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

插入两条测试数据INSERT INTO `t_json`(`id`, `json`) VALUES (1, '{\"id\": \"100\", \"name\": \"普通json\"}');INSERT INTO `t_json`(`id`, `json`) VALUES (2, '[{\"id\": \"1\", \"name\": \"json数组1\"}, {\"id\": \"2\", \"name\": \"json数组2\"}]');

解析json

我们使用 MySQL JSON_EXTRACT 语法

1. 对于普通json的解析方法如下：select JSON_EXTRACT(json, '$.name') from t_json where `id` = 1;#输出："普通json"为了更好的应用可以使用截取前后两位，将双引号去掉。

2. 解析json数组的方法有点复杂：

首先看我插入进去的数组格式：[{"id": "1", "name": "json数组1"}, {"id": "2", "name": "json数组2"}]

没有数组key值。 所以我们需要将其拼凑成完整json格式。

比如拼凑成： {"item":[{"id": "1", "name": "json数组1"}, {"id": "2", "name": "json数组2"}]}CONCAT('{"item":',json,'}')

然后我们再使用 JSON_EXTRACT 语法进行解析。如下所示：# 第一个JSON_EXTRACT是为了解析json，指定我们的key，就是要解析的数组。# 第二个JSON_EXTRACT是解析指定key的数组。 这块我使用$[*].name：表示循环解析所有的name值，如果要解析第一个name值，使用$[0].name# 如果要解析id，就将$[*].name 换成 $[*].idselect JSON_EXTRACT(JSON_EXTRACT(CONCAT('{"item":',json,'}'), '$.item'), '$[*].name') from t_json where `id` = 2;# 以上SQL执行输出：["json数组1", "json数组2"]

看样子也不是我们想要的。json数组1,json数组2 也比 ["json数组1", "json数组2"] 这种格式要好。所以下边我们进行转换。

转换的方式是使用replace语法，进行替换。

这块使用有点绕，其实就是使用了三次repalce，第一次是将 [" 替换掉， 第二次是将", " 替换掉，第三次是将 "] 替换掉。如下所示：select REPLACE(REPLACE(REPLACE(JSON_EXTRACT(JSON_EXTRACT(CONCAT('{"item":',json,'}'), '$.item'), '$[*].name'), '["', ''), '", "', ','), '"]', '') from t_json where `id` = 2;#输出结果：json数组1,json数组2

这时候可能有的业务要求特殊，需要将上边的值换成列，也就是我们所说的以逗号分隔，行转列。 这个我们需要使用 mysql.help_topic表来协助完成。select substring_index(substring_index(REPLACE(REPLACE(REPLACE(JSON_EXTRACT(JSON_EXTRACT(CONCAT('{"item":',j.json,'}'), '$.item'), '$[*].name'), '["', ''), '", "', ','), '"]', ''),',',ht.help_topic_id+1),',',-1) as 'value值'from  t_json j join mysql.help_topic hton ht.help_topic_id 

或者换种写法，写成以下这种形似，更容易理解一些select substring_index(substring_index(tmp.jsonstr,',',ht.help_topic_id+1),',',-1) as 'value值' from (select REPLACE(REPLACE(REPLACE(JSON_EXTRACT(JSON_EXTRACT(CONCAT('{"item":',json,'}'), '$.item'), '$[*].name'), '["', ''), '", "', ','), '"]', '') as jsonstr from t_json j where j.id = 2) as tmpjoin mysql.help_topic ht on ht.help_topic_id 

3 +1

版权声明

分享到:

发表评论

请文明留言

发表

共 0 条评论