MySQL對JSON型別欄位資料進行提取和查詢的實現

2022-04-21 16:01:29

前言

昨天上線後通過系統報警發現了一個bug，於是緊急進行了回滾操作，但是期間有使用者下單，資料產生了影響，因此需要排查影響了哪些訂單，並對資料進行修復。

1. 問題現象

由於bug導致了訂單表的customer_extra_info欄位的hasFreightInsurance誤更新成了“是”，因此需要查詢回滾前一共有多少被誤更新為“是”的訂單，如下圖：

於是檢視訂單表中customer_extra_info欄位型別發現是JSON型別的

2. 解決方案

查詢資料發現mysql5.7以後提供了一種新的欄位格式-json。

對JSON型別的資料MySQL提供了相關的查詢操作。

先給出查詢SQL，後面在介紹MySQL對JSON型別欄位的查詢操作

SELECT
	* 
FROM
	( SELECT id, customer_extra_info -> '$.hasFreightInsurance' AS Insurance FROM oms_order_list WHERE project_id = 1 AND update_time > '2022-04-15 16:30:17' ) t 
WHERE
	JSON_CONTAINS ( Insurance, JSON_OBJECT ( "value", "是" ) )

3. JSON資料查詢

3.1 一般基礎查詢操作

1、使用 json欄位名->’$.json屬性’ 進行查詢條件

SELECT
	id,
	customer_extra_info
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'
	AND customer_extra_info -> '$.maxClaimAmount'=10

查詢結果如下：

2、關聯表查詢

json欄位也支援關聯表的查詢，這裡只寫出使用方法，不做範例展示。其中deptLeaderId和id分別是dept,dept_leader兩個表中的關聯欄位。

SELECT * from dept,dept_leader WHERE dept.json_value->'$.deptLeaderId'=dept_leader.json_value->'$.id' ;

3.2 一般函數查詢操作

寫到這裡大家都發現了，我們查詢的json都是整條json資料，這樣看起來不是很方便，那麼如果我們只想看json中的某個欄位怎麼辦？

這樣就引入了我們的第一個函數:json_extract(欄位名,json欄位名)

在詳細介紹用法之前我們可以看看官網的函數介紹：

咱們可以看到官網介紹json_extract（）這個函數很詳細：Return data from JSON document

從json中返回欄位

1、函數 json_extract（）：從json中返回想要的欄位

用法：json_extract(欄位名,$.json欄位名)
範例：

SELECT
	id,
	json_extract ( customer_extra_info, '$.hasFreightInsurance' ) AS hasFreightInsurance 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'

查詢結果如下：

2、函數JSON_CONTAINS()：JSON格式資料是否在欄位中包含特定物件

用法: JSON_CONTAINS(target, candidate[, path])
範例：

SELECT
	id,
	customer_extra_info 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17' 
	AND JSON_CONTAINS ( customer_extra_info, JSON_OBJECT ( "maxClaimAmount", 10 ) )

查詢結果如下：

3、函數JSON_OBJECT():將一個鍵值對列表轉換成json物件

比如我們想查詢某個物件裡面的值等於多少

我們可以看到hasFreightInsurance中還有一個物件，裡面還有name和value兩個屬性欄位，那麼我們應該怎麼查詢value=否的訂單呢。

用法：JSON_OBJECT([key, val[, key, val] …])
範例：

SELECT
	* 
FROM
	( SELECT id, customer_extra_info -> '$.hasFreightInsurance' AS Insurance FROM oms_order_list WHERE project_id = 1 AND update_time > '2022-04-15 16:30:17' ) t 
WHERE
	JSON_CONTAINS ( Insurance, JSON_OBJECT ( "value", "否" ) )

查詢結果如下：

4、函數JSON_ARRAY():建立JSON陣列

用法：JSON_ARRAY([val[, val] …])

範例：我們要查詢deptName包含1的資料

SELECT
	id,
	customer_extra_info 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17' 
	AND JSON_CONTAINS ( customer_extra_info -> '$.deptName', JSON_ARRAY ( "1" ) )

查詢結果如下：

5、函數JSON_TYPE(）：查詢某個json欄位屬性型別

用法：JSON_TYPE(json_val)
事例：比如我們想查詢deptName的欄位屬性是什麼

SELECT
	id,
	customer_extra_info -> '$.deptName',
	JSON_TYPE ( customer_extra_info -> '$.deptName' ),
	customer_extra_info -> '$.hasFreightInsurance',
	JSON_TYPE ( customer_extra_info -> '$.hasFreightInsurance' ) 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'

查詢結果如下：

6、函數JSON_EXTRACT() ：從JSON檔案返回資料

這也是我們開發中會經常用到的一個函數

SELECT
	* 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17' 
	AND JSON_CONTAINS ( JSON_EXTRACT ( customer_extra_info, '$.hasFreightInsurance' ), JSON_OBJECT ( "value", "否" ) )

查詢結果如下：

7、函數JSON_KEYS() ：JSON檔案中的鍵陣列

用法：JSON_KEYS(json_value)

範例：比如我們想查詢json格式資料中的所有key

SELECT
	id,
	JSON_KEYS ( customer_extra_info ) 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'

查詢結果如下：

4. JSON資料新增更新刪除

接下來的3種函數都是新增資料型別的：
JSON_SET(json_doc, path, val[, path, val] …)
JSON_INSERT(json_doc, path, val[, path, val] …)
JSON_REPLACE(json_doc, path, val[, path, val] …)

1、函數JSON_SET() ：將資料插入JSON格式中，有key則替換，無key則新增
這也是我們開發過程中經常會用到的一個函數

用法：JSON_SET(json_doc, path, val[, path, val] …)

範例：比如我們想針對id=2的資料新增一組：newData:新增的資料,修改deptName為新增的部門1
sql語句如下：

update dept set json_value=JSON_SET('{"deptName": "部門2", "deptId": "2", "deptLeaderId": "4"}','$.deptName','新增的部門1','$.newData','新增的資料') WHERE id=2;
 
select * from dept WHERE id =2

結果：

注意：json_doc如果不帶這個單元格之前的值，之前的值是會被新值覆蓋的，比如我們如果更新的語句換成:

update dept set json_value=JSON_SET('{"a":"1","b":"2"}','$.deptName','新增的部門1','$.newData','新增的資料') WHERE id=2

我們可以看到這裡json_doc是{“a”:“1”,“b”:“2”}，這樣的話會把之前的單元格值覆蓋後再新增/覆蓋這個單元格欄位

結果:

2、函數JSON_INSERT():插入值（往json中插入新值，但不替換已經存在的舊值）
用法：JSON_INSERT(json_doc, path, val[, path, val] …)

範例：

UPDATE dept set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', '新增的部門2','$.newData2','新增的資料2') 
WHERE id=2

結果：

我們可以看到由於json_doc變化將之前的值覆蓋了，新增了deptName和newData2.
如果我們再執行以下剛才的那個sql，只是換了value，我們會看到裡面的key值不會發生變化。
因為這個函數只負責往json中插入新值，但不替換已經存在的舊值。

3、函數JSON_REPLACE（）
用法：JSON_REPLACE(json_doc, path, val[, path, val] …)

用例：
如果我們要更新id=2資料中newData2的值為：更新的資料2

sql語句如下：

UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "新增的部門2", "newData2": "新增的資料2"}', '$.newData2', '更新的資料2') WHERE id =2;
 
select * from dept WHERE id =2

結果：

4、函數JSON_REMOVE() ：從JSON檔案中刪除資料
用法：JSON_REMOVE(json_doc, path[, path] …)

舉例：刪除key為a的欄位。

UPDATE dept set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": "新增的部門2", "newData2": "更新的資料2"}','$.a') WHERE id =2;

結果：

5、函數JSON_SEARCH() ：用於在json格式中查詢並返回符合條件的節點
這是一個非常強大的函數

到此這篇關於MySQL對JSON型別欄位資料進行提取和查詢的實現的文章就介紹到這了,更多相關MySQL JSON欄位提取和查詢內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL對JSON型別欄位資料進行提取和查詢的實現

目錄

前言

1. 問題現象

2. 解決方案

3. JSON資料查詢

3.1 一般基礎查詢操作

3.2 一般函數查詢操作

4. JSON資料新增更新刪除

熱門文章