mysql中json基礎(chǔ)查詢詳解(附圖文)
mysq json 主要有JSON 對象（json object ）和JSON 數(shù)組（json array ）兩種類型
$表示整個json對象，在索引數(shù)據(jù)時用下標（對于json array，從0開始）或鍵值（對于json object，含有特殊字符的key要用"括起來，比如$.“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對象（json_object(key,value,key,value…)） | |
| 修改json | json_remove | 刪除元素（json_remove（json,path）） |
| json_merge | json合并（json_object同路徑值合并成數(shù)組） | |
| json_array_append | 在json數(shù)組的某個下標對應(yīng)元素中添加值 | |
| json_array_insert | 為json數(shù)組添加元素（json_array_insert(json,path,value)） | |
| 查詢json | json_quote | 將json轉(zhuǎn)成json字符串類型 |
| json_contains | 判斷是否包含某個json值 | |
| json_contains_path | 判斷某個路徑下是否包json值 | |
| json_extract | 提取json值 | |
| column->path | json_extract的簡潔寫法，MYSQL 5.7.9開始支持 | |
| column->>path | json_unquote(column -> path)的簡潔寫法 | |
| json_keys | 提取json中的鍵值jison數(shù)組 | |
| json_search | 按給定字符串關(guān)鍵字搜索 | |
| 其他 | json_valid | json格式校驗（校驗通過返回1，否則返回0） |
| json_type | json_type（返回OBJECT或ARRAY，當json格式錯誤，報錯） | |
| json_depth | json深度（到value最大深度） | |
| json_length | json長度（頂層元素個數(shù)） | |
| json_pretty | json格式化 |
1.1 查詢JSON
注： $表示整個json對象，
$.*為整個json文件下; $**.key表示JSON中所有key的值
1.1.1 json_contains
? json_contains 判斷是否包含某個，返回值0或1，0代表存在該值，1代表不存在該值。
? 規(guī)則: json_contains(json, 查詢值, 查詢路徑)，如果任何參數(shù)為NULL或路徑參數(shù)未標識目標文檔的某個部分，則返回NULL；
例1：
select json_contains('{"a": {"a":2,"b":1},"b":2}','2','$.b'); -- 既在json中 b這個key的路徑下存在為2的value,返回1
例2：
select json_contains('{"a": {"a":2,"b":1},"b":2}','2','$.a'); --在json中 $.a 這個key不存在2的這個vaule,因此返回0.（$代表整個JSON，$.a獲取數(shù)據(jù)為 {"a":1,"b":2} 這個JSON對象，而不存在單獨的 2 這個值，從而返回0）
select json_contains('{"a": {"a":2,"b":1},"b":2}','2','$.a.a'); -當取到正確的路徑下時，返回1
1.2 json_contains_path
? json_contains_path 判斷JSON文檔是都包含給定路徑上的數(shù)據(jù)
? 規(guī)則：json_contains_path(json, one_or_all, path(可多個))，如果任何參數(shù)為NULL，則返回NULL；如果JSON無法解析、或 one_or_all 不為 ‘one’ 或則 ‘all’、或任何路徑參數(shù)都不是有效的路徑表達式 ，則報錯。
? 1）‘one’：如果文檔中至少存在一個路徑，則為1，否則為0。
? 2）‘all’：如果文檔中存在所有路徑，則為1，否則為0。
例1：
select json_contains_path('{"a": {"a":2,"b":1},"b":2}','one','$.a') -- 在JSON中存在一個'$.a'的路徑，返回1
例2:
select json_contains_path('{"a": {"a":2,"b":1},"b":2}','one','$.e') -- 在JSON中不存在一個'$.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'）都要存在，才會返回1，否則返回0（'$.e'不存在）。
1.3 json_extract
json_extract 返回匹配路徑下的所有值，如果任何參數(shù)為NULL或沒有在文檔中找到該路徑，則返回NULL；若如果JSON無法解析、或路徑錯誤，則報錯。
? 規(guī)則：json_extract(json,path(可多個))，json可為 object 或 array 兩種類型，當json為object類型 時 路徑為 ‘$.key’ ，當json為array 類型時路徑為數(shù)組下標（從0開始）。
? 注：返回值由路徑參數(shù)匹配的所有值組成。如果這些參數(shù)可能返回多個值，則匹配的值將自動包裝為一個數(shù)組，其順序與生成它們的路徑相對應(yīng)。否則，返回值為單個匹配值。
例1：
select json_extract('{"a": {"a":2,"b":1},"b":2}','$.a') -- '$.a' 下對應(yīng)值是個json對象,則將整個對應(yīng)對象返回
例2：
select json_extract('{"a": {"a":2,"b":1},"b":2}','$.b') -- '$.a' 下對應(yīng)值是單個值,則返回對應(yīng)值
例3：
select json_extract('{"a": {"a":2,"b":1},"b":2}','$.*') -- $.* 代表整個json的任意路徑，即返回了$.a的數(shù)據(jù),又返回了$.b 的數(shù)據(jù)，返回了多個值，自動包裝成數(shù)組
例4：
select json_extract('{"a": {"a":2,"b":1},"b":2}','$.a','$.c') --可寫多個路徑，當路徑都不存在時，返回null
例5：
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') -- '$[1]'獲取下標為1的數(shù)據(jù)（起始下標為0）
例6：
select JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') -- path可為多個路徑，返回值與path的排序相同
例6：
select JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') -- $[index][index]...[index] 可多層數(shù)組下標查找數(shù)據(jù)，[*]表所有數(shù)據(jù)
例7：
select JSON_EXTRACT('[10, [20,[60]], [30, [40,50]]]', '$[*][1][0]') --[*] 可以放在任意位置作為通配表所有，'$[*][1][0]' 中 $[*]表示第一層查所有，第二層取第二個數(shù)，第三層取第一個數(shù)。可用于查詢最大層級數(shù)
例8：
SELECT JSON_EXTRACT('{"a": {"a":2,"b":1},"b":2,"c":1}', '$**.a'); -- '$**.a' 表示搜索整個JSON中的key為a的值，第一層{"a": 2, "b": 1},第二層取{"a":2,"b":1}中a的值2，組合包裝成數(shù)組返回
1.4 json_quote
json_quote 通過用雙引號字符 包裝 字符串 并轉(zhuǎn)義內(nèi)部引號和其他字符，返回結(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 相反（當啟用 NO_BACKSLASH_ESCAPES SQL模式時，會將反斜杠當作普通字符，導致無法正確解析，例\n 一般情況下表示換行，但是 啟用 NO_BACKSLASH_ESCAPES時，會被解析成n）
? 注：SELECT @@sql_mode; – 查看當前模式
例：
select json_unquote("{\"a\": {\"a\":2,\"b\":1},\"b\":2}")
1.6 column->path 和column->>path
? 5.7.9 版本之后，加入 -> 和 ->> 語法糖： ->相當于 JSON_EXTRACT(column,path)，存在轉(zhuǎn)義字符; ->> 相當于 json_unquote(JSON_EXTRACT(column,path ))。 注：column為表列，不能直接寫死值，若想以某值做測試，請子查詢作為臨時表
例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' 會報錯
1.7 json_keys
? json_keys 將JSON對象的頂層值中的鍵作為JSON數(shù)組返回，如果給定了路徑參數(shù)，則返回所選路徑中的頂層鍵（及第一層）。
? 規(guī)則：json_keys(json) 或json_keys(json,path) 。json_keys(json) 返回整個JSON的頂層鍵，json_keys(json,path) 返回對應(yīng)path下的頂層鍵。
? 1) 當json格式錯誤，或path中包含*或**時報錯。
? 2) 當path下級不包含key時，返回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}') --不寫path 返回整個JSON頂層鍵
1.8 json_search為空
json_search 返回JSON中給定字符串的路徑。若json為空、或search_str為空、或search_str在json中不存在、或path參數(shù)為NULL、或path不存在，則返回NULL；當json格式錯誤、one_or_all不是“one”或“all”、路徑表達式錯誤、escape_char不是常量表達式，則報錯
? 規(guī)則：JSON_SEARCH(json_doc，one_or_all，search_str [，escape_char [，path] …])
1.8.1 one_or_all
? 為all時，返回所有路徑匹配值，若多個路徑，則包裝成數(shù)組返回（數(shù)組元素的順序未定義），多個相同路徑只顯示一個。
? 為one時，搜索在第一次匹配后終止，并返回一個路徑字符串（先匹配哪個路徑是不能確定的）。
? 例：
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 返回一個vule為1的路徑
select json_search('{"a": {"a":"2","b":"1"},"b":"1"}','all','2',null,'$.a','$') -- '$'和'$.a' 返回路徑是同一個，不重復顯示
1.8.2 json中的值必須為字符串，否則找不到對應(yīng)路徑，返回null
? 例1：
select json_search('{"a": {"a":2,"b":1},"b":21}','all','21') --當json中值不為char類型，無法匹配，返回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個字符，’_ ‘表示匹配單個字符（因此在json中若需要匹配%或_的字符串，需提前轉(zhuǎn)義（編譯時就得轉(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]表示下標為2的，同理，也可將'$[2]' 替換成key
例2：
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL, '$');
-- '$' 表示整個json
例3：
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL,'$[*]');
-- '$[*]'表示整個數(shù)組 *表示通配
例4：
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL,'$**.k');
-- 路徑最后一級 key 為k 且值為10的 路徑
例5：
SELECT JSON_SEARCH('["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]', 'all', '10', NULL,'$[*][0].k'); --'$[*][0].k' 數(shù)組中第1個員素下key為k
總結(jié)
到此這篇關(guān)于mysql中json基礎(chǔ)查詢詳解的文章就介紹到這了,更多相關(guān)mysql json基礎(chǔ)查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
mysql-5.7.42升級到mysql-8.2.0(二進制方式)
隨著數(shù)據(jù)量的增長和業(yè)務(wù)需求的變更,我們可能需要升級MySQL,本文主要介紹了mysql-5.7.42升級到mysql-8.2.0(二進制方式),具有一定的參考價值,感興趣的可以了解一下2024-03-03
MySQL5.6.31 winx64.zip 安裝配置教程詳解
這篇文章主要介紹了MySQL5.6.31 winx64.zip 安裝配置教程詳解,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2017-02-02
Java數(shù)據(jù)類型與MySql數(shù)據(jù)類型對照表
這篇文章主要介紹了Java數(shù)據(jù)類型與MySql數(shù)據(jù)類型對照表,以表格形式分析了java與mysql對應(yīng)數(shù)據(jù)類型,并簡單講述了數(shù)據(jù)類型的選擇與使用方法,需要的朋友可以參考下2016-06-06
ktl工具實現(xiàn)mysql向mysql同步數(shù)據(jù)方法
在本篇內(nèi)容里我們給大家介紹了用ktl工具實現(xiàn)mysql向mysql同步數(shù)據(jù)的具體步驟，有需要的朋友們跟著學習參考下。2019-03-03
什么是事務(wù)處理呢？？事務(wù)處理是用來維護數(shù)據(jù)庫的完整性的，它保證成批的MySQL操作要么完全執(zhí)行，要么完成不執(zhí)行。2017-08-08
Navicat是一款流行的數(shù)據(jù)庫管理工具,而MySQL則是其中的一種數(shù)據(jù)庫軟件,下面這篇文章主要給大家介紹了關(guān)于Navicat使用報2059錯誤的兩種解決方案,需要的朋友可以參考下2023-11-11
在oracle數(shù)據(jù)庫中，有一個變量綁定的用法，很多人都比較熟悉，可以調(diào)高數(shù)據(jù)庫效率，應(yīng)對高并發(fā)等，好吧，這其中并不包括我，當同事問我MySQL中有沒有類似的寫法時，我是很茫然的，于是就上網(wǎng)查，找到了如下一種寫法2011-08-08
本文主要介紹了mysql中#{}和${}的區(qū)別，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-02-02
插件式存儲引擎是MySQL數(shù)據(jù)庫最重要的特征之一，用戶可以根據(jù)應(yīng)用的需要尋找如何存儲和索引數(shù)據(jù)、是否使用事務(wù)等。MySQL默認支持多種存儲引擎，以適用于不同領(lǐng)域的數(shù)據(jù)庫應(yīng)用需求，用戶可以通過選擇選擇不同的存儲引擎提供應(yīng)用的效率，提供靈活的存儲2017-02-02
詳解MySQL監(jiān)控工具 mysql-monitor
這篇文章主要介紹了解MySQL監(jiān)控工具 mysql-monitor的相關(guān)知識，本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友參考下吧2020-07-07