MySQL資料庫基礎學習之JSON函數各類操作詳解

前言

很多日常業務場景都會用到json檔案作為資料儲存起來，而mysql5.7以上就提供了儲存json的支撐。往常儲存json一般都保留在pg庫或者是hive庫裡面，現在mysql有了支援的話基本業務都可以用mysql來實現。現在mysql8.x版本對json字元出處理已經做的非常完善了。現在就讓我們來詳細瞭解一下關於json資料資料型別mysql都有哪些函數能夠對其進行操作。

該系列文章將按照這個脈絡行文，此係列文章將被納入我的專欄一文速學SQL各類資料庫操作，基本覆蓋到使用SQL處理日常業務以及常規的查詢建庫分析以及複雜操作方方面面的問題。從基礎的建庫建表逐步入門到處理各類資料庫複雜操作，以及專業的SQL常用函數講解都花費了大量時間和心思創作，如果大家有需要從事資料分析或者資料開發的朋友推薦訂閱專欄，將在第一時間學習到最實用常用的知識。此篇部落格篇幅較長，值得細讀實踐一番，我會將精華部分挑出細講實踐。博主會長期維護博文，有錯誤或者疑惑可以在評論區指出，感謝大家的支援。

一、JSON語法規則

首先我們還是先複習一遍json資料型別的語法規則，這在我的很多文章都有寫到：

JSON是一個標記符的序列。這套標記符包含六個構造字元、字串、數位和三個字面名。

JSON是一個序列化的物件或陣列。

• 資料為  鍵 / 值 (name/value）對；
• 資料由逗號(,)分隔；
• 大括號儲存物件（object）；
• 方括號儲存陣列（Array）；

值可以是物件、陣列、數位、字串或者三個字面值(false、null、true)中的一個。值中的字面值中的英文必須使用小寫。

如：

"code":"100"

物件由花括號括起來的逗號分割的成員構成，成員是字串鍵和上文所述的值由逗號分割的鍵值對組成: ｛“code”:20,"type":"mysql"｝

陣列是由方括號括起來的一組值構成:

"datesource":[
{"code":"20", "type":"mysql"},
{"code":"20", "type":"mysql"},
{"code":"20", "type":"mysql"}
]

複習完畢之後我們再來對mysql處理json函數實驗。

二、JSON函數

首先我們建立一個表來進行操作：

create TABLE json_test(
	id int not null primary key auto_increment,
	content json
	);

接下來，向test_json資料表中插入資料。

insert into json_test(content) values('{"name":"fanstuck","age":23,"address":{"province":"zhejiang","city":"hangzhou"}}')

可以使用“->”和“->>”查詢JSON資料中指定的內容。

SELECT content->'$.name' FROM json_test where id =1;

1.JSON_CONTAINS(json_doc,value)函數

JSON_CONTAINS(json_doc,value)函數查詢JSON型別的欄位中是否包含value資料。如果包含則返回1，否則返回0。其中，json_doc為JSON型別的資料，value為要查詢的資料。

SELECT JSON_CONTAINS(content, '{"name":"fanstuck"}') FROM json_test ;    

2.JSON_SEARCH()函數 

SELECT JSON_SEARCH(content ->> '$.address', 'all', 'nanchang') FROM json_test ;

3.JSON_PRETTY(json_doc)函數

JSON_PRETTY(json_doc)函數以標準的格式顯示JSON資料。

 SELECT JSON_PRETTY(content) FROM json_test ;

4.JSON_DEPTH(json_doc)函數

JSON_DEPTH(json_doc)函數返回JSON資料的最大深度。

 SELECT JSON_DEPTH(content) FROM json_test;

5.JSON_LENGTH(json_doc[,path])函數

JSON_LENGTH(json_doc[,path])函數返回JSON資料的長度。

SELECT JSON_LENGTH(content) FROM json_test;

6.JSON_KEYS(json_doc[,path])函數

JSON_KEYS(json_doc[,path])函數返回JSON資料中頂層key組成的JSON陣列。

 SELECT JSON_KEYS(content) FROM json_test;

7. JSON_INSERT(json_doc,path,val[,path,val] ...)函數

JSON_INSERT(json_doc,path,val[,path,val] ...)函數用於向JSON資料中插入資料。

{"age": 23, "name": "fanstuck", "address": {"ip": "192.168.12.12", "city": "hangzhou", "province": "zhejiang"}}

可以看到，JSON_INSERT()函數並沒有更新資料表中的資料，只是修改了顯示結果。

8.JSON_REMOVE(json_doc,path[,path] ...)函數

JSON_REMOVE(json_doc,path[,path] ...)函數用於移除JSON資料中指定key的資料。

 SELECT JSON_REMOVE(content, '$.address.city') FROM json_test WHERE id = 2;

9.JSON_REPLACE(json_doc,path,val[,path,val] ...)函數

JSON_REPLACE(json_doc,path,val[,path,val] ...)函數用於更新JSON資料中指定Key的資料。

SELECT JSON_REPLACE(content,'$.age',20) FROM json_test ;

 可以看到，JSON_REPLACE()函數並沒有更新資料表中的資料，只是修改了顯示結果。

10.JSON_SET(json_doc,path,val[,path,val] ...)函數

JSON_SET(json_doc,path,val[,path,val] ...)函數用於向JSON資料中插入資料。

 SELECT JSON_SET(content, '$.address.street', 'xxx街道') FROM json_test WHERE id = 1;

11.JSON_TYPE(json_val)函數

JSON_TYPE(json_val)函數用於返回JSON資料的JSON型別，MySQL中支援的JSON型別除了可以是MySQL中的資料型別外，還可以是OBJECT和ARRAY型別，其中OBJECT表示JSON物件，ARRAY表示JSON陣列。

 SELECT JSON_TYPE(content) FROM json_test ;

12. JSON_VALID(value)函數

JSON_VALID(value)函數用於判斷value的值是否是有效的JSON資料，如果是，則返回1，否則返回0，如果value的值為NULL，則返回NULL。

 SELECT JSON_VALID('{"name":"binghe"}'), JSON_VALID('name'), JSON_VALID(NULL);

到此這篇關於MySQL資料庫基礎學習之JSON函數各類操作詳解的文章就介紹到這了,更多相關MySQL JSON函數內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！