mysql解析json數據組獲取數據組所有字段的方法實例

Posted on by

引言

在開發過程中，遇到過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.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

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

當然通過 mysql.help_topic 表的 help_topic_id 字段也是可以。代碼和結果如下：

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

總結 

到此這篇關於mysql解析json數據組獲取數據組所有字段的文章就介紹到這瞭,更多相關mysql解析json數據組內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：