目錄

• 前言
• 一、JSON語法規則
• 二、JSON函數
• 1.JSON_CONTAINS(json_doc,value)函數
• 2.JSON_SEARCH()函數
• 3.JSON_PRETTY(json_doc)函數
• 4.JSON_DEPTH(json_doc)函數
• 5.JSON_LENGTH(json_doc[,path])函數
• 6.JSON_KEYS(json_doc[,path])函數
• 7. JSON_INSERT(json_doc,path,val[,path,val] ...)函數
• 8.JSON_REMOVE(json_doc,path[,path] ...)函數
• 9.JSON_REPLACE(json_doc,path,val[,path,val] ...)函數
• 10.JSON_SET(json_doc,path,val[,path,val] ...)函數
• 11.JSON_TYPE(json_val)函數
• 12. JSON_VALID(value)函數

前言

很多日常業務場景都會用到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函數內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！