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

引言

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

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

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

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

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

SUBSTRING_INDEX

JSON_EXTRACT

具體用法，請(qǐng)自行百度，本文不做講解。

第一步：一行拆分成多行

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

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

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

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

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

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

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

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

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

1.3 通過(guò)join on拆分多行

這時(shí)候，就可以通過(guò) 將maptest表和新建的 keyid表進(jìn)行join，用on條件，匹配成多行。在通過(guò) SUBSTRING_INDEX進(jìn)行拆分。

代碼如下：

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字符串

拆分成多行之后，就可以通過(guò) JSON_EXTRACT 進(jìn)行解析了。效果如下：

完成代碼如下：

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)然通過(guò) 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 )
;

注意：通過(guò) JSON_EXTRACT 解析出來(lái)的字段，如果是字符串，會(huì)帶有 "" 雙引號(hào)，只要replace替換掉即可。

總結(jié)

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

您可能感興趣的文章: