MYSQL中的json數(shù)據(jù)操作代碼
MYSQL中的json數(shù)據(jù)操作
1.2 基礎(chǔ)查詢操作
用法提示:
- 如果
json字符串不是數(shù)組,則直接使用$.字段名 - 如果
json字符串是數(shù)組[Array],則直接使用$[對應(yīng)元素的索引id]
1.2.1 一般json查詢
使用json字段名->’$.json屬性’進(jìn)行查詢條件,注意:如果 ‘->’ 不能用也可用 ‘->>’ 查詢
舉個例子:如果想查詢deptLeader=張五的數(shù)據(jù),那么sql語句如下:
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';
1.2.2 多個條件查詢
比如想查dept為“部門3”和deptLeaderId=5的數(shù)據(jù),sql如下:
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';
1.2.3 json中多個字段關(guān)系查詢
比如想查詢json格式中deptLeader=張五和deptId=5的數(shù)據(jù)
SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';
1.2.4 關(guān)聯(lián)表查詢
這里我們要連表查詢在dept 表中部門leader在dept_leader 中的詳情
SELECT * from dept,dept_leader WHERE dept.json_value->'$.deptLeaderId'=dept_leader.json_value->'$.id' ;
1.3 JSON函數(shù)操作
寫到這里大家都發(fā)現(xiàn)了,我們查詢的json都是整條json數(shù)據(jù),這樣看起來不是很方便,那么如果我們只想看json中的某個字段怎么辦?
1.3.1 官方j(luò)son函數(shù)
| Name | Description | 解釋 |
|---|---|---|
| -> | Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT() | 計(jì)算路徑后返回JSON列的值;相當(dāng)于JSON_EXTRACT () |
| ->> | Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()). | 從JSON列返回值后,就算路徑和取消引號的結(jié)果;相當(dāng)于JSON_UNQUOTE (JSON_EXTRACT ()) |
| JSON_ARRAY() | Create JSON array | 創(chuàng)建JSON數(shù)組 |
| JSON_ARRAY_APPEND() | Append data to JSON document | 向JSON文檔追加數(shù)據(jù) |
| JSON_ARRAY_INSERT() | Insert into JSON array | 插入JSON數(shù)組 |
| JSON_CONTAINS() | Whether JSON document contains specific object at path | JSON文檔是否包含路徑上的特定對象 |
| JSON_CONTAINS_PATH() | Whether JSON document contains any data at path | JSON文檔是否在路徑上包含任何數(shù)據(jù) |
| JSON_DEPTH() | Maximum depth of JSON document | JSON文檔的最大深度 |
| JSON_EXTRACT() | Return data from JSON document | 從JSON文檔返回?cái)?shù)據(jù) |
| JSON_INSERT() | Insert data into JSON document | 將數(shù)據(jù)插入JSON文檔 |
| JSON_KEYS() | Array of keys from JSON document | 來自JSON文檔的鍵數(shù)組 |
| JSON_LENGTH() | Number of elements in JSON document | JSON文檔中的元素?cái)?shù)量 |
| JSON_MERGE_PATCH() | Merge JSON documents, replacing values of duplicate keys | 合并JSON文檔,替換重復(fù)鍵的值 |
| JSON_MERGE_PRESERVE() | Merge JSON documents, preserving duplicate keys | 合并JSON文檔,保留重復(fù)的密鑰 |
| JSON_OBJECT() | Create JSON object | 創(chuàng)建JSON對象 |
| JSON_OVERLAPS() | Compares two JSON documents, returns TRUE (1) if these have any key-value pairs or array elements in common, otherwise FALSE (0) | 比較兩個JSON文檔,如果它們有共同的鍵值對或數(shù)組元素,則返回TRUE(1),否則返回FALSE (0) |
| JSON_PRETTY() | Print a JSON document in human-readable format | 以人類可讀的格式打印JSON文檔 |
| JSON_QUOTE() | Quote JSON document | 引用JSON文檔 |
| JSON_REMOVE() | Remove data from JSON document | 從JSON文檔中刪除數(shù)據(jù) |
| JSON_REPLACE() | Replace values in JSON document | 替換JSON文檔中的值 |
| JSON_SCHEMA_VALID() | Validate JSON document against JSON schema; returns TRUE/1 if document validates against schema, or FALSE/0 if it does not | 針對JSON模式驗(yàn)證JSON文檔;如果文檔針對模式進(jìn)行驗(yàn)證,則返回TRUE/1,否則返回FALSE/0 |
| JSON_SCHEMA_VALIDATION_REPORT() | Validate JSON document against JSON schema; returns report in JSON format on outcome on validation including success or failure and reasons for failure | 針對JSON模式驗(yàn)證JSON文檔;以JSON格式返回關(guān)于驗(yàn)證結(jié)果的報(bào)告,包括成功或失敗以及失敗原因 |
| JSON_SEARCH() | Path to value within JSON document | JSON文檔中值的路徑 |
| JSON_SET() | Insert data into JSON document | 將數(shù)據(jù)插入JSON文檔 |
| JSON_STORAGE_FREE() | Freed space within binary representation of JSON column value following partial update | 在部分更新后釋放JSON列值的二進(jìn)制表示形式中的空間 |
| JSON_STORAGE_SIZE() | pace used for storage of binary representation of a JSON document | 用于存儲JSON文檔的二進(jìn)制表示的空間 |
| JSON_TABLE() | Return data from a JSON expression as a relational table | 以關(guān)系表的形式從JSON表達(dá)式返回?cái)?shù)據(jù) |
| JSON_TYPE() | Type of JSON value | JSON值類型 |
| JSON_UNQUOTE() | Unquote JSON value | 不引用JSON值 |
| JSON_VALID() | Whether JSON value is valid | JSON值是否有效 |
| JSON_VALUE() | Extract value from JSON document at location pointed to by path provided; return this value as VARCHAR(512) or specified type | 根據(jù)所提供的路徑從JSON文檔中所指向的位置提取值;返回該值為VARCHAR(512)或指定的類型 |
| MEMBER OF() | Returns true (1) if first operand matches any element of JSON array passed as second operand, otherwise returns false (0) | 如果第一個操作數(shù)匹配作為第二個操作數(shù)的JSON數(shù)組中的任何元素,則返回true(1),否則返回false (0) |
1.3.2 ->、->>區(qū)別
->在field中使用的時(shí)候結(jié)果帶引號,->>的結(jié)果不帶引號
1.3.2.2 在where條件中使用
特別注意:->當(dāng)做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格式數(shù)據(jù)是否在字段中包含特定對象
用法: JSON_CONTAINS(target, candidate[, path])
事例:如果我們想查詢包含deptName=部門5的對象
select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部門5"))
1.3.5 SON_OBJECT():將一個鍵值對列表轉(zhuǎn)換成json對象
比如我們想查詢某個對象里面的值等于多少
比如我們添加這么一組數(shù)據(jù)到dept表中:
insert into dept VALUES(6,'部門9','{"deptName": {"dept":"de","depp":"dd"}, "deptId": "5", "deptLeaderId": "5"}');我們可以看到deptName中還有一個對象,里面還有dept和depp兩個屬性字段,那么我們應(yīng)該怎么查詢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():創(chuàng)建JSON數(shù)組
比如我們添加這么一組數(shù)據(jù)到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的數(shù)據(jù)
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文檔中的鍵數(shù)組
用法:JSON_KEYS(json_value)
事例:比如我們想查詢json格式數(shù)據(jù)中的所有key
SELECT JSON_KEYS(json_value) FROM dept
接下來的3種函數(shù)都是新增數(shù)據(jù)類型的:
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():將數(shù)據(jù)插入JSON格式中,有key則替換,無key則新增
這也是我們開發(fā)過程中經(jīng)常會用到的一個函數(shù)
用法:JSON_SET(json_doc, path, val[, path, val] …)
事例:比如我們想針對id=2的數(shù)據(jù)新增一組:newData:新增的數(shù)據(jù),修改deptName為新增的部門1
sql語句如下:
update dept set json_value=JSON_SET('{"deptName": "部門2", "deptId": "2", "deptLeaderId": "4"}','$.deptName','新增的部門1','$.newData','新增的數(shù)據(jù)') WHERE id=2;
注意:json_doc如果不帶這個單元格之前的值,之前的值是會新值被覆蓋的,比如我們?nèi)绻碌恼Z句換成:
update dept set json_value=JSON_SET('{"a":"1","b":"2"}','$.deptName','新增的部門1','$.newData','新增的數(shù)據(jù)') WHERE id=2我們可以看到這里json_doc是{“a”:“1”,“b”:“2”},這樣的話會把之前的單元格值覆蓋后再新增/覆蓋這個單元格字段
1.3.10 JSON_INSERT():插入值(往json中插入新值,但不替換已經(jīng)存在的舊值)
用法:JSON_INSERT(json_doc, path, val[, path, val] …)
事例:
UPDATE dept set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', '新增的部門2','$.newData2','新增的數(shù)據(jù)2')
WHERE id=2我們可以看到由于json_doc變化將之前的值覆蓋了,新增了deptName和newData2.
如果我們再執(zhí)行以下剛才的那個sql,只是換了value,我們會看到里面的key值不會發(fā)生變化。
因?yàn)檫@個函數(shù)只負(fù)責(zé)往json中插入新值,但不替換已經(jīng)存在的舊值。
1.3.11 JSON_REPLACE()
用法:JSON_REPLACE(json_doc, path, val[, path, val] …)
用例:
如果我們要更新id=2數(shù)據(jù)中newData2的值為:更新的數(shù)據(jù)2
sql語句如下:
UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "新增的部門2", "newData2": "新增的數(shù)據(jù)2"}', '$.newData2', '更新的數(shù)據(jù)2') WHERE id =2;
1.3.12 JSON_REMOVE():從JSON文檔中刪除數(shù)據(jù)
用法:JSON_REMOVE(json_doc, path[, path] …)
舉例:刪除key為a的字段。
UPDATE dept set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": "新增的部門2", "newData2": "更新的數(shù)據(jù)2"}','$.a') WHERE id =2;
到此這篇關(guān)于MYSQL中的json數(shù)據(jù)操作的文章就介紹到這了,更多相關(guān)mysql json數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
關(guān)于case when語句的報(bào)錯問題詳解
SQL CASE 表達(dá)式是一種通用的條件表達(dá)式,類似于其它語言中的 if/else 語句,下面這篇文章主要給大家介紹了關(guān)于case when語句的報(bào)錯問題的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2018-08-08
mysql 隊(duì)列 實(shí)現(xiàn)并發(fā)讀
隊(duì)列是常用的數(shù)據(jù)結(jié)構(gòu),基本特點(diǎn)就是先入先出,在事務(wù)處理等方面都要用到它,有的時(shí)候是帶有優(yōu)先級的隊(duì)列。當(dāng)隊(duì)列存在并發(fā)訪問的時(shí)候,比如多線程情況下,就需要鎖機(jī)制來保證隊(duì)列中的同一個元素不被多次獲取2012-04-04
Mysql?COUNT()函數(shù)基本用法及應(yīng)用詳解
這篇文章主要介紹了Mysql?COUNT()函數(shù)基本用法及應(yīng)用的相關(guān)資料,COUNT()函數(shù)是SQL中常用的聚合函數(shù),用于統(tǒng)計(jì)滿足特定條件的記錄數(shù),它可以靈活地應(yīng)用于各種查詢場景,幫助用戶快速獲取所需的數(shù)據(jù)統(tǒng)計(jì)信息,需要的朋友可以參考下2024-12-12
net?start?mysql服務(wù)名無效的三種解決方法
這篇文章主要介紹了net?start?mysql服務(wù)名無效的三種解決方法,通過圖文結(jié)合的方式講解的非常詳細(xì),對大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2024-08-08
mysql 8.0.15 winx64解壓版安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了mysql 8.0.15 winx64解壓版安裝配置方法圖文教程,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2019-02-02

