mysql中json基礎(chǔ)查詢?cè)斀?附圖文)
mysq json 主要有JSON 對(duì)象（json object ）和JSON 數(shù)組（json array ）兩種類型
$表示整個(gè)json對(duì)象，在索引數(shù)據(jù)時(shí)用下標(biāo)（對(duì)于json array，從0開(kāi)始）或鍵值（對(duì)于json object，含有特殊字符的key要用"括起來(lái)，比如$.“my name”）。
1、mysql 相關(guān)JSON函數(shù)
| 分類 | 函數(shù) | 描述 |
|---|---|---|
| 創(chuàng)建json | json_array | 創(chuàng)建json數(shù)組 (json_array(數(shù)組元素)) |
| json_object | 創(chuàng)建json對(duì)象（json_object(key,value,key,value…)） | |
| 修改json | json_remove | 刪除元素（json_remove（json,path）） |
| json_merge | json合并（json_object同路徑值合并成數(shù)組） | |
| json_array_append | 在json數(shù)組的某個(gè)下標(biāo)對(duì)應(yīng)元素中添加值 | |
| json_array_insert | 為json數(shù)組添加元素（json_array_insert(json,path,value)） | |
| 查詢json | json_quote | 將json轉(zhuǎn)成json字符串類型 |
| json_contains | 判斷是否包含某個(gè)json值 | |
| json_contains_path | 判斷某個(gè)路徑下是否包json值 | |
| json_extract | 提取json值 | |
| column->path | json_extract的簡(jiǎn)潔寫(xiě)法，MYSQL 5.7.9開(kāi)始支持 | |
| column->>path | json_unquote(column -> path)的簡(jiǎn)潔寫(xiě)法 | |
| json_keys | 提取json中的鍵值jison數(shù)組 | |
| json_search | 按給定字符串關(guān)鍵字搜索 | |
| 其他 | json_valid | json格式校驗(yàn)（校驗(yàn)通過(guò)返回1，否則返回0） |
| json_type | json_type（返回OBJECT或ARRAY，當(dāng)json格式錯(cuò)誤，報(bào)錯(cuò)） | |
| json_depth | json深度（到value最大深度） | |
| json_length | json長(zhǎng)度（頂層元素個(gè)數(shù)） | |
| json_pretty | json格式化 |
1.1 查詢JSON
注： $表示整個(gè)json對(duì)象，
$.*為整個(gè)json文件下; $**.key表示JSON中所有key的值
1.1.1 json_contains
? json_contains 判斷是否包含某個(gè)，返回值0或1，0代表存在該值，1代表不存在該值。
? 規(guī)則: json_contains(json, 查詢值, 查詢路徑)，如果任何參數(shù)為NULL或路徑參數(shù)未標(biāo)識(shí)目標(biāo)文檔的某個(gè)部分，則返回NULL；
例1：
select json_contains('{"a": {"a":2,"b":1},"b":2}','2','$.b'); -- 既在json中 b這個(gè)key的路徑下存在為2的value,返回1
例2：
select json_contains('{"a": {"a":2,"b":1},"b":2}','2','$.a'); --在json中 $.a 這個(gè)key不存在2的這個(gè)vaule,因此返回0.（$代表整個(gè)JSON，$.a獲取數(shù)據(jù)為 {"a":1,"b":2} 這個(gè)JSON對(duì)象，而不存在單獨(dú)的 2 這個(gè)值，從而返回0）
select json_contains('{"a": {"a":2,"b":1},"b":2}','2','$.a.a'); -當(dāng)取到正確的路徑下時(shí)，返回1
1.2 json_contains_path
? json_contains_path 判斷JSON文檔是都包含給定路徑上的數(shù)據(jù)
? 規(guī)則：json_contains_path(json, one_or_all, path(可多個(gè)))，如果任何參數(shù)為NULL，則返回NULL；如果JSON無(wú)法解析、或 one_or_all 不為 ‘one’ 或則 ‘all’、或任何路徑參數(shù)都不是有效的路徑表達(dá)式 ，則報(bào)錯(cuò)。
? 1）‘one’：如果文檔中至少存在一個(gè)路徑，則為1，否則為0。
? 2）‘all’：如果文檔中存在所有路徑，則為1，否則為0。
例1：
select json_contains_path('{"a": {"a":2,"b":1},"b":2}','one','$.a') -- 在JSON中存在一個(gè)'$.a'的路徑，返回1
例2:
select json_contains_path('{"a": {"a":2,"b":1},"b":2}','one','$.e') -- 在JSON中不存在一個(gè)'$.e'的路徑，返回0
例3：
select json_contains_path('{"a": {"a":2,"b":1},"b":2}','one','$.a','$.e') -- 'one'表示在JSON中，存在'$.a','$.e'任意一路徑，則返回1。
例4：
select json_contains_path('{"a": {"a":2,"b":1},"b":2}','all','$.a','$.e') -- 'all'表示后續(xù)路徑（'$.a','$.e'）都要存在，才會(huì)返回1，否則返回0（'$.e'不存在）。
1.3 json_extract
json_extract 返回匹配路徑下的所有值，如果任何參數(shù)為NULL或沒(méi)有在文檔中找到該路徑，則返回NULL；若如果JSON無(wú)法解析、或路徑錯(cuò)誤，則報(bào)錯(cuò)。
? 規(guī)則：json_extract(json,path(可多個(gè)))，json可為 object 或 array 兩種類型，當(dāng)json為object類型 時(shí) 路徑為 ‘$.key’ ，當(dāng)json為array 類型時(shí)路徑為數(shù)組下標(biāo)（從0開(kāi)始）。
? 注：返回值由路徑參數(shù)匹配的所有值組成。如果這些參數(shù)可能返回多個(gè)值，則匹配的值將自動(dòng)包裝為一個(gè)數(shù)組，其順序與生成它們的路徑相對(duì)應(yīng)。否則，返回值為單個(gè)匹配值。
例1：
select json_extract('{"a": {"a":2,"b":1},"b":2}','$.a') -- '$.a' 下對(duì)應(yīng)值是個(gè)json對(duì)象,則將整個(gè)對(duì)應(yīng)對(duì)象返回
例2：
select json_extract('{"a": {"a":2,"b":1},"b":2}','$.b') -- '$.a' 下對(duì)應(yīng)值是單個(gè)值,則返回對(duì)應(yīng)值
例3：
select json_extract('{"a": {"a":2,"b":1},"b":2}','$.*') -- $.* 代表整個(gè)json的任意路徑，即返回了$.a的數(shù)據(jù),又返回了$.b 的數(shù)據(jù)，返回了多個(gè)值，自動(dòng)包裝成數(shù)組
例4：
select json_extract('{"a": {"a":2,"b":1},"b":2}','$.a','$.c') --可寫(xiě)多個(gè)路徑，當(dāng)路徑都不存在時(shí)，返回null
例5：
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') -- '$[1]'獲取下標(biāo)為1的數(shù)據(jù)（起始下標(biāo)為0）
例6：
select JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') -- path可為多個(gè)路徑，返回值與path的排序相同
例6：
select JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') -- $[index][index]...[index] 可多層數(shù)組下標(biāo)查找數(shù)據(jù)，[*]表所有數(shù)據(jù)
例7：
select JSON_EXTRACT('[10, [20,[60]], [30, [40,50]]]', '$[*][1][0]') --[*] 可以放在任意位置作為通配表所有，'$[*][1][0]' 中 $[*]表示第一層查所有，第二層取第二個(gè)數(shù)，第三層取第一個(gè)數(shù)?？捎糜诓樵冏畲髮蛹?jí)數(shù)
例8：
SELECT JSON_EXTRACT('{"a": {"a":2,"b":1},"b":2,"c":1}', '$**.a'); -- '$**.a' 表示搜索整個(gè)JSON中的key為a的值，第一層{"a": 2, "b": 1},第二層取{"a":2,"b":1}中a的值2，組合包裝成數(shù)組返回
1.4 json_quote
json_quote 通過(guò)用雙引號(hào)字符 包裝 字符串 并轉(zhuǎn)義內(nèi)部引號(hào)和其他字符，返回結(jié)果為utf8mb4字符串。
例：
select json_quote('{"a": {"a":2,"b":1},"b":2}') -- 外層'轉(zhuǎn)換成" ，內(nèi)層字符轉(zhuǎn)義，如"轉(zhuǎn)\"
1.5 json_unquote
json_unquote 轉(zhuǎn)義解析，與json_quote 相反（當(dāng)啟用 NO_BACKSLASH_ESCAPES SQL模式時(shí)，會(huì)將反斜杠當(dāng)作普通字符，導(dǎo)致無(wú)法正確解析，例\n 一般情況下表示換行，但是 啟用 NO_BACKSLASH_ESCAPES時(shí)，會(huì)被解析成n）
? 注：SELECT @@sql_mode; – 查看當(dāng)前模式
例：
select json_unquote("{\"a\": {\"a\":2,\"b\":1},\"b\":2}")
1.6 column->path 和column->>path
? 5.7.9 版本之后，加入 -> 和 ->> 語(yǔ)法糖： ->相當(dāng)于 JSON_EXTRACT(column,path)，存在轉(zhuǎn)義字符; ->> 相當(dāng)于 json_unquote(JSON_EXTRACT(column,path ))。 注：column為表列，不能直接寫(xiě)死值，若想以某值做測(cè)試，請(qǐng)子查詢作為臨時(shí)表
例1：
例2：
select t.co ->>'$.a' from
(select '{"a": {"a":2,"b":1},"b":2}' ->> as 'co') t --注 select '{"a": {"a":2,"b":1},"b":2}' ->>'$.a' 會(huì)報(bào)錯(cuò)
1.7 json_keys
? json_keys 將JSON對(duì)象的頂層值中的鍵作為JSON數(shù)組返回，如果給定了路徑參數(shù)，則返回所選路徑中的頂層鍵（及第一層）。
? 規(guī)則：json_keys(json) 或json_keys(json,path) 。json_keys(json) 返回整個(gè)JSON的頂層鍵，json_keys(json,path) 返回對(duì)應(yīng)path下的頂層鍵。
? 1) 當(dāng)json格式錯(cuò)誤，或path中包含*或**時(shí)報(bào)錯(cuò)。
? 2) 當(dāng)path下級(jí)不包含key時(shí)，返回null
? 3) json 必須是object 類型
例1：
select json_keys('{"a": {"a":2,"b":1},"b":2}','$.b') -- '$.b' 下不包含key,返回null
例2：
select json_keys('{"a": {"a":2,"b":1},"b":2}') --不寫(xiě)path 返回整個(gè)JSON頂層鍵
1.8 json_search為空
json_search 返回JSON中給定字符串的路徑。若json為空、或search_str為空、或search_str在json中不存在、或path參數(shù)為NULL、或path不存在，則返回NULL；當(dāng)json格式錯(cuò)誤、one_or_all不是“one”或“all”、路徑表達(dá)式錯(cuò)誤、escape_char不是常量表達(dá)式，則報(bào)錯(cuò)
? 規(guī)則：JSON_SEARCH(json_doc，one_or_all，search_str [，escape_char [，path] …])
1.8.1 one_or_all
? 為all時(shí)，返回所有路徑匹配值，若多個(gè)路徑，則包裝成數(shù)組返回（數(shù)組元素的順序未定義），多個(gè)相同路徑只顯示一個(gè)。
? 為one時(shí)，搜索在第一次匹配后終止，并返回一個(gè)路徑字符串（先匹配哪個(gè)路徑是不能確定的）。
? 例：
select json_search('{"a": {"a":2,"b":"1"},"b":"1"}','all','1') -- all返回所有匹配路徑
select json_search('{"a": {"a":2,"b":"1"},"b":"1"}','one','1') -- one 返回一個(gè)vule為1的路徑
select json_search('{"a": {"a":"2","b":"1"},"b":"1"}','all','2',null,'$.a','$') -- '$'和'$.a' 返回路徑是同一個(gè)，不重復(fù)顯示
1.8.2 json中的值必須為字符串，否則找不到對(duì)應(yīng)路徑，返回null
? 例1：
select json_search('{"a": {"a":2,"b":1},"b":21}','all','21') --當(dāng)json中值不為char類型，無(wú)法匹配，返回null
例2：
select json_search('{"a": {"a":2,"b":1},"b":"21"}','all','21') -- json中存在的值為21的數(shù)據(jù)，返回其路徑
1.8.3 模糊匹配
? 與sql中l(wèi)ike用法相似，’% ‘表示匹配0~n個(gè)字符，’_ ‘表示匹配單個(gè)字符（因此在json中若需要匹配%或_的字符串，需提前轉(zhuǎn)義（編譯時(shí)就得轉(zhuǎn)）加\反斜杠）。
例1：
select json_search('{"a": {"a":"123","b":"345"},"b":"12"}','all','12%') --%匹配
例2：
select json_search('{"a": {"a":"123","b":"345"},"b":"12"}','all','12_')
例3：
select json_search('{"a": {"a":"123","b":"345"},"b":"12%"}','all','%\%%') -- \% 模糊查找%
1.8.4 其他
? 帶路徑匹配：
例1：
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '%b%', NULL, '$[2]'); -- $[2]表示下標(biāo)為2的，同理，也可將'$[2]' 替換成key
例2：
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL, '$');
-- '$' 表示整個(gè)json
例3：
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL,'$[*]');
-- '$[*]'表示整個(gè)數(shù)組 *表示通配
例4：
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL,'$**.k');
-- 路徑最后一級(jí) key 為k 且值為10的 路徑
例5：
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL,'$[*][0].k'); --'$[*][0].k' 數(shù)組中第1個(gè)員素下key為k
總結(jié)
到此這篇關(guān)于mysql中json基礎(chǔ)查詢?cè)斀獾奈恼戮徒榻B到這了,更多相關(guān)mysql json基礎(chǔ)查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
坑人的Mysql5.7問(wèn)題(默認(rèn)不支持Group By語(yǔ)句)
這篇文章主要介紹了坑人的Mysql5.7問(wèn)題(默認(rèn)不支持Group By語(yǔ)句),具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-10-10
MySQL數(shù)據(jù)遷移相關(guān)總結(jié)
這篇文章主要介紹了MySQL數(shù)據(jù)遷移的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)使用MySQL數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下2021-04-04
MySQL中根據(jù)binlog日志進(jìn)行恢復(fù)的實(shí)現(xiàn)
MySQL的binlog功能為數(shù)據(jù)庫(kù)的恢復(fù)和故障排查提供了有力支持,本文主要介紹了MySQL中根據(jù)binlog日志進(jìn)行恢復(fù)的實(shí)現(xiàn),具有一定的參考價(jià)值,感興趣的可以了解一下2025-04-04
這篇文章主要介紹了MySQL中的SUM函數(shù)使用教程,是MySQL入門學(xué)習(xí)中的基礎(chǔ)知識(shí),需要的朋友可以參考下2015-05-05
這篇文章主要給大家介紹了關(guān)于MySQL中or、in、union與索引優(yōu)化的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-12-12
關(guān)于數(shù)據(jù)庫(kù)連接池Druid使用說(shuō)明
這篇文章主要介紹了關(guān)于數(shù)據(jù)庫(kù)連接池Druid使用說(shuō)明，涉及Druid 參數(shù)和運(yùn)行原理等等相關(guān)內(nèi)容，小編覺(jué)得挺不錯(cuò)的，在這里給大家分享一下。2017-10-10
mysql中ALTER CONVERSION使用場(chǎng)景
本文主要介紹了mysql中ALTER CONVERSION使用場(chǎng)景,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2025-05-05
本文主要介紹了MySQL阻塞與死鎖的解決,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-09-09
MySQL全局遍歷替換特征字符串的實(shí)現(xiàn)方法
本文主要介紹了MySQL全局遍歷替換特征字符串的實(shí)現(xiàn)方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-03-03
MySQL查詢隨機(jī)數(shù)據(jù)的4種方法和性能對(duì)比
從MySQL隨機(jī)選取數(shù)據(jù)也是我們最常用的一種發(fā)發(fā)，其最簡(jiǎn)單的辦法就是使用”O(jiān)RDER BY RAND()”,本文介紹了包括ORDER BY RAND()的4種獲取隨機(jī)數(shù)據(jù)的方法，并分析了各自的優(yōu)缺點(diǎn)。2014-04-04