mysql json 数据解析

一、目标需求
mysql5.7支持json极大的方便了我们,但是其中json数组解析一直很麻烦,之前都是先取json数组的长度,然后遍历逐个解析,后来看了一篇博客受到启发,写了这个方法能一次全部解析出来,希望对大家有所启发。

二、建表
新建jsonarray_test表并添加测试数据。

CREATE TABLE jsonarray_test (
  id int(11) NOT NULL AUTO_INCREMENT,
  title varchar(10) NOT NULL COMMENT '名称',
  info json DEFAULT NULL COMMENT 'json数组',
  UNIQUE KEY (title),
  KEY id (id)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
REPLACE INTO jsonarray_test(title, info) value ('上证19','[{"Symbol": "600030", "ShortName": "中信证券"}, {"Symbol": "601166", "ShortName": "兴业银行"}, {"Symbol": "600887", "ShortName": "伊利股份"},
{"Symbol": "600000", "ShortName": "浦发银行"}, {"Symbol": "601857", "ShortName": "中国石油"}, {"Symbol": "600999", "ShortName": "招商证券"}, {"Symbol": "601006", "ShortName": "大秦铁路"}, 
{"Symbol": "601989", "ShortName": "中国重工"}, {"Symbol": "600104", "ShortName": "上海汽车"}, {"Symbol": "601377", "ShortName": "兴业证券"}, {"Symbol": "601390", "ShortName": "中国中铁"},
{"Symbol": "600050", "ShortName": "中国联通"}, {"Symbol": "600048", "ShortName": "保利地产"}, {"Symbol": "600111", "ShortName": "包钢稀土"}, {"Symbol": "601169", "ShortName": "北京银行"},
{"Symbol": "601800", "ShortName": "中国交建"}, {"Symbol": "600547", "ShortName": "山东黄金"}, {"Symbol": "601668", "ShortName": "中国建筑"}, {"Symbol": "601688", "ShortName": "华泰证券"}]');
REPLACE INTO jsonarray_test(title, info) value ('西藏板块','[{"Symbol": "000752", "ShortName": "西藏发展"}, {"Symbol": "000762", "ShortName": "西藏矿业"}, {"Symbol": "600211", "ShortName": "西藏药业"},
{"Symbol": "600326", "ShortName": "西藏天路"}, {"Symbol": "600338", "ShortName": "西藏珠峰"}, {"Symbol": "600749", "ShortName": "西藏旅游"}, {"Symbol": "600773", "ShortName": "西藏城投"}]');
1
2
3
4
5
6
7
8
9
10
11
12
13
14


三、写查询存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_jsonarray_extract`$$
CREATE PROCEDURE `sp_jsonarray_extract`()
BEGIN
    declare v_i int default 1;
    DROP TABLE IF EXISTS info_incre_table;
    create table info_incre_table (ID int NOT NULL,PRIMARY KEY (ID))ENGINE=MyISAM DEFAULT CHARSET=utf8; -- 自增临时表
    WHILE (v_i<100) DO -- 100代表解json数组的最大长度
        insert into info_incre_table values (v_i); 
        SET v_i=v_i+1;
    END WHILE;
    select a.title,substring_index(substring_index(replace(replace(replace(replace(JSON_EXTRACT(a.info,'$[*].Symbol'),' ',''),'"',''),'[',''),']',''),',',b.ID),',',-1) as symbol from jsonarray_test a
    join info_incre_table b on b.ID <= (length(JSON_EXTRACT(a.info,'$[*].Symbol')) - length(replace(JSON_EXTRACT(a.info,'$[*].Symbol'),',',''))+1) order by a.title;
END$$
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
执行SQL:call sp_jsonarray_extract();;

mysql5.7查询结果(是我们想要的): 

四、说明总结
mysql本身可以这样解析出json数组的所有元素:select title,JSON_EXTRACT(info,’$[*].Symbol’) from jsonarray_test; 
结果如下,显然这样的结果并不方便我们进行运算和一系列的操作。 

我这个小案例其实是基于上面这个结果,将其分解,利用一张自增临时表,避免了循环遍历,一次把结果解出来,但是效率在数据量较大时不是很高。实测解析出3W条记录用时60S左右。本案例希望能对你的工作或学习有所启发。 
原文地址:http://www.enjoyown.com/mysql5.7-Jsonarray/
--------------------- 
作者:明亮Lucky 
来源:CSDN 
原文:https://blog.csdn.net/u013329580/article/details/77096630 
版权声明:本文为博主原创文章,转载请附上博文链接!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值