mysql解析json数据组获取数据组所有字段的方法实例

作者:xixiangdai 时间:2024-01-21 11:10:13 

引言

在开发过程中,遇到过json数据组的字符串,需要解析json组,得到组内所有的信息。如下格式:

[{"itemId":3101,"itemName":"空滤器及进气管道"},{"itemId":3102,"itemName":"水管、水泵"},{"itemId":3103,"itemName":"柴油管道"},{"itemId":3104,"itemName":"高压泵、机油泵"}]

观察json组发现,它是一个list里面包含多个json字符串,我们要做的是拆分出list所有json字符串,并对每个json字符串做解析。

做分析发现,如果是单独一个json字符串,通过 JSON_EXTRACT 方法即可。但是list里面有多个json字符串,所以我们需要对list进行拆分,变成多个json字符串。

在学习本文内容之前,需要提前了解mysql两个函数:

SUBSTRING_INDEX

JSON_EXTRACT

 具体用法,请自行百度,本文不做讲解。

第一步:一行拆分成多行

一行拆成多行,即把list拆分成多行 json,为此我们需要

1.1 新建一张表keyid,只insert从0开始的数字,如下:

mysql解析json数据组获取数据组所有字段的方法实例

在其他的教程中,通过 mysql.help_topic 表的 help_topic_id 字段也是可以的。但是这个库表需要root权限才可以使用。因此建立自己的匹配表,是最合适的。

注意:id的值,不能小于 list里面json字符串的个数。比如上述list里面的json字符串是4个,那id必须大于4。help_topic_id最大值是700,如果list里面json字符串的个数大于这个值,用help_topic_id是不合适的。

1.2 找到拆分标识符

所谓拆分标识符,就是能根据此符号,一次性拆分成多行的标志。在下面list当中,没有找到拆分标识符,因此需要处理一下。可以将 ; 当成拆分标识符。处理后的内容如下:

{"itemId":3101,"itemName":"空滤器及进气管道"};{"itemId":3102,"itemName":"水管、水泵"};{"itemId":3103,"itemName":"柴油管道"};{"itemId":3104,"itemName":"高压泵、机油泵"}

 去除前后 [ 和 ] 两个list标志,将 },{ 变成 };{ 这样就可以将 ; 变成拆分标识符。如下

select replace(replace(replace(jsonarr,"},{","};{"),"]",""),"[","") as jsonarr from maptest

mysql解析json数据组获取数据组所有字段的方法实例

1.3 通过join on拆分多行

这时候,就可以通过 将maptest表和 新建的 keyid表进行join,用on条件,匹配成多行。在通过 SUBSTRING_INDEX进行拆分。

mysql解析json数据组获取数据组所有字段的方法实例

 代码如下:

select
a.jsonarr,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ) AS jsonarr_info,
b.id
from
(select replace(replace(replace(jsonarr,"},{","};{"),"]",""),"[","") as jsonarr from maptest) a
join keyid b
on b.id< ( length( a.jsonarr ) - length( replace ( a.jsonarr, ";", "" ) ) + 1 )
;

到此,就完成了 将json组,拆分成多行的工作。

第二步:解析json字符串

拆分成多行之后,就可以通过 JSON_EXTRACT 进行解析了。效果如下:

mysql解析json数据组获取数据组所有字段的方法实例

完成代码如下:

select
a.jsonarr,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ) AS jsonarr_info,
b.id,
JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].itemId') as itemId,
replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].itemName'),'"','') as itemName
from
(select replace(replace(replace(jsonarr,"},{","};{"),"]",""),"[","") as jsonarr from maptest) a
join keyid b
on b.id< ( length( a.jsonarr ) - length( replace ( a.jsonarr, ";", "" ) ) + 1 )
;

当然通过 mysql.help_topic 表的 help_topic_id 字段也是可以。代码和结果如下:

mysql解析json数据组获取数据组所有字段的方法实例

select
a.jsonarr,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ) AS jsonarr_info,
b.help_topic_id,
JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ), '$[0].itemId') as itemId,
replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.help_topic_id + 1 ), ";",- 1 ), '$[0].itemName'),'"','') as itemName
from
(select replace(replace(replace(jsonarr,"},{","};{"),"]",""),"[","") as jsonarr from maptest) a
join mysql.help_topic b
on b.help_topic_id < ( length( a.jsonarr ) - length( replace ( a.jsonarr, ";", "" ) ) + 1 )
;

 注意: 通过 JSON_EXTRACT 解析出来的字段,如果是字符串,会带有 "" 双引号,只要replace替换掉即可。

总结 

来源:https://blog.csdn.net/xixiangdai/article/details/125410222

标签:解析,json,数组
0
投稿

猜你喜欢

  • Python箱型图绘制与特征值获取过程解析

    2023-09-20 06:22:37
  • Vuex中如何getters动态获取state的值

    2024-05-28 15:54:23
  • javascript给span标签赋值的方法

    2023-09-05 21:28:53
  • 在pytorch中动态调整优化器的学习率方式

    2022-08-14 00:30:57
  • javascript实现延时显示提示框特效代码

    2024-04-25 13:10:50
  • 详解Python3中yield生成器的用法

    2021-09-03 05:59:27
  • mac下安装mysql忘记密码的修改方法

    2024-01-25 17:34:01
  • Python中base64与xml取值结合问题

    2021-08-22 21:15:03
  • Python之关于类变量的两种赋值区别详解

    2021-09-08 08:05:26
  • asp如何远程读取数据库页面?

    2010-06-16 09:57:00
  • 5个MySQL GUI工具推荐,帮助你进行数据库管理

    2024-01-14 09:32:29
  • Python基础之hashlib模块subprocess模块logging模块

    2022-08-12 02:45:52
  • python基础之文件操作

    2022-05-08 11:55:41
  • 妙用Dreamweaver MX共享Word XP文件

    2010-09-05 21:17:00
  • Python基于爬虫实现全网搜索并下载音乐

    2023-12-26 16:36:40
  • CSS绝对定位在宽屏分辨率下错位

    2009-07-28 12:24:00
  • MySQL常用分库分表方案汇总

    2024-01-18 10:51:14
  • jupyter notebook中美观显示矩阵实例

    2023-06-06 18:13:35
  • 理解SQL SERVER中的逻辑读,预读和物理读

    2012-01-05 19:32:29
  • Laravel框架文件上传功能实现方法示例

    2023-11-15 19:16:31
  • asp之家 网络编程 m.aspxhome.com