MySQL實(shí)現(xiàn)查詢(xún)處理JSON數(shù)據(jù)的示例詳解
前言
最近在做項(xiàng)目時(shí)，表 A 有多個(gè)字段，其中一個(gè)字段 info 把當(dāng)前項(xiàng)目用不到的冗余的數(shù)據(jù)按照 JSON 格式都存了進(jìn)來(lái)。隨著項(xiàng)目的推進(jìn)，有些冗余字段需要單獨(dú)成一列。新增一列之后，需要把 info中對(duì)應(yīng)的數(shù)據(jù)刷入新增列，這就需要從 MySQL 中讀取 JSON 數(shù)據(jù)。
當(dāng)時(shí)想到的方法，就是寫(xiě)個(gè)程序，批量查詢(xún)數(shù)據(jù)，把 info 字段查詢(xún)出來(lái)后反序列化，取出其中的key-value，然后再存入數(shù)據(jù)庫(kù)。后面查詢(xún)資料，發(fā)現(xiàn) MySQL 已經(jīng)提供了從 JSON 數(shù)據(jù)中查找和比較的函數(shù)，極大地方便了數(shù)據(jù)處理！
這是在沒(méi)有寫(xiě)這篇文章前，查詢(xún)資料寫(xiě)出來(lái)的刷數(shù)據(jù)SQL，其實(shí)還有優(yōu)化的空間，等文章最后我們一起看下吧！
select?id, ???????replace(replace(json_extract(`info`,?'$.budget_mode'),?'"',?''),?'null',?''), ???????replace(replace(json_extract(`info`,?'$.budget'),?'"',?''),?'null',?0), ???????replace(replace(json_extract(`info`,?'$.bid'),?'"',?''),?'null',?0), from?table where?code?=?'xxx';
其次，為了方便后面的學(xué)習(xí)和測(cè)試，我們新建一張表，建表語(yǔ)句如下:
create?table?`userinfo` ( ????`id`???bigint?unsigned?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵'?PRIMARY?KEY, ????`info`?longtext????????NOT?NULL?COMMENT?'用戶(hù)信息' );
接下來(lái)我們就看下MySQL提供的 JSON 查詢(xún)和比較函數(shù)，比較常用的應(yīng)該就是 JSON_EXTRACT 、column->path、column->>path 和 JSON_VALUE 四個(gè)函數(shù)，可以按需學(xué)習(xí)哦！
JSON_CONTAINS
該函數(shù)用于判斷一個(gè) JSON 文檔是否包含另一個(gè) JSON 文檔。如果提供了路徑，用于判斷 JSON 文檔相應(yīng)路徑下的數(shù)據(jù)是否包含另一個(gè)JSON 文檔。
語(yǔ)法
JSON_CONTAINS(target,candidate[,path])
- target: 必填。目標(biāo) JSON 文檔
- candidate: 必填。被包含的 JSON 文檔
- path: 可選。路徑
返回值
- 如果 target 或者 target 在 path 路徑下的數(shù)據(jù)包含 candidate，返回 1；否則返回 0
- 如果任意一個(gè)必填參數(shù)為 NULL，或者路徑 path 在 target 中不存在，返回 NULL
- 如果 target 或者 candidate 不是一個(gè)有效的JSON 文檔，查詢(xún)報(bào)錯(cuò)
- 如果提供的 path 不是一個(gè)有效的路徑表達(dá)式，或者 path 包含通配符 '*' 或者 '**' ，查詢(xún)報(bào)錯(cuò)
規(guī)則
- 對(duì)于兩個(gè)簡(jiǎn)單類(lèi)型的變量，如果兩者類(lèi)型相同、該類(lèi)型可比較且值相等，則 target 包含 candidate
- 對(duì)于兩個(gè)數(shù)組類(lèi)型的變量，如果 candidate 數(shù)組中的每個(gè)元素，都存在于 target 中的某些元素中，則 target 包含 candidate
- 對(duì)于一個(gè)非數(shù)組類(lèi)型 candidate 和數(shù)組類(lèi)型 target，如果 candidate 存在于 target 的某些元素中，則 target 包含 candidate
- 對(duì)于兩個(gè)對(duì)象，如果 candidate 的每個(gè) key 都在 target 中存在，且對(duì)應(yīng)的 value 值也被包含，則 target 包含 candidate
測(cè)試
insert?into?userinfo?(id,?info)?values?(1,'{"a":?1,?"b":?2,?"c":?{"d":?4},"d":[1,2,3]}');
select??JSON_CONTAINS(info,'1')?from?userinfo?where?id=1;?#?0,?target?不包含?JSON?'1'
select??JSON_CONTAINS(info,'1','$.a')?from?userinfo?where?id=1;?#?1,?兩個(gè)簡(jiǎn)單類(lèi)型,?1?包含?1
select??JSON_CONTAINS(info,'1','$.d')?from?userinfo?where?id=1;?#?1,?非數(shù)組和數(shù)組類(lèi)型比較,?[1,2,3]?包含?1
select??JSON_CONTAINS(info,'[1,2]','$.d')?from?userinfo?where?id=1;?#?1,?兩個(gè)數(shù)組類(lèi)型比較,?[1,2,3]?包含?數(shù)組類(lèi)型?[1,2]
select??JSON_CONTAINS(info,'[1,2,4]','$.d')?from?userinfo?where?id=1;?#?0,?兩個(gè)數(shù)組類(lèi)型比較,?[1,2,3]?不包含?數(shù)組類(lèi)型?[1,2,4]
select??JSON_CONTAINS(info,'{"a":1}')?from?userinfo?where?id=1;?#?1,?兩個(gè)對(duì)象比較,?target?中存在?key?'a',且?value?包含
select??JSON_CONTAINS(info,'{"a":2}')?from?userinfo?where?id=1;?#?0,?兩個(gè)對(duì)象比較,?target?存在?key?'a',但?value?不包含
select??JSON_CONTAINS(info,'{"d":2}')?from?userinfo?where?id=1;?#?1,?兩個(gè)對(duì)象比較,?target?存在?key?'d',且?value?包含
select??JSON_CONTAINS(info,'{"a":1,"d":2}')?from?userinfo?where?id=1;?#?1,?兩個(gè)對(duì)象比較,?target?存在?key?'a'?和?'d'?,且?value?均包含
select??JSON_CONTAINS(info,'{"a":1,"d":[2,3]}')?from?userinfo?where?id=1;?#?1,?兩個(gè)對(duì)象比較,?target?存在?key?'a'?和?'d'?,且?value?均包含JSON_CONTAINS_PATH
該函數(shù)用于判斷一個(gè) JSON 文檔是否包含一個(gè)或者多個(gè)路徑 path
語(yǔ)法
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path...])]
- json_doc: 必填。一個(gè) JSON 文檔
- one_or_all: 必填。值為 'one' 或者 'all'，指定至少一個(gè)還是所有 path 存在于 json_doc
- path: 必填。至少填寫(xiě)一個(gè)路徑
返回值
- one_or_all = 'one' 時(shí)，如果存在一個(gè) path 存在于 json_doc，返回 1 ; 否則返回 0
- one_or_all = 'all' 時(shí)，所有 path 存在于 json_doc 返回 1 ; 否則返回 0
- 如果有參數(shù)為 NULL，則返回 NULL
- 如果 json_doc 不是有效的JSON數(shù)據(jù)，或者 path 不是合法的表達(dá)式，或者 one_or_all 參數(shù) 取值不是 'one' 或者 'all'，返回 error
測(cè)試
insert?into?userinfo?(id,?info)?values?(2,'{"a":?1,?"b":?2,?"c":?{"d":?4}}');
select?JSON_CONTAINS_PATH(info,'one','$.a')?from?userinfo?where?id=2;?#?1，?a?存在于?路徑中
select?JSON_CONTAINS_PATH(info,'one','$.a','$.e')?from?userinfo?where?id=2?;?#?1，?至少一個(gè)存在即可，且路徑?a?存在
select?JSON_CONTAINS_PATH(info,'all','$.a','$.e')?from?userinfo?where?id=2;?#?0，?必須所有路徑都存在，但路徑?e?不存在中
select?JSON_CONTAINS_PATH(info,'all','$.c.d')?from?userinfo?where?id=2;?#?1，?路徑?c.d?存在JSON_EXTRACT
該函數(shù)用于從 JSON 字段中查詢(xún)路徑 path 對(duì)應(yīng)的 value 值
語(yǔ)法
JSON_EXTRACT(json_doc, path[,path...])
- json_doc: 必填。一個(gè) JSON 文檔
- path: 必填。至少填寫(xiě)一個(gè)路徑
返回值
- 如果只匹配到一個(gè)path，則返回對(duì)應(yīng)的 value
- 如果匹配到多個(gè) path，則將所有的 value 組合成一個(gè)數(shù)組返回，value 在數(shù)組的順序和 提供的 path 順序保持一致
- 如果參數(shù)為 NULL，或者未在 json_doc 中匹配到對(duì)應(yīng)的 path，則返回NULL
- 如果 json_doc 不是合法的 JSON 文檔，或者 path 不是合法的路徑表達(dá)式，則返回error
insert?into?userinfo?(id,?info)?values?(3,'{"a":?1,?"b":?2,?"c":?{"d":?4},"d":[1,2,3],"e":{"name":"tom","age":12}}');
select?json_extract(info,'$.a')?from?userinfo?where?id=3;?#?1
select?json_extract(info,'$.c.d')?from?userinfo?where?id=3;?#?4
select?json_extract(info,'$.d')?from?userinfo?where?id=3;?#?[1,2,3]
select?json_extract(info,'$.d[0]')?from?userinfo?where?id=3;?#?1
select?json_extract(info,'$.d[3]')?from?userinfo?where?id=3;?#?NULL
select?json_extract(info,'$.f')?from?userinfo?where?id=3;?#?NULL
select?json_extract(info,'$.a','$.b','$.c','$.d','$.e.name','$.e.age','$.f')?from?userinfo?where?id=3;?#?[1,?2,?{"d":?4},?[1,?2,?3],?"tom",?12]如果只查詢(xún)一個(gè) path，可以使用接下來(lái)介紹的 -> 操作符
column->path
JSON_EXTRACT 只有兩個(gè)參數(shù)時(shí)的縮寫(xiě)。
如下兩個(gè)查詢(xún)是等價(jià)的：
select?info,info->'$.a'?as?info_a?from?userinfo?where?info->'$.a'?>0?;
select?info,JSON_EXTRACT(info,'$.a')?as?info_a??from?userinfo?where?JSON_EXTRACT(info,'$.a')>0;
+-----------------------------------------------------------------------+------+
|info???????????????????????????????????????????????????????????????????|info_a|
+-----------------------------------------------------------------------+------+
|{"a":?1,?"b":?2,?"c":?{"d":?4},"d":[1,2,3]}????????????????????????????|1?????|
|{"a":?1,?"b":?2,?"c":?{"d":?4}}????????????????????????????????????????|1?????|
|{"a":?1,?"b":?2,?"c":?{"d":?4},"d":[1,2,3],"e":{"name":"tom","age":12}}|1?????|
+-----------------------------------------------------------------------+------+和列操作一樣，這個(gè)符號(hào)可以用于 where條件、order by 條件等
select?info,info->'$.a'?as?a,?info->'$.c.d'?as?info_c_d?from?userinfo?where?info->'$.d'?is?not?null?;
+-----------------------------------------------------------------------+-+--------+
|info???????????????????????????????????????????????????????????????????|a|info_c_d|
+-----------------------------------------------------------------------+-+--------+
|{"a":?1,?"b":?2,?"c":?{"d":?4},"d":[1,2,3]}????????????????????????????|1|4???????|
|{"a":?1,?"b":?2,?"c":?{"d":?4},"d":[1,2,3],"e":{"name":"tom","age":12}}|1|4???????|
+-----------------------------------------------------------------------+-+--------+
select?info,info->'$.a'?as?a,info->'$.c.d'?as?info_c_d?from?userinfo?where?info->'$.d[0]'>0?order?by?'$.a';
+-----------------------------------------------------------------------+-+--------+
|info???????????????????????????????????????????????????????????????????|a|info_c_d|
+-----------------------------------------------------------------------+-+--------+
|{"a":?1,?"b":?2,?"c":?{"d":?4},"d":[1,2,3]}????????????????????????????|1|4???????|
|{"a":?1,?"b":?2,?"c":?{"d":?4},"d":[1,2,3],"e":{"name":"tom","age":12}}|1|4???????|
+-----------------------------------------------------------------------+-+--------+column->>path
'->>' 符號(hào)相對(duì)于 '->'，增加了去除引號(hào)的功能。如果一個(gè) JSON 文檔中，key 對(duì)應(yīng)的 value 是字符串類(lèi)型，那么如下三個(gè)表達(dá)式返回相同的結(jié)果：
- JSON_UNQUOTE(JSON_EXTRACT(column,path))
- JSON_UNQUOTE(column->path)
- column->>path
select?info->'$.e.name'?as?name?from?userinfo?where?id=3;?#?"tom" select?json_unquote(json_extract(info,'$.e.name'))?as?name?from?userinfo?where?id?=3;?#?tom select?json_unquote(info->'$.e.name')?as?name?from?userinfo?where?id=3;?#?tom select?info->>'$.e.name'?as?name?from?userinfo?where?id=3;?#?tom
JSON_KEYS
該函數(shù)用于返回 JSON 文檔或者指定 path 下最頂層的所有 key
語(yǔ)法
JSON_KEYS(json_doc,[path])
- json_doc: 必填。一個(gè) JSON 文檔
- path: 選填。路徑
返回值
- 返回 json_doc 或者指定 path 下最頂層的 key 數(shù)組
- 如果任意參數(shù)為 NULL，或者 json_doc 不是一個(gè)對(duì)象（可能是個(gè)數(shù)組），或者根據(jù) path 沒(méi)有定位到數(shù)據(jù)，則返回NULL
- 如果 json_doc不是 JSON 對(duì)象，或者指定的路徑不合法，返回error
測(cè)試
select?info,json_keys(info)?from?userinfo?where?id=1;
+-------------------------------------------+--------------------+
|info???????????????????????????????????????|json_keys(info)?????|
+-------------------------------------------+--------------------+
|{"a":?1,?"b":?2,?"c":?{"d":?4},"d":[1,2,3]}|["a",?"b",?"c",?"d"]|
+-------------------------------------------+--------------------+
select?info,json_keys(info->'$.c')?from?userinfo?where?id=1;
+-------------------------------------------+----------------------+
|info???????????????????????????????????????|json_keys(info->'$.c')|
+-------------------------------------------+----------------------+
|{"a":?1,?"b":?2,?"c":?{"d":?4},"d":[1,2,3]}|["d"]?????????????????|
+-------------------------------------------+----------------------+JSON_OVERLAPS
該函數(shù)用于判斷兩個(gè)JSON文檔是否有重疊
語(yǔ)法
JSON_OVERLAPS(json_doc1, json_doc2)
- json_doc1: 必填。JSON文檔1
- json_doc2: 必填。JSON文檔2
返回值
- 如果兩個(gè)JSON文檔有重疊，返回 1；否則返回 0
- 如果參數(shù)為NULL，返回NULL
重疊邏輯
- 如果兩個(gè)JSON文檔均為簡(jiǎn)單類(lèi)型，相當(dāng)于判等操作，相等則為重疊
- 如果兩個(gè)JSON文檔均為數(shù)組，如果至少有一個(gè)元素相同，則為重疊
- 如果兩個(gè)JSON文檔均為對(duì)象，如果至少有 key-value 相同，則為重疊
測(cè)試
兩個(gè)基礎(chǔ)元素，就是簡(jiǎn)單的判等操作
SELECT?JSON_OVERLAPS('5',?'5');?#?1,?相等
SELECT?JSON_OVERLAPS('"5"',?'5');?#?0,?類(lèi)型不同，不相等對(duì)于數(shù)組，需要有元素相同；如果是多維數(shù)組，子數(shù)組元素需要完全一樣
SELECT?JSON_OVERLAPS("[1,3,5,7]",?"[2,5,7]");??#?1,?存在相同的元素?5?和?7
SELECT?JSON_OVERLAPS("[1,3,5,7]",?"[2,6,7]");?#?1,?存在相同的元素?7
SELECT?JSON_OVERLAPS("[1,3,5,7]",?"[2,6,8]");?#?0,?沒(méi)有相同元素
SELECT?JSON_OVERLAPS('[[1,2],[3,4],5]',?'[1,[2,3],[4,5]]');?#?0,?沒(méi)有相同元素
SELECT?JSON_OVERLAPS('[[1,2],[3,4],5]',?'[[1,2],[2,3],[4,5]]');?#?1,?有相同元素?[1,2]如果是對(duì)象，需要 key-value 完全一樣
SELECT?JSON_OVERLAPS('{"a":1,"b":10,"d":10}',?'{"c":1,"e":10,"f":1,"d":10}');?#?1,?相同key-value?"d":10
SELECT?JSON_OVERLAPS('{"a":1,"b":10,"d":10}',?'{"a":5,"e":10,"f":1,"d":20}');?#?0,?沒(méi)有相同元素
SELECT?JSON_OVERLAPS('{"a":1,"b":10,"d":[20,30]}',?'{"a":5,"e":10,"f":1,"d":[20]}');?#?0,?沒(méi)有相同元素如果一個(gè)基礎(chǔ)類(lèi)型和數(shù)組類(lèi)型比較，基礎(chǔ)類(lèi)型會(huì)被轉(zhuǎn)成數(shù)組類(lèi)型
SELECT?JSON_OVERLAPS('[4,5,6,7]',?'6');??#?1,?[4,5,6,7]和?[6]?有相同元素?6
SELECT?JSON_OVERLAPS('[4,5,6,7]',?'"6"');?#?0,?類(lèi)型不同，沒(méi)有相同元素JSON_SEARCH
對(duì)于給定的字符串，返回該字符串在 JSON 文檔中的路徑
語(yǔ)法
JSON_SEARCH(json_doc, one_or_all, search_str, escape_char, path...)
json_doc: 必填。JSON文檔
one_or_all: 必填。取值只能為 one 或者 all
- one: 返回第一個(gè)匹配的路徑
- all: 以數(shù)組的形式返回所有匹配到的路徑，去重，無(wú)順序
search_str: 必填。要查詢(xún)的字符串，可以使用通配符
- %: 匹配0個(gè)或多個(gè)字符
- _: 匹配一個(gè)字符
escape_char: 可選。如果 search_str 中包含 % 和 _，需要在他們之前添加轉(zhuǎn)移字符。默認(rèn)是 \。
path: 可選。指定在具體路徑下搜索
返回值
JSON_SEARCH() 函數(shù)返回一個(gè)給定字符串在一個(gè) JSON 文檔中的路徑。它返回一個(gè)路徑字符串或者由多個(gè)路徑組成的數(shù)組。
JSON_SEARCH() 函數(shù)將在以下情況下返回 NULL：
- 未搜索到指定的字符串
- JSON 文檔中不存在指定的 path
- 任意一個(gè)參數(shù)為 NULL
JSON_SEARCH() 函數(shù)將在以下情況下返回錯(cuò)誤：
- 如果參數(shù) json 不是有效的 JSON 文檔，MySQL 將會(huì)給出錯(cuò)誤。
- 如果參數(shù) path 不是有效的路徑表達(dá)式, MySQL 將會(huì)給出錯(cuò)誤。
測(cè)試
SET?@json_doc?=?'["abc",?[{"k":?"10"},?"def"],?{"x":"abc"},?{"y":"bcd"}]';
select?JSON_SEARCH(@json_doc,?'one',?'abc');?#?"$[0]"
select?JSON_SEARCH(@json_doc,?'all',?'abc');?#?["$[0]",?"$[2].x"]
select?JSON_SEARCH(@json_doc,?'all',?'ghi');?#?null
select?JSON_SEARCH(@json_doc,?'all',?'10');?#?"$[1][0].k"??
--?指定路徑
select?JSON_SEARCH(@json_doc,?'all',?'10',?NULL,?'$[*][0].k');?#?"$[1][0].k"?
select?JSON_SEARCH(@json_doc,?'all',?'10',?NULL,?'$[1][0]');?#?"$[1][0].k"?
select?JSON_SEARCH(@json_doc,?'all',?'abc',?NULL,?'$[2]');?#?"$[2].x"?
--?通配符
select?JSON_SEARCH(@json_doc,?'all',?'%a%');?#?["$[0]",?"$[2].x"]??
select?JSON_SEARCH(@json_doc,?'all',?'%b%');?#?["$[0]",?"$[2].x",?"$[3].y"]?
select?JSON_SEARCH(@json_doc,?'all',?'%b%',?NULL,?'$[2]');?#?"$[2].x"???JSON_VALUE
該函數(shù)的作用是：查詢(xún) JSON 文檔 path 下的值
語(yǔ)法
JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])
on_empty:NULLERROR | DEFAULT valueON EMPTY
on_error:NULLERROR | DEFAULT valueON ERROR
參數(shù)
json_doc: 必填。JSON文檔
path: 必填。指定的路徑
RETURNING type: 可選。將結(jié)果轉(zhuǎn)為指定的類(lèi)型，可以為如下類(lèi)型：
- FLOAT
- DOUBLE
- DECIMAL
- SIGNED
- UNSIGNED
- DATE
- TIME
- DATETIME
- YEAR (MySQL 8.0.22 and later)
- CHAR
- JSON
NULLERROR | DEFAULT valueON EMPTY
可選。如果指定了，它決定了指定路徑下沒(méi)有數(shù)據(jù)的返回值：
NULL ON EMPTY: 如果指定路徑下沒(méi)有數(shù)據(jù)，JSON_VALUE() 函數(shù)將返回 NULL，這是默認(rèn)的行為。
DEFAULT value ON EMPTY: 如果指定路徑下沒(méi)有數(shù)據(jù)，JSON_VALUE`() 函數(shù)將返回 value。
ERROR ON EMPTY: 如果指定路徑下沒(méi)有數(shù)據(jù)，JSON_VALUE() 函數(shù)將拋出一個(gè)錯(cuò)誤。
NULLERROR | DEFAULT valueON ERROR
可選的。如果指定了，它決定了處理錯(cuò)誤的邏輯：
- NULL ON ERROR: 如果有錯(cuò)誤，JSON_VALUE() 函數(shù)將返回 NULL，這是默認(rèn)的行為。
- DEFAULT value ON ERROR: 如果有錯(cuò)誤，JSON_VALUE() 函數(shù)將返回 value。
- ERROR ON ERROR: 如果有錯(cuò)誤，JSON_VALUE() 函數(shù)將拋出一個(gè)錯(cuò)誤。
返回值
默認(rèn)以字符串的格式，返回 JSON 文檔在指定的路徑上的值；如果使用 RETURNING type 子句，會(huì)把結(jié)果轉(zhuǎn)為 type 類(lèi)型
測(cè)試
SELECT?JSON_VALUE('{"fname":?"Joe",?"lname":?"Palmer"}',?'$.fname');?#?Joe
SELECT?JSON_VALUE('{"item":?"shoes",?"price":?"49.95"}',?'$.price'?RETURNING?DECIMAL(4,2));?#?49.95
SELECT?JSON_VALUE('{"item":?"shoes",?"price":?"49.95"}',?'$.total'?DEFAULT?100.00?ON?EMPTY);?#?100.00MEMBEROF
該函數(shù)用于判斷value，是否是數(shù)組 json_array 的元素
語(yǔ)法
value MEMBER_OF (json_array)
value: 必填。任意值，可以是一個(gè)簡(jiǎn)單類(lèi)型或者 JSON
json_array: 必填。一個(gè)JSON數(shù)組
返回值
如果 value 是 json_array 中的元素，返回1；否則返回0
測(cè)試
SELECT?17?MEMBER?OF('[23,?"abc",?17,?"ab",?10]');?#?1
SELECT?'17'?MEMBER?OF('[23,?"abc",?17,?"ab",?10]');?#?0,?類(lèi)型不一致
SELECT?'ab'?MEMBER?OF('[23,?"abc",?17,?"ab",?10]');?#?1
SELECT?CAST('[4,5]'?AS?JSON)?MEMBER?OF('[[3,4],[4,5]]');?#?1
SELECT?JSON_ARRAY(4,5)?MEMBER?OF('[[3,4],[4,5]]');?#?1現(xiàn)在我們可以回過(guò)頭來(lái)看下文章開(kāi)頭要優(yōu)化的SQL：
這是表 info 字段存儲(chǔ)的數(shù)據(jù)，如果字段有數(shù)據(jù)，存儲(chǔ)對(duì)應(yīng)的數(shù)據(jù)類(lèi)型；如果沒(méi)有數(shù)據(jù)，存儲(chǔ) null。但是 string 類(lèi)型的 value 有引號(hào)，我們想去掉引號(hào)；其次對(duì)于 null 值，也想替換成默認(rèn)值
{
????"ulink":null,
????"budget_mode":"BUDGET_MODE_DAY",
????"hide_if_exists":0
}之前我們的SQL 是這樣的
select?id, ???????replace(replace(json_extract(`info`,?'$.budget_mode'),?'"',?''),?'null',?''), ???????replace(replace(json_extract(`info`,?'$.budget'),?'"',?''),?'null',?0), ???????replace(replace(json_extract(`info`,?'$.bid'),?'"',?''),?'null',?0), from?table where?code?=?'xxx';
json_extract 是為了拿到對(duì)應(yīng)的 value，里面的 replace(）是為了去掉引號(hào)，外面的 replace 是為了將 null 替換為默認(rèn)值。對(duì)于去掉引號(hào)，我們可以使用 column ->> path 簡(jiǎn)化：
select?id, ???????replace(info?->>?'$.budget_mode',?'null',?''), ???????replace(info?->>?'$.budget',?'null',?0), ???????replace(info?->>?'$.bid',?'null',?0) from?ad_ad where?id?=?6993;
總結(jié)
本篇文章一共介紹了如下幾個(gè)函數(shù)：
- JSON_CONTAINS：判斷一個(gè) JSON 文檔是否包含另一個(gè) JSON 文檔
- JSON_CONTAINS_PATH：判斷一個(gè)JSON文檔，是否包含一個(gè)或者多個(gè)路徑 path
- JSON_EXTRACT：從 JSON 文檔中查詢(xún)路徑對(duì)應(yīng)的 value 值
- column->path：JSON_EXTRACT 只有兩個(gè)參數(shù)時(shí)的縮寫(xiě)
- column->>path：相對(duì)于 '->'，增加了去除
引號(hào)的功能 - JSON_KEYS：返回 JSON 文檔或者指定 path 下最頂層的所有 key
- JSON_OVERLAPS：判斷兩個(gè) JSON 文檔是否有重疊
- JSON_SEARCH：返回給定字符串在 JSON 文檔中的路徑
- JSON_VALUE：查詢(xún) JSON 文檔 path 下的值
- MEMBEROF：判斷一個(gè)值是否為一個(gè) JSON 數(shù)組中的元素
到此這篇關(guān)于MySQL實(shí)現(xiàn)查詢(xún)處理JSON數(shù)據(jù)的示例詳解的文章就介紹到這了,更多相關(guān)MySQL查詢(xún)處理JSON數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
- Mysql中json類(lèi)型數(shù)據(jù)查詢(xún)問(wèn)題
- MySQL查詢(xún)和篩選存儲(chǔ)的JSON數(shù)據(jù)的操作方法
- mysql中json基礎(chǔ)查詢(xún)?cè)斀?附圖文)
- Mysql中json類(lèi)型數(shù)據(jù)查詢(xún)的實(shí)現(xiàn)
- Mysql中json類(lèi)型查詢(xún)方法示例
- Mysql如何對(duì)json數(shù)據(jù)進(jìn)行查詢(xún)及修改
- Mysql內(nèi)儲(chǔ)存JSON字符串根據(jù)條件進(jìn)行查詢(xún)
- Mysql 直接查詢(xún)存儲(chǔ)的Json字符串中的數(shù)據(jù)
- Mysql將查詢(xún)結(jié)果集轉(zhuǎn)換為JSON數(shù)據(jù)的實(shí)例代碼
- MySQL中查詢(xún)json格式的字段實(shí)例詳解
- MySQL中查詢(xún)JSON字段的實(shí)現(xiàn)示例
相關(guān)文章
在SQL語(yǔ)句中我們可能會(huì)經(jīng)?；旌鲜褂玫絆R和AND，其中可能會(huì)出現(xiàn)一些小問(wèn)題，下面小編來(lái)講一講它的使用技巧2019-05-05
需求源自項(xiàng)目中的MemCache需求，開(kāi)始想用MemCached（官方站點(diǎn)：http://memcached.org/ ），但這個(gè)在Linux下面應(yīng)用廣泛的開(kāi)源軟件無(wú)官方支持的Windows版本2011-12-12
MySQL數(shù)據(jù)庫(kù)遠(yuǎn)程訪問(wèn)權(quán)限設(shè)置方式
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)遠(yuǎn)程訪問(wèn)權(quán)限設(shè)置方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-04-04
mysql group by having 實(shí)例代碼
mysql中g(shù)roup by語(yǔ)句用于分組查詢(xún)，可以根據(jù)給定數(shù)據(jù)列的每個(gè)成員對(duì)查詢(xún)結(jié)果進(jìn)行分組統(tǒng)計(jì)，最終得到一個(gè)分組匯總表， 經(jīng)常和having一起使用,需要的朋友可以參考下2016-11-11
索引就是一種數(shù)據(jù)結(jié)構(gòu)，這種結(jié)構(gòu)類(lèi)似，鏈表，樹(shù)等等。但是比它們要復(fù)雜的多，索引（index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)(有序)，本文詳細(xì)介紹了MySQL索引，感興趣的同學(xué)可以參考閱讀2023-04-04
MySQL實(shí)戰(zhàn)記錄之如何快速定位慢SQL
這可能是困然很多人的一個(gè)問(wèn)題,MySQL通過(guò)慢查詢(xún)?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的SQL語(yǔ)句,下面這篇文章主要給大家介紹了關(guān)于MySQL實(shí)戰(zhàn)記錄之如何快速定位慢SQL的相關(guān)資料,需要的朋友可以參考下2022-03-03
關(guān)于django連接mysql數(shù)據(jù)庫(kù)并進(jìn)行數(shù)據(jù)庫(kù)的創(chuàng)建的問(wèn)題
這篇文章主要介紹了django連接mysql數(shù)據(jù)庫(kù)并進(jìn)行數(shù)據(jù)庫(kù)的創(chuàng)建,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-06-06
這篇文章主要為大家詳細(xì)介紹了mysql登錄警告問(wèn)題的解決方法，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-10-10
在本文中，總結(jié)了開(kāi)發(fā)過(guò)程中最為常見(jiàn)的幾種 MySQL 拋出的異常以及如何解決，包括高版本驅(qū)動(dòng)的問(wèn)題、時(shí)區(qū)配置問(wèn)題、SSL 連接問(wèn)題等。于我個(gè)人而言，這一篇足以解決目前項(xiàng)目中所有遇到的MySQL問(wèn)題。同時(shí)，也希望本文能對(duì) MySQL 數(shù)據(jù)庫(kù)初學(xué)者有一定的引導(dǎo)入門(mén)作用。2021-05-05
mysql如何創(chuàng)建和刪除唯一索引(unique key)
這篇文章主要介紹了mysql如何創(chuàng)建和刪除唯一索引(unique key)問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12