mysql中json_extract的具體使用
更新時間：2024年05月31日 11:04:52 作者：IT楓斗者
mysql5.7版本開始支持JSON類型字段,本文主要介紹了mysql中json_extract的具體使用,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
前言
mysql5.7版本開始支持JSON類型字段，本文詳細介紹json_extract函數(shù)如何獲取mysql中的JSON類型數(shù)據(jù)
json_extract可以完全簡寫為 ->
json_unquote(json_extract())可以完全簡寫為 ->>
下面介紹中大部分會利用簡寫
創(chuàng)建示例表
CREATE TABLE `test_json` ( `id` int(11) NOT NULL AUTO_INCREMENT, `content` json DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
# 插入兩條測試用的記錄
INSERT INTO `test_json` (`content`) VALUES ('{\"name\":\"tom\",\"age\":18,\"score\":[100,90,87],\"address\":{\"province\":\"湖南\",\"city\":\"長沙\"}}');
INSERT INTO `test_json` (`content`) VALUES ('[1, "apple", "red", {"age": 18, "name": "tom"}]');| id | content |
|---|---|
| 1 | {“age”: 18, “name”: “tom”, “score”: [100, 90, 87], “address”: {“city”: “長沙”, “province”: “湖南”}} |
| 2 | [1, “apple”, “red”, {“age”: 18, “name”: “tom”}] |
基本語法
獲取JSON對象中某個key對應的value值
json_extract函數(shù)中，第一個參數(shù)content表示json數(shù)據(jù)，第二個參數(shù)為json路徑，其中 表示該 j s o n 數(shù)據(jù)本身， 表示該json數(shù)據(jù)本身， 表示該json數(shù)據(jù)本身，.name就表示獲取json中key為name的value值
可以利用 -> 表達式來代替json_extract
若獲取的val本身為字符串，那么獲取的val會被引號包起來，比如"tom"，這種數(shù)據(jù)被解析到程序對象中時，可能會被轉義為\“tom\”。為了解決這個問題了，可以在外面再包上一層json_unquote函數(shù)，或者使用 ->> 代替->
content:
{“age”: 18, “name”: “tom”, “score”: [100, 90, 87], “address”: {“city”: “長沙”, “province”: “湖南”}}
# 得到"tom" select json_extract(content,'$.name') from test_json where id = 1; # 簡寫方式：字段名->表達式等價于json_extract(字段名,表達式) select content->'$.name' from test_json where id = 1; # 結果: +--------------------------------+ | json_extract(content,'$.name') | +--------------------------------+ | "tom" | +--------------------------------+ +-------------------+ | content->'$.name' | +-------------------+ | "tom" | +-------------------+ # 解除雙引號，得到tom select json_unquote(json_extract(content,'$.name')) from test_json where id = 1; # 簡寫方式：字段名->>表達式等價于json_unquote(json_extract(字段名,表達式)) select content->>'$.name' from test_json where id = 1; # 結果: +----------------------------------------------+ | json_unquote(json_extract(content,'$.name')) | +----------------------------------------------+ | tom | +----------------------------------------------+ +--------------------+ | content->>'$.name' | +--------------------+ | tom | +--------------------+
獲取JSON數(shù)組中某個元素
json_extract函數(shù)中，第一個參數(shù)content表示json數(shù)據(jù)，第二個參數(shù)為json路徑，其中 表示該 j s o n 數(shù)據(jù)本身， 表示該json數(shù)據(jù)本身， 表示該json數(shù)據(jù)本身，[i]表示獲取該json數(shù)組索引為i的元素（索引從0開始）
與獲取key-val一樣，若獲取的元素為字符串，默認的方式也會得到雙引號包起來的字符，導致程序轉義，方法也是利用json_unquote函數(shù)，或者使用 ->> 代替->
content:
[1, “apple”, “red”, {“age”: 18, “name”: “tom”}]
# 得到"apple" select json_extract(content,'$[1]') from test_json where id = 2; # 簡寫，效果同上 select content->'$[1]' from test_json where id = 2; # 結果: +------------------------------+ | json_extract(content,'$[1]') | +------------------------------+ | "apple" | +------------------------------+ +-----------------+ | content->'$[1]' | +-----------------+ | "apple" | +-----------------+ # 解除雙引號，得到apple select json_unquote(json_extract(content,'$[1]')) from test_json where id = 2; # 簡寫，效果同上 select content->>'$[1]' from test_json where id = 2; # 結果: +--------------------------------------------+ | json_unquote(json_extract(content,'$[1]')) | +--------------------------------------------+ | apple | +--------------------------------------------+ +------------------+ | content->>'$[1]' | +------------------+ | apple | +------------------+
獲取JSON中的嵌套數(shù)據(jù)
結合前面介紹的兩種獲取方式，可以獲取json數(shù)據(jù)中的嵌套數(shù)據(jù)
content: id=1
{“age”: 18, “name”: “tom”, “score”: [100, 90, 87], “address”: {“city”: “長沙”, “province”: “湖南”}}
content: id=2
[1, “apple”, “red”, {“age”: 18, “name”: “tom”}]
# 得到:87 select content->'$.score[2]' from test_json where id = 1; # 結果: +-----------------------+ | content->'$.score[2]' | +-----------------------+ | 87 | +-----------------------+ # 得到:18 select content->'$[3].age' from test_json where id = 2; # 結果: +---------------------+ | content->'$[3].age' | +---------------------+ | 18 | +---------------------+
漸入佳境
獲取JSON多個路徑的數(shù)據(jù)
將會把多個路徑的數(shù)據(jù)組合成數(shù)組返回
content: id=1
{“age”: 18, “name”: “tom”, “score”: [100, 90, 87], “address”: {“city”: “長沙”, “province”: “湖南”}}
select json_extract(content,'$.age','$.score') from test_json where id = 1; # 結果: +-----------------------------------------+ | json_extract(content,'$.age','$.score') | +-----------------------------------------+ | [18, [100, 90, 87]] | +-----------------------------------------+ select json_extract(content,'$.name','$.address.province','$.address.city') from test_json where id = 1; # 結果: +----------------------------------------------------------------------+ | json_extract(content,'$.name','$.address.province','$.address.city') | +----------------------------------------------------------------------+ | ["tom", "湖南", "長沙"] | +----------------------------------------------------------------------+
路徑表達式*的使用
將會把多個路徑的數(shù)據(jù)組合成數(shù)組返回
# 先插入一條用于測試的數(shù)據(jù)
INSERT INTO `test_json` (`id`,`content`) VALUES(3,'{"name":"tom","address":{"name":"中央公園","city":"長沙"},"class":{"id":3,"name":"一年三班"},"friend":[{"age":20,"name":"marry"},{"age":21,"name":"Bob"}]}')content: id=3
{“name”: “tom”, “class”: {“id”: 3, “name”: “一年三班”}, “friend”: [{“age”: 20, “name”: “marry”}, {“age”: 21, “name”: “Bob”}], “address”: {“city”: “長沙”, “name”: “中央公園”}}
# 獲取所有二級嵌套中key=name的值 # 由于friend的二級嵌套是一個數(shù)組,所以.name獲取不到其中的所有name值 select content->'$.*.name' from test_json where id = 3; +----------------------------------+ | content->'$.*.name' | +----------------------------------+ | ["一年三班", "中央公園"] | +----------------------------------+``` # 獲取所有key為name值的數(shù)據(jù)，包括任何嵌套內的name select content->'$**.name' from test_json where id = 3; +---------------------------------------------------------+ | content->'$**.name' | +---------------------------------------------------------+ | ["tom", "一年三班", "marry", "Bob", "中央公園"] | +---------------------------------------------------------+ # 獲取數(shù)組中所有的name值 select content->'$.friend[*].name' from test_json where id = 3; +-----------------------------+ | content->'$.friend[*].name' | +-----------------------------+ | ["marry", "Bob"] | +-----------------------------+
返回NULL值
content: id=1
{“age”: 18, “name”: “tom”, “score”: [100, 90, 87], “address”: {“city”: “長沙”, “province”: “湖南”}}
尋找的JSON路徑都不存在
# age路徑不存在，返回NULL # 若有多個路徑，只要有一個路徑存在則不會返回NULL select json_extract(content,'$.price') from test_json where id = 1; +---------------------------------+ | json_extract(content,'$.price') | +---------------------------------+ | NULL | +---------------------------------+
路徑中有NULL
# 存在任意路徑為NULL則返回NULL select json_extract(content,'$.age',NULL) from test_json where id = 1; +------------------------------------+ | json_extract(content,'$.age',NULL) | +------------------------------------+ | NULL | +------------------------------------+
返回錯誤
若第一個參數(shù)不是JSON類型的數(shù)據(jù)，則返回錯誤
select json_extract('{1,2]',$[0])
若路徑表達式不規(guī)范，則返回錯誤
select content->'$age' from test_json where id = 1; # 結果: ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 1.
使用場景
JSON_EXTRACT函數(shù)通常用于要獲取JSON中某個特定的數(shù)據(jù)或者要根據(jù)它作為判斷條件時使用
到此這篇關于mysql中json_extract的具體使用的文章就介紹到這了,更多相關mysql json_extract內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
超時是我們日常經常會遇到的一個問題,這篇文章主要給大家介紹了關于Mysql超時配置項的深入理解,內容簡明扼要并且容易理解,絕對能使你眼前一亮,需要的朋友可以參考下2023-01-01
這篇文章主要為大家詳細介紹了Windows安裝MySQL 5.7.18 解壓版的詳細教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-07-07
這篇文章主要給大家介紹了關于MySQL查詢學習之基礎查詢操作的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-05-05
這篇文章主要為大家詳細介紹了mysql 5.7.24 安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-11-11
MySQL日志記錄了MySQL數(shù)據(jù)庫日常操作和錯誤信息，MySQL有不同類型的日志文件（各自存儲了不同類型的日志），從日志當中可以查詢到MySQL數(shù)據(jù)庫的運行情況、用戶操作、錯誤信息等2022-08-08
MySQL 與 Elasticsearch 數(shù)據(jù)不對稱問題解決辦法
這篇文章主要介紹了MySQL 與 Elasticsearch 數(shù)據(jù)不對稱問題解決辦法的相關資料,對于 elasticsearch 增量寫入，但經常jdbc源一端的數(shù)據(jù)庫可能會做數(shù)據(jù)庫刪除或者更新操作，這里提供解決辦法，需要的朋友可以參考下2017-08-08
這篇文章主要介紹了MySQL如何實現(xiàn)循環(huán)插入千萬級數(shù)據(jù)，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2020-09-09
解決mysql報錯You must reset your password&nb
文章介紹了在Linux系統(tǒng)中解決MySQL 5.7及以上版本root用戶密碼過期無法登錄的問題方法,以及如何處理系統(tǒng)權限表mysql.user結構錯誤的問題2024-11-11
mysql5.7的安裝及Navicate長久免費使用的實現(xiàn)過程
這篇文章主要介紹了mysql5.7的安裝及Navicate長久免費使用的實現(xiàn)過程,本文給大家分享問題及解決方法，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-11-11
MySQL查詢表中重復數(shù)據(jù)的實現(xiàn)
在數(shù)據(jù)庫中,我們經常需要查詢重復字段來確保數(shù)據(jù)的準確性,如果數(shù)據(jù)中有重復字段,則可能會導致查詢結果錯誤,本文就想詳細的介紹了MySQL查詢表中重復數(shù)據(jù),感興趣的可以了解一下2023-08-08