mysql解析json資料組獲取資料組所有欄位的方法範例

2022-08-22 14:00:48

引言

在開發過程中，遇到過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資料組內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！