文章詳情頁
MySQL實現查詢處理JSON數據的示例詳解
瀏覽：3日期：2023-07-28 19:59:55
目錄前言JSON_CONTAINSJSON_CONTAINS_PATHJSON_EXTRACTcolumn->pathcolumn->>pathJSON_KEYSJSON_OVERLAPSJSON_SEARCHJSON_VALUEMEMBEROF總結前言
最近在做項目時，表 A 有多個字段，其中一個字段 info 把當前項目用不到的冗余的數據按照 JSON 格式都存了進來。隨著項目的推進，有些冗余字段需要單獨成一列。新增一列之后，需要把 info中對應的數據刷入新增列，這就需要從 MySQL 中讀取 JSON 數據。
當時想到的方法，就是寫個程序，批量查詢數據，把 info 字段查詢出來后反序列化，取出其中的key-value，然后再存入數據庫。后面查詢資料，發現 MySQL 已經提供了從 JSON 數據中查找和比較的函數，極大地方便了數據處理！
這是在沒有寫這篇文章前，查詢資料寫出來的刷數據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?tablewhere?code?=?'xxx';其次，為了方便后面的學習和測試，我們新建一張表，建表語句如下:
create?table?`userinfo`(????`id`???bigint?unsigned?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵'?PRIMARY?KEY,????`info`?longtext????????NOT?NULL?COMMENT?'用戶信息');接下來我們就看下MySQL提供的 JSON 查詢和比較函數，比較常用的應該就是 JSON_EXTRACT 、column->path、column->>path 和 JSON_VALUE 四個函數，可以按需學習哦！
JSON_CONTAINS該函數用于判斷一個 JSON 文檔是否包含另一個 JSON 文檔。如果提供了路徑，用于判斷 JSON 文檔相應路徑下的數據是否包含另一個JSON 文檔。
語法
JSON_CONTAINS(target,candidate[,path])
target: 必填。目標 JSON 文檔candidate: 必填。被包含的 JSON 文檔path: 可選。路徑返回值
如果 target 或者 target 在 path 路徑下的數據包含 candidate，返回 1；否則返回 0如果任意一個必填參數為 NULL，或者路徑 path 在 target 中不存在，返回 NULL如果 target 或者 candidate 不是一個有效的JSON 文檔，查詢報錯如果提供的 path 不是一個有效的路徑表達式，或者 path 包含通配符 '*' 或者 '**' ，查詢報錯規則
對于兩個簡單類型的變量，如果兩者類型相同、該類型可比較且值相等，則 target 包含 candidate對于兩個數組類型的變量，如果 candidate 數組中的每個元素，都存在于 target 中的某些元素中，則 target 包含 candidate對于一個非數組類型 candidate 和數組類型 target，如果 candidate 存在于 target 的某些元素中，則 target 包含 candidate對于兩個對象，如果 candidate 的每個 key 都在 target 中存在，且對應的 value 值也被包含，則 target 包含 candidate測試
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,?兩個簡單類型,?1?包含?1select??JSON_CONTAINS(info,'1','$.d')?from?userinfo?where?id=1;?#?1,?非數組和數組類型比較,?[1,2,3]?包含?1select??JSON_CONTAINS(info,'[1,2]','$.d')?from?userinfo?where?id=1;?#?1,?兩個數組類型比較,?[1,2,3]?包含?數組類型?[1,2]select??JSON_CONTAINS(info,'[1,2,4]','$.d')?from?userinfo?where?id=1;?#?0,?兩個數組類型比較,?[1,2,3]?不包含?數組類型?[1,2,4]select??JSON_CONTAINS(info,'{'a':1}')?from?userinfo?where?id=1;?#?1,?兩個對象比較,?target?中存在?key?'a',且?value?包含select??JSON_CONTAINS(info,'{'a':2}')?from?userinfo?where?id=1;?#?0,?兩個對象比較,?target?存在?key?'a',但?value?不包含select??JSON_CONTAINS(info,'{'d':2}')?from?userinfo?where?id=1;?#?1,?兩個對象比較,?target?存在?key?'d',且?value?包含select??JSON_CONTAINS(info,'{'a':1,'d':2}')?from?userinfo?where?id=1;?#?1,?兩個對象比較,?target?存在?key?'a'?和?'d'?,且?value?均包含select??JSON_CONTAINS(info,'{'a':1,'d':[2,3]}')?from?userinfo?where?id=1;?#?1,?兩個對象比較,?target?存在?key?'a'?和?'d'?,且?value?均包含JSON_CONTAINS_PATH該函數用于判斷一個 JSON 文檔是否包含一個或者多個路徑 path
語法
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path...])]
json_doc: 必填。一個 JSON 文檔one_or_all: 必填。值為 'one' 或者 'all'，指定至少一個還是所有 path 存在于 json_docpath: 必填。至少填寫一個路徑返回值
one_or_all = 'one' 時，如果存在一個 path 存在于 json_doc，返回 1 ; 否則返回 0one_or_all = 'all' 時，所有 path 存在于 json_doc 返回 1 ; 否則返回 0如果有參數為 NULL，則返回 NULL如果 json_doc 不是有效的JSON數據，或者 path 不是合法的表達式，或者 one_or_all 參數 取值不是 'one' 或者 'all'，返回 error測試
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，?至少一個存在即可，且路徑?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該函數用于從 JSON 字段中查詢路徑 path 對應的 value 值
語法
JSON_EXTRACT(json_doc, path[,path...])
json_doc: 必填。一個 JSON 文檔path: 必填。至少填寫一個路徑返回值
如果只匹配到一個path，則返回對應的 value如果匹配到多個 path，則將所有的 value 組合成一個數組返回，value 在數組的順序和 提供的 path 順序保持一致如果參數為 NULL，或者未在 json_doc 中匹配到對應的 path，則返回NULL如果 json_doc 不是合法的 JSON 文檔，或者 path 不是合法的路徑表達式，則返回errorinsert?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;?#?1select?json_extract(info,'$.c.d')?from?userinfo?where?id=3;?#?4select?json_extract(info,'$.d')?from?userinfo?where?id=3;?#?[1,2,3]select?json_extract(info,'$.d[0]')?from?userinfo?where?id=3;?#?1select?json_extract(info,'$.d[3]')?from?userinfo?where?id=3;?#?NULLselect?json_extract(info,'$.f')?from?userinfo?where?id=3;?#?NULLselect?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]如果只查詢一個 path，可以使用接下來介紹的 -> 操作符
column->pathJSON_EXTRACT 只有兩個參數時的縮寫。
如下兩個查詢是等價的：
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?????|+-----------------------------------------------------------------------+------+和列操作一樣，這個符號可以用于 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'->>' 符號相對于 '->'，增加了去除引號的功能。如果一個 JSON 文檔中，key 對應的 value 是字符串類型，那么如下三個表達式返回相同的結果：
JSON_UNQUOTE(JSON_EXTRACT(column,path))JSON_UNQUOTE(column->path)column->>pathselect?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;?#?tomselect?json_unquote(info->'$.e.name')?as?name?from?userinfo?where?id=3;?#?tomselect?info->>'$.e.name'?as?name?from?userinfo?where?id=3;?#?tomJSON_KEYS該函數用于返回 JSON 文檔或者指定 path 下最頂層的所有 key
語法
JSON_KEYS(json_doc,[path])
json_doc: 必填。一個 JSON 文檔path: 選填。路徑返回值
返回 json_doc 或者指定 path 下最頂層的 key 數組如果任意參數為 NULL，或者 json_doc 不是一個對象（可能是個數組），或者根據 path 沒有定位到數據，則返回NULL如果 json_doc不是 JSON 對象，或者指定的路徑不合法，返回error測試
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該函數用于判斷兩個JSON文檔是否有重疊
語法
JSON_OVERLAPS(json_doc1, json_doc2)
json_doc1: 必填。JSON文檔1json_doc2: 必填。JSON文檔2返回值
如果兩個JSON文檔有重疊，返回 1；否則返回 0如果參數為NULL，返回NULL重疊邏輯
如果兩個JSON文檔均為簡單類型，相當于判等操作，相等則為重疊如果兩個JSON文檔均為數組，如果至少有一個元素相同，則為重疊如果兩個JSON文檔均為對象，如果至少有 key-value 相同，則為重疊測試
兩個基礎元素，就是簡單的判等操作
SELECT?JSON_OVERLAPS('5',?'5');?#?1,?相等SELECT?JSON_OVERLAPS(''5'',?'5');?#?0,?類型不同，不相等對于數組，需要有元素相同；如果是多維數組，子數組元素需要完全一樣
SELECT?JSON_OVERLAPS('[1,3,5,7]',?'[2,5,7]');??#?1,?存在相同的元素?5?和?7SELECT?JSON_OVERLAPS('[1,3,5,7]',?'[2,6,7]');?#?1,?存在相同的元素?7SELECT?JSON_OVERLAPS('[1,3,5,7]',?'[2,6,8]');?#?0,?沒有相同元素SELECT?JSON_OVERLAPS('[[1,2],[3,4],5]',?'[1,[2,3],[4,5]]');?#?0,?沒有相同元素SELECT?JSON_OVERLAPS('[[1,2],[3,4],5]',?'[[1,2],[2,3],[4,5]]');?#?1,?有相同元素?[1,2]如果是對象，需要 key-value 完全一樣
SELECT?JSON_OVERLAPS('{'a':1,'b':10,'d':10}',?'{'c':1,'e':10,'f':1,'d':10}');?#?1,?相同key-value?'d':10SELECT?JSON_OVERLAPS('{'a':1,'b':10,'d':10}',?'{'a':5,'e':10,'f':1,'d':20}');?#?0,?沒有相同元素SELECT?JSON_OVERLAPS('{'a':1,'b':10,'d':[20,30]}',?'{'a':5,'e':10,'f':1,'d':[20]}');?#?0,?沒有相同元素如果一個基礎類型和數組類型比較，基礎類型會被轉成數組類型
SELECT?JSON_OVERLAPS('[4,5,6,7]',?'6');??#?1,?[4,5,6,7]和?[6]?有相同元素?6SELECT?JSON_OVERLAPS('[4,5,6,7]',?''6'');?#?0,?類型不同，沒有相同元素JSON_SEARCH對于給定的字符串，返回該字符串在 JSON 文檔中的路徑
語法
JSON_SEARCH(json_doc, one_or_all, search_str, escape_char, path...)
json_doc: 必填。JSON文檔
one_or_all: 必填。取值只能為 one 或者 all
one: 返回第一個匹配的路徑all: 以數組的形式返回所有匹配到的路徑，去重，無順序search_str: 必填。要查詢的字符串，可以使用通配符
%: 匹配0個或多個字符_: 匹配一個字符escape_char: 可選。如果 search_str 中包含 % 和 _，需要在他們之前添加轉移字符。默認是 \。
path: 可選。指定在具體路徑下搜索
返回值
JSON_SEARCH() 函數返回一個給定字符串在一個 JSON 文檔中的路徑。它返回一個路徑字符串或者由多個路徑組成的數組。
JSON_SEARCH() 函數將在以下情況下返回 NULL：
未搜索到指定的字符串JSON 文檔中不存在指定的 path任意一個參數為 NULLJSON_SEARCH() 函數將在以下情況下返回錯誤：
如果參數 json 不是有效的 JSON 文檔，MySQL 將會給出錯誤。如果參數 path 不是有效的路徑表達式, MySQL 將會給出錯誤。測試
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');?#?nullselect?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該函數的作用是：查詢 JSON 文檔 path 下的值
語法
JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])
on_empty:NULLERROR | DEFAULT valueON EMPTY
on_error:NULLERROR | DEFAULT valueON ERROR
參數
json_doc: 必填。JSON文檔
path: 必填。指定的路徑
RETURNING type: 可選。將結果轉為指定的類型，可以為如下類型：
FLOATDOUBLEDECIMALSIGNEDUNSIGNEDDATETIMEDATETIMEYEAR (MySQL 8.0.22 and later)CHARJSONNULLERROR | DEFAULT valueON EMPTY
可選。如果指定了，它決定了指定路徑下沒有數據的返回值：
NULL ON EMPTY: 如果指定路徑下沒有數據，JSON_VALUE() 函數將返回 NULL，這是默認的行為。
DEFAULT value ON EMPTY: 如果指定路徑下沒有數據，JSON_VALUE`() 函數將返回 value。
ERROR ON EMPTY: 如果指定路徑下沒有數據，JSON_VALUE() 函數將拋出一個錯誤。
NULLERROR | DEFAULT valueON ERROR
可選的。如果指定了，它決定了處理錯誤的邏輯：
NULL ON ERROR: 如果有錯誤，JSON_VALUE() 函數將返回 NULL，這是默認的行為。DEFAULT value ON ERROR: 如果有錯誤，JSON_VALUE() 函數將返回 value。ERROR ON ERROR: 如果有錯誤，JSON_VALUE() 函數將拋出一個錯誤。返回值
默認以字符串的格式，返回 JSON 文檔在指定的路徑上的值；如果使用 RETURNING type 子句，會把結果轉為 type 類型
測試
SELECT?JSON_VALUE('{'fname':?'Joe',?'lname':?'Palmer'}',?'$.fname');?#?JoeSELECT?JSON_VALUE('{'item':?'shoes',?'price':?'49.95'}',?'$.price'?RETURNING?DECIMAL(4,2));?#?49.95SELECT?JSON_VALUE('{'item':?'shoes',?'price':?'49.95'}',?'$.total'?DEFAULT?100.00?ON?EMPTY);?#?100.00MEMBEROF該函數用于判斷value，是否是數組 json_array 的元素
語法
value MEMBER_OF (json_array)
value: 必填。任意值，可以是一個簡單類型或者 JSON
json_array: 必填。一個JSON數組
返回值
如果 value 是 json_array 中的元素，返回1；否則返回0
測試
SELECT?17?MEMBER?OF('[23,?'abc',?17,?'ab',?10]');?#?1SELECT?'17'?MEMBER?OF('[23,?'abc',?17,?'ab',?10]');?#?0,?類型不一致SELECT?'ab'?MEMBER?OF('[23,?'abc',?17,?'ab',?10]');?#?1SELECT?CAST('[4,5]'?AS?JSON)?MEMBER?OF('[[3,4],[4,5]]');?#?1SELECT?JSON_ARRAY(4,5)?MEMBER?OF('[[3,4],[4,5]]');?#?1現在我們可以回過頭來看下文章開頭要優化的SQL：
這是表 info 字段存儲的數據，如果字段有數據，存儲對應的數據類型；如果沒有數據，存儲 null。但是 string 類型的 value 有引號，我們想去掉引號；其次對于 null 值，也想替換成默認值
{????'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?tablewhere?code?=?'xxx';json_extract 是為了拿到對應的 value，里面的 replace(）是為了去掉引號，外面的 replace 是為了將 null 替換為默認值。對于去掉引號，我們可以使用 column ->> path 簡化：
select?id,???????replace(info?->>?'$.budget_mode',?'null',?''),???????replace(info?->>?'$.budget',?'null',?0),???????replace(info?->>?'$.bid',?'null',?0)from?ad_adwhere?id?=?6993;總結本篇文章一共介紹了如下幾個函數：
JSON_CONTAINS：判斷一個 JSON 文檔是否包含另一個 JSON 文檔JSON_CONTAINS_PATH：判斷一個JSON文檔，是否包含一個或者多個路徑 pathJSON_EXTRACT：從 JSON 文檔中查詢路徑對應的 value 值column->path：JSON_EXTRACT 只有兩個參數時的縮寫column->>path：相對于 '->'，增加了去除 引號 的功能JSON_KEYS：返回 JSON 文檔或者指定 path 下最頂層的所有 keyJSON_OVERLAPS：判斷兩個 JSON 文檔是否有重疊JSON_SEARCH：返回給定字符串在 JSON 文檔中的路徑JSON_VALUE：查詢 JSON 文檔 path 下的值MEMBEROF：判斷一個值是否為一個 JSON 數組中的元素到此這篇關于MySQL實現查詢處理JSON數據的示例詳解的文章就介紹到這了,更多相關MySQL查詢處理JSON數據內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！
排行榜
網公網安備