mysql解析json資料組怎麼取得資料組所有字段-mysql教程-PHP中文網

mysql解析json資料組怎麼取得資料組所有字段

王林

發布： 2023-05-29 20:01:46

轉載

1749 人瀏覽過

引言

在開發過程中，遇到json資料組的字串，需要解析json組，得到組內所有的資訊。如下格式：

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

登入後複製

觀察json群組發現，它是一個list裡麵包含多個json字串，我們要做的是拆分出list所有json字串，並對每個json字串做解析。

分析後發現，當JSON字串獨立存在時，可以使用JSON_EXTRACT方法進行擷取。由於清單中包含多個JSON字串，因此我們需要將清單拆分為多個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字串的個數。舉例來說，如果在上述列表中有4個JSON字串，那麼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拆分多行

此時，我們可以透過使用JOIN操作將maptest表和新建的keyid表連接起來，在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 )
;

登入後複製