快捷導(dǎo)航

mysql解析json數(shù)據(jù)組獲取數(shù)據(jù)組所有字段的方法實例

更新時間：2022年08月22日 11:38:42 作者：xixiangdai

mysql在5.7開始支持json解析了,也可以解析數(shù)組,下面這篇文章主要給大家介紹了關(guān)于mysql解析json數(shù)據(jù)組獲取數(shù)據(jù)組所有字段的相關(guān)資料,文中通過圖文以及實例代碼介紹的非常詳細(xì),需要的朋友可以參考下

引言

在開發(fā)過程中，遇到過json數(shù)據(jù)組的字符串，需要解析json組，得到組內(nèi)所有的信息。如下格式：

[{"itemId":3101,"itemName":"空濾器及進氣管道"},{"itemId":3102,"itemName":"水管、水泵"},{"itemId":3103,"itemName":"柴油管道"},{"itemId":3104,"itemName":"高壓泵、機油泵"}]

觀察json組發(fā)現(xiàn)，它是一個list里面包含多個json字符串，我們要做的是拆分出list所有json字符串，并對每個json字符串做解析。

做分析發(fā)現(xiàn)，如果是單獨一個json字符串，通過 JSON_EXTRACT 方法即可。但是list里面有多個json字符串，所以我們需要對list進行拆分，變成多個json字符串。

在學(xué)習(xí)本文內(nèi)容之前，需要提前了解mysql兩個函數(shù)：

SUBSTRING_INDEX

JSON_EXTRACT

具體用法，請自行百度，本文不做講解。

第一步：一行拆分成多行

一行拆成多行，即把list拆分成多行 json，為此我們需要

1.1 新建一張表keyid，只insert從0開始的數(shù)字，如下：

在其他的教程中，通過 mysql.help_topic 表的 help_topic_id 字段也是可以的。但是這個庫表需要root權(quán)限才可以使用。因此建立自己的匹配表，是最合適的。

注意：id的值，不能小于 list里面json字符串的個數(shù)。比如上述list里面的json字符串是4個，那id必須大于4。help_topic_id最大值是700，如果list里面json字符串的個數(shù)大于這個值，用help_topic_id是不合適的。

1.2 找到拆分標(biāo)識符

所謂拆分標(biāo)識符，就是能根據(jù)此符號，一次性拆分成多行的標(biāo)志。在下面list當(dāng)中，沒有找到拆分標(biāo)識符，因此需要處理一下?？梢詫?; 當(dāng)成拆分標(biāo)識符。處理后的內(nèi)容如下：

{"itemId":3101,"itemName":"空濾器及進氣管道"};{"itemId":3102,"itemName":"水管、水泵"};{"itemId":3103,"itemName":"柴油管道"};{"itemId":3104,"itemName":"高壓泵、機油泵"}

去除前后 [ 和 ] 兩個list標(biāo)志，將 },{ 變成 };{ 這樣就可以將 ; 變成拆分標(biāo)識符。如下

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

1.3 通過join on拆分多行

這時候，就可以通過將maptest表和新建的 keyid表進行join，用on條件，匹配成多行。在通過 SUBSTRING_INDEX進行拆分。

代碼如下：

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 進行解析了。效果如下：

完成代碼如下：

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 )
;

當(dāng)然通過 mysql.help_topic 表的 help_topic_id 字段也是可以。代碼和結(jié)果如下：

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替換掉即可。

總結(jié)

到此這篇關(guān)于mysql解析json數(shù)據(jù)組獲取數(shù)據(jù)組所有字段的文章就介紹到這了,更多相關(guān)mysql解析json數(shù)據(jù)組內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: