MYSQL中的json資料操作程式碼

MYSQL中的json資料操作

1.2 基礎查詢操作

用法提示：

• 如果json字串不是陣列，則直接使用$.欄位名
• 如果json字串是陣列[Array]，則直接使用$[對應元素的索引id]

1.2.1 一般json查詢

使用json欄位名->’$.json屬性’進行查詢條件,注意：如果 ‘->’ 不能用也可用 ‘->>’ 查詢
舉個例子：如果想查詢deptLeader=張五的資料，那麼sql語句如下：

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';

1.2.2 多個條件查詢

比如想查dept為“部門3”和deptLeaderId=5的資料，sql如下：

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';

1.2.3 json中多個欄位關係查詢

比如想查詢json格式中deptLeader=張五和deptId=5的資料

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';

1.2.4 關聯表查詢

這裡我們要連表查詢在dept 表中部門leader在dept_leader 中的詳情

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

1.3 JSON函數操作

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

1.3.1 官方json函數

1.3.2 ->、->>區別

->在field中使用的時候結果帶引號，->>的結果不帶引號

1.3.2.2 在where條件中使用

特別注意：->當做where查詢是要注意型別的，->>是不用注意型別的

1.3.3 json_extract():從json中返回想要的欄位

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

select id,json_extract(json_value,'$.deptName') as deptName from dept;

1.3.4 JSON_CONTAINS():JSON格式資料是否在欄位中包含特定物件

用法: JSON_CONTAINS(target, candidate[, path])
事例:如果我們想查詢包含deptName=部門5的物件

select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部門5"))

1.3.5 SON_OBJECT():將一個鍵值對列表轉換成json物件

比如我們想查詢某個物件裡面的值等於多少
比如我們新增這麼一組資料到dept表中：

insert into dept VALUES(6,'部門9','{"deptName": {"dept":"de","depp":"dd"}, "deptId": "5", "deptLeaderId": "5"}');

我們可以看到deptName中還有一個物件，裡面還有dept和depp兩個屬性欄位，那麼我們應該怎麼查詢depp=dd的員工呢。

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

SELECT * from (
    SELECT *,json_value->'$.deptName' as deptName FROM dept
) t WHERE JSON_CONTAINS(deptName,JSON_OBJECT("depp","dd"));

1.3.6 JSON_ARRAY():建立JSON陣列

比如我們新增這麼一組資料到dept表中：

insert into dept VALUES(7,'部門9','{"deptName": ["1","2","3"], "deptId": "5", "deptLeaderId": "5"}');
insert into dept VALUES(7,'部門9','{"deptName": ["5","6","7"], "deptId": "5", "deptLeaderId": "5"}');

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

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

SELECT * from dept WHERE JSON_CONTAINS(json_value->'$.deptName',JSON_ARRAY("1"))

1.3.7 JSON_TYPE():查詢某個json欄位屬性型別

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

SELECT json_value->'$.deptName' ,JSON_TYPE(json_value->'$.deptName') as type from dept 

1.3.8 JSON_KEYS():JSON檔案中的鍵陣列

用法：JSON_KEYS(json_value)
事例：比如我們想查詢json格式資料中的所有key

SELECT JSON_KEYS(json_value) FROM dept 

接下來的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.3.9 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;

注意：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」}，這樣的話會把之前的單元格值覆蓋後再新增/覆蓋這個單元格欄位

1.3.10 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中插入新值，但不替換已經存在的舊值。

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

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

到此這篇關於MYSQL中的json資料操作的文章就介紹到這了,更多相關mysql json資料內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！