MySQL實(shí)現(xiàn)查詢處理JSON數(shù)據(jù)的示例詳解

更新時(shí)間：2023年06月29日 14:43:20 作者：CodePlayer

這篇文章主要為大家詳細(xì)介紹了MySQL如何實(shí)現(xiàn)查詢處理JSON數(shù)據(jù)，文中的示例代碼講解詳細(xì)，具有一定的借鑒價(jià)值，感興趣的小伙伴可以了解一下

前言

最近在做項(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è)程序，批量查詢數(shù)據(jù)，把 info 字段查詢出來(lái)后反序列化，取出其中的key-value，然后再存入數(shù)據(jù)庫(kù)。后面查詢資料，發(fā)現(xiàn) MySQL 已經(jīng)提供了從 JSON 數(shù)據(jù)中查找和比較的函數(shù)，極大地方便了數(shù)據(jù)處理！

這是在沒(méi)有寫(xiě)這篇文章前，查詢資料寫(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?'用戶信息'
);

接下來(lái)我們就看下MySQL提供的 JSON 查詢和比較函數(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 文檔，查詢報(bào)錯(cuò)
如果提供的 path 不是一個(gè)有效的路徑表達(dá)式，或者 path 包含通配符 '*' 或者 '**' ，查詢報(bào)錯(cuò)

規(guī)則

對(duì)于兩個(gè)簡(jiǎn)單類型的變量，如果兩者類型相同、該類型可比較且值相等，則 target 包含 candidate
對(duì)于兩個(gè)數(shù)組類型的變量，如果 candidate 數(shù)組中的每個(gè)元素，都存在于 target 中的某些元素中，則 target 包含 candidate
對(duì)于一個(gè)非數(shù)組類型 candidate 和數(shù)組類型 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)單類型,?1?包含?1
select??JSON_CONTAINS(info,'1','$.d')?from?userinfo?where?id=1;?#?1,?非數(shù)組和數(shù)組類型比較,?[1,2,3]?包含?1
select??JSON_CONTAINS(info,'[1,2]','$.d')?from?userinfo?where?id=1;?#?1,?兩個(gè)數(shù)組類型比較,?[1,2,3]?包含?數(shù)組類型?[1,2]
select??JSON_CONTAINS(info,'[1,2,4]','$.d')?from?userinfo?where?id=1;?#?0,?兩個(gè)數(shù)組類型比較,?[1,2,3]?不包含?數(shù)組類型?[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 字段中查詢路徑 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]

如果只查詢一個(gè) path，可以使用接下來(lái)介紹的 -> 操作符

column->path

JSON_EXTRACT 只有兩個(gè)參數(shù)時(shí)的縮寫(xiě)。

如下兩個(gè)查詢是等價(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 是字符串類型，那么如下三個(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)單類型，相當(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,?類型不同，不相等

對(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ǔ)類型和數(shù)組類型比較，基礎(chǔ)類型會(huì)被轉(zhuǎn)成數(shù)組類型

SELECT?JSON_OVERLAPS('[4,5,6,7]',?'6');??#?1,?[4,5,6,7]和?[6]?有相同元素?6
SELECT?JSON_OVERLAPS('[4,5,6,7]',?'"6"');?#?0,?類型不同，沒(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: 必填。要查詢的字符串，可以使用通配符

%: 匹配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ù)的作用是：查詢 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)為指定的類型，可以為如下類型：

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 類型

測(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.00

MEMBEROF

該函數(shù)用于判斷value，是否是數(shù)組 json_array 的元素

語(yǔ)法

value MEMBER_OF (json_array)

value: 必填。任意值，可以是一個(gè)簡(jiǎn)單類型或者 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,?類型不一致
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ù)類型；如果沒(méi)有數(shù)據(jù)，存儲(chǔ) null。但是 string 類型的 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 文檔中查詢路徑對(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：查詢 JSON 文檔 path 下的值
MEMBEROF：判斷一個(gè)值是否為一個(gè) JSON 數(shù)組中的元素

到此這篇關(guān)于MySQL實(shí)現(xiàn)查詢處理JSON數(shù)據(jù)的示例詳解的文章就介紹到這了,更多相關(guān)MySQL查詢處理JSON數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: