mysql中json_extract的使用方法實(shí)例詳解
更新時(shí)間：2023年04月13日 09:14:09 作者：-王尚可-
MYSQl自帶的解析函數(shù)JSON_EXTRACT,用JSON_EXTRACT函數(shù)解析出來(lái)的函數(shù)會(huì)包含雙引號(hào),下面這篇文章主要給大家介紹了關(guān)于mysql中json_extract的使用方法,需要的朋友可以參考下
一、前言
mysql5.7版本開(kāi)始支持JSON類型字段，本文詳細(xì)介紹json_extract函數(shù)如何獲取mysql中的JSON類型數(shù)據(jù)
json_extract可以完全簡(jiǎn)寫(xiě)為 ->
json_unquote(json_extract())可以完全簡(jiǎn)寫(xiě)為 ->>
下面介紹中大部分會(huì)利用簡(jiǎn)寫(xiě)
二、創(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;
# 插入兩條測(cè)試用的記錄
INSERT INTO `test_json` (`content`) VALUES ('{\"name\":\"tom\",\"age\":18,\"score\":[100,90,87],\"address\":{\"province\":\"湖南\",\"city\":\"長(zhǎng)沙\"}}');
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”: “長(zhǎng)沙”, “province”: “湖南”}} |
| 2 | [1, “apple”, “red”, {“age”: 18, “name”: “tom”}] |
三、基本語(yǔ)法
- 獲取JSON對(duì)象中某個(gè)key對(duì)應(yīng)的value值
- json_extract函數(shù)中，第一個(gè)參數(shù)content表示json數(shù)據(jù)，第二個(gè)參數(shù)為json路徑，其中$表示該json數(shù)據(jù)本身，$.name就表示獲取json中key為name的value值
- 可以利用 -> 表達(dá)式來(lái)代替json_extract
- 若獲取的val本身為字符串，那么獲取的val會(huì)被引號(hào)包起來(lái)，比如"tom"，這種數(shù)據(jù)被解析到程序?qū)ο笾袝r(shí)，可能會(huì)被轉(zhuǎn)義為\“tom\”。為了解決這個(gè)問(wèn)題了，可以在外面再包上一層json_unquote函數(shù)，或者使用 ->> 代替->
content:
{“age”: 18, “name”: “tom”, “score”: [100, 90, 87], “address”: {“city”: “長(zhǎng)沙”, “province”: “湖南”}}
# 得到"tom" select json_extract(content,'$.name') from test_json where id = 1; # 簡(jiǎn)寫(xiě)方式：字段名->表達(dá)式等價(jià)于json_extract(字段名,表達(dá)式) select content->'$.name' from test_json where id = 1; # 結(jié)果: +--------------------------------+ | json_extract(content,'$.name') | +--------------------------------+ | "tom" | +--------------------------------+ +-------------------+ | content->'$.name' | +-------------------+ | "tom" | +-------------------+ # 解除雙引號(hào)，得到tom select json_unquote(json_extract(content,'$.name')) from test_json where id = 1; # 簡(jiǎn)寫(xiě)方式：字段名->>表達(dá)式等價(jià)于json_unquote(json_extract(字段名,表達(dá)式)) select content->>'$.name' from test_json where id = 1; # 結(jié)果: +----------------------------------------------+ | json_unquote(json_extract(content,'$.name')) | +----------------------------------------------+ | tom | +----------------------------------------------+ +--------------------+ | content->>'$.name' | +--------------------+ | tom | +--------------------+
- 獲取JSON數(shù)組中某個(gè)元素
- json_extract函數(shù)中，第一個(gè)參數(shù)content表示json數(shù)據(jù)，第二個(gè)參數(shù)為json路徑，其中$表示該json數(shù)據(jù)本身，$[i]表示獲取該json數(shù)組索引為i的元素（索引從0開(kāi)始）
- 與獲取key-val一樣，若獲取的元素為字符串，默認(rèn)的方式也會(huì)得到雙引號(hào)包起來(lái)的字符，導(dǎo)致程序轉(zhuǎn)義，方法也是利用json_unquote函數(shù)，或者使用 ->> 代替->
content:
[1, “apple”, “red”, {“age”: 18, “name”: “tom”}]
# 得到"apple" select json_extract(content,'$[1]') from test_json where id = 2; # 簡(jiǎn)寫(xiě)，效果同上 select content->'$[1]' from test_json where id = 2; # 結(jié)果: +------------------------------+ | json_extract(content,'$[1]') | +------------------------------+ | "apple" | +------------------------------+ +-----------------+ | content->'$[1]' | +-----------------+ | "apple" | +-----------------+ # 解除雙引號(hào)，得到apple select json_unquote(json_extract(content,'$[1]')) from test_json where id = 2; # 簡(jiǎn)寫(xiě)，效果同上 select content->>'$[1]' from test_json where id = 2; # 結(jié)果: +--------------------------------------------+ | json_unquote(json_extract(content,'$[1]')) | +--------------------------------------------+ | apple | +--------------------------------------------+ +------------------+ | content->>'$[1]' | +------------------+ | apple | +------------------+
- 獲取JSON中的嵌套數(shù)據(jù)
結(jié)合前面介紹的兩種獲取方式，可以獲取json數(shù)據(jù)中的嵌套數(shù)據(jù)
content: id=1
{“age”: 18, “name”: “tom”, “score”: [100, 90, 87], “address”: {“city”: “長(zhǎng)沙”, “province”: “湖南”}}
content: id=2
[1, “apple”, “red”, {“age”: 18, “name”: “tom”}]
# 得到:87 select content->'$.score[2]' from test_json where id = 1; # 結(jié)果: +-----------------------+ | content->'$.score[2]' | +-----------------------+ | 87 | +-----------------------+ # 得到:18 select content->'$[3].age' from test_json where id = 2; # 結(jié)果: +---------------------+ | content->'$[3].age' | +---------------------+ | 18 | +---------------------+
四、漸入佳境
- 獲取JSON多個(gè)路徑的數(shù)據(jù)
將會(huì)把多個(gè)路徑的數(shù)據(jù)組合成數(shù)組返回
content: id=1
{“age”: 18, “name”: “tom”, “score”: [100, 90, 87], “address”: {“city”: “長(zhǎng)沙”, “province”: “湖南”}}
select json_extract(content,'$.age','$.score') from test_json where id = 1; # 結(jié)果: +-----------------------------------------+ | json_extract(content,'$.age','$.score') | +-----------------------------------------+ | [18, [100, 90, 87]] | +-----------------------------------------+ select json_extract(content,'$.name','$.address.province','$.address.city') from test_json where id = 1; # 結(jié)果: +----------------------------------------------------------------------+ | json_extract(content,'$.name','$.address.province','$.address.city') | +----------------------------------------------------------------------+ | ["tom", "湖南", "長(zhǎng)沙"] | +----------------------------------------------------------------------+
- 路徑表達(dá)式*的使用
將會(huì)把多個(gè)路徑的數(shù)據(jù)組合成數(shù)組返回
# 先插入一條用于測(cè)試的數(shù)據(jù)
INSERT INTO `test_json` (`id`,`content`) VALUES(3,'{"name":"tom","address":{"name":"中央公園","city":"長(zhǎng)沙"},"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”: “長(zhǎng)沙”, “name”: “中央公園”}}
# 獲取所有二級(jí)嵌套中key=name的值 # 由于friend的二級(jí)嵌套是一個(gè)數(shù)組,所以.name獲取不到其中的所有name值 select content->'$.*.name' from test_json where id = 3; +----------------------------------+ | content->'$.*.name' | +----------------------------------+ | ["一年三班", "中央公園"] | +----------------------------------+``` # 獲取所有key為name值的數(shù)據(jù)，包括任何嵌套內(nèi)的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”: “長(zhǎng)沙”, “province”: “湖南”}}
尋找的JSON路徑都不存在
# age路徑不存在，返回NULL # 若有多個(gè)路徑，只要有一個(gè)路徑存在則不會(huì)返回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 | +------------------------------------+
- 返回錯(cuò)誤
若第一個(gè)參數(shù)不是JSON類型的數(shù)據(jù)，則返回錯(cuò)誤
select json_extract('{1,2]',$[0])
若路徑表達(dá)式不規(guī)范，則返回錯(cuò)誤
select content->'$age' from test_json where id = 1; # 結(jié)果: ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 1.
五、使用場(chǎng)景
JSON_EXTRACT函數(shù)通常用于要獲取JSON中某個(gè)特定的數(shù)據(jù)或者要根據(jù)它作為判斷條件時(shí)使用
六、參考文檔
總結(jié)
到此這篇關(guān)于mysql中json_extract使用方法的文章就介紹到這了,更多相關(guān)mysql中json_extract使用內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
一文帶你永久擺脫Mysql時(shí)區(qū)錯(cuò)誤問(wèn)題(idea數(shù)據(jù)庫(kù)可視化插件配置)
在MySQL啟動(dòng)時(shí)會(huì)檢查當(dāng)前系統(tǒng)的時(shí)區(qū)并根據(jù)系統(tǒng)時(shí)區(qū)設(shè)置全局參數(shù)system_time_zone的值,下面這篇文章主要給大家介紹了關(guān)于如何永久擺脫Mysql時(shí)區(qū)錯(cuò)誤問(wèn)題(idea數(shù)據(jù)庫(kù)可視化插件配置)的相關(guān)資料,需要的朋友可以參考下2022-08-08
在MySQL數(shù)據(jù)庫(kù)中,當(dāng)多個(gè)事務(wù)同時(shí)請(qǐng)求對(duì)同一行數(shù)據(jù)進(jìn)行修改時(shí),就會(huì)發(fā)生鎖表現(xiàn)象,本文主要介紹了MySQL查看鎖表的實(shí)現(xiàn)步驟,具有一定的參考價(jià)值,感興趣的可以了解一下2024-01-01
MySQL表字段設(shè)置默認(rèn)值(圖文教程及注意細(xì)節(jié))
默認(rèn)值的設(shè)置很重要，比如在插入的時(shí)候一些字段是可以省略的，這會(huì)帶來(lái)很多的方便，接下來(lái)將要介紹MySQL表字段設(shè)置默認(rèn)值感興趣的你可以千萬(wàn)不要走開(kāi)啊，希望本文對(duì)你有所幫助2013-01-01
MySQL中使用PROFILING來(lái)查看SQL執(zhí)行流程的實(shí)現(xiàn)步驟
在MySQL中,PROFILING功能提供了一種方式來(lái)分析SQL語(yǔ)句的執(zhí)行時(shí)間,包括查詢執(zhí)行的各個(gè)階段,如發(fā)送、解析、優(yōu)化、執(zhí)行等,這對(duì)于診斷性能問(wèn)題非常有用,本文給大家介紹了MySQL中使用PROFILING來(lái)查看SQL執(zhí)行流程的實(shí)現(xiàn)步驟,需要的朋友可以參考下2024-07-07
mysql使用mysqld_multi部署單機(jī)多實(shí)例的方法教程
這篇文章主要給大家介紹了關(guān)于mysql使用mysqld_multi部署單機(jī)多實(shí)例的相關(guān)資料，文中通過(guò)示例代碼將實(shí)現(xiàn)的步驟一步步介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧。2018-03-03
mysql innodb 異常修復(fù)經(jīng)驗(yàn)分享
這篇文章主要介紹了mysql innodb 異常修復(fù)經(jīng)驗(yàn)分享,需要的朋友可以參考下2017-04-04
mysql下的一些數(shù)據(jù)類型，后面附有類型的說(shuō)明。2009-04-04
這篇文章主要介紹了MySQL占用CPU過(guò)高,排查原因及解決方案，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-12-12
MYSQL性能優(yōu)化之分庫(kù)分表與不停機(jī)修改mysql表結(jié)構(gòu)，需要的朋友可以參考下2012-02-02
mysql添加索引方法詳解(Navicat可視化加索引與sql語(yǔ)句加索引)
索引用來(lái)快速地尋找那些具有特定值的記錄,如果沒(méi)有索引,執(zhí)行查詢時(shí)MySQL必須從第一個(gè)記錄開(kāi)始掃描整個(gè)表的所有記錄,直至找到符合要求的記錄,表里面的記錄數(shù)量越多,代價(jià)就越高,下面這篇文章主要給大家介紹了關(guān)于mysql添加索引的相關(guān)資料,需要的朋友可以參考下2022-11-11