mysql中json基礎查詢詳解(附圖文)
mysq json 主要有JSON 對象（json object ）和JSON 數(shù)組（json array ）兩種類型
$表示整個json對象，在索引數(shù)據(jù)時用下標（對于json array，從0開始）或鍵值（對于json object，含有特殊字符的key要用"括起來，比如$.“my name”）。
1、mysql 相關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ù)組的某個下標對應元素中添加值 | |
| json_array_insert | 為json數(shù)組添加元素（json_array_insert(json,path,value)） | |
| 查詢json | json_quote | 將json轉成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 | 按給定字符串關鍵字搜索 | |
| 其他 | 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ù)組，其順序與生成它們的路徑相對應。否則，返回值為單個匹配值。
例1：
select json_extract('{"a": {"a":2,"b":1},"b":2}','$.a') -- '$.a' 下對應值是個json對象,則將整個對應對象返回
例2：
select json_extract('{"a": {"a":2,"b":1},"b":2}','$.b') -- '$.a' 下對應值是單個值,則返回對應值
例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 通過用雙引號字符 包裝 字符串 并轉義內部引號和其他字符，返回結果為utf8mb4字符串。
例：
select json_quote('{"a": {"a":2,"b":1},"b":2}') -- 外層'轉換成" ，內層字符轉義，如"轉\"
1.5 json_unquote
json_unquote 轉義解析，與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)，存在轉義字符; ->> 相當于 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) 返回對應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中的值必須為字符串，否則找不到對應路徑，返回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中若需要匹配%或_的字符串，需提前轉義（編譯時就得轉）加\反斜杠）。
例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
總結
到此這篇關于mysql中json基礎查詢詳解的文章就介紹到這了,更多相關mysql json基礎查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
行級鎖加鎖規(guī)則比較復雜,不同的場景,加鎖的形式是不同的,本文主要介紹了深入理解MySQL中的行級鎖,具有一定的參考價值,感興趣的可以了解一下2024-01-01
在分布式數(shù)據(jù)庫架構中,Mycat?作為一個中間件,提供了強大的數(shù)據(jù)分片和路由能力,本文將詳細介紹如何在?Mycat?中配置多個?Schema,以滿足不同業(yè)務模塊的數(shù)據(jù)隔離需求,希望對大家有所幫助2025-05-05
這篇文章主要給大家介紹了關于mysql查詢控制語句的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-04-04
MySQL數(shù)據(jù)庫事務transaction示例講解教程
這篇文章主要為大家介紹了MySQL數(shù)據(jù)庫事務transaction的示例講解教程，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步2021-10-10
mysql 5.7.11 winx64.zip安裝配置方法圖文教程
這篇文章主要為大家分享了mysql5.7.11安裝配置方法圖文教程，具有一定的參考價值，感興趣的朋友可以參考一下2017-02-02
文章總結：作者分享了在解決MySQL中根據(jù)發(fā)票ID和單位統(tǒng)計單位數(shù)量的問題時遇到的困難及解決方法,通過使用IFNULL()函數(shù)和CASEWHEN都無法解決問題,最終作者選擇了嵌套循環(huán)的方法來實現(xiàn)需求,并總結了經(jīng)驗以供參考2024-11-11
mysql分組后如何獲取每個組的第一條數(shù)據(jù)
這篇文章主要介紹了mysql分組后如何獲取每個組的第一條數(shù)據(jù)問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08
本篇文章是對MySQL Master Slave 同步配置進行了詳細的分析介紹，需要的朋友參考下2013-06-06
MySQL根據(jù)條件遷移數(shù)據(jù)的幾種方法實現(xiàn)
本文主要介紹了MySQL根據(jù)條件遷移數(shù)據(jù)的實現(xiàn),包括使用INSERT INTO SELECT、UPDATE、DELETE語句以及結合子查詢進行遷移,具有一定的參考價值,感興趣的可以了解一下2024-02-02
MySQL連表更新實現(xiàn)高效數(shù)據(jù)同步的實戰(zhàn)指南
在數(shù)據(jù)庫開發(fā)中,連表更新（JOIN UPDATE）是一種常見且強大的操作,它允許我們基于關聯(lián)表的數(shù)據(jù)來更新目標表,本文將深入探討MySQL連表更新的語法、應用場景、性能優(yōu)化及常見陷阱,幫助開發(fā)者掌握這一核心技能,需要的朋友可以參考下2026-02-02