MySQL?中的?JSON?查詢案例詳解
MySQL 的 JSON 路徑格式
MySQL 使用特定的 JSON 路徑表達式語法來導航和提取 JSON 文檔中的數(shù)據(jù)
基本結(jié)構(gòu)
MySQL 中的 JSON 路徑遵循以下通用格式
$[路徑組件]
路徑組件詳解
| 操作符 | 描述 | 示例 | | ----------- | --------- | --------------------- | | $ | 根對象 | $ | | . 或 [] | 成員訪問 | $.name 或 $['name'] | | [*] | 數(shù)組通配符 | $.items[*] | | [n] | 數(shù)組索引 | $[0] | | [m to n] | 數(shù)組范圍 | $[1 to 3] | | ** | 遞歸通配符 | $**.price |
1. 根對象 ($)
$表示整個 JSON 文檔
2. 成員訪問 (. 或 [])
- 點號表示法：
$.store.book - 括號表示法：
$['store']['book'] - 當鍵名包含特殊字符或空格時使用括號表示法
3. 數(shù)組訪問
- 所有元素：
$[*]或$.array[*] - ???????指定索引：
$[0]計數(shù)是從0開始 - 范圍：
$[1 to 3]（MySQL 8.0.26+）
4. 通配符
*匹配當前層級所有成員/元素**遞歸搜索所有路徑（MySQL 8.0.26+）
特殊語法元素
1. 過濾表達式 (MySQL 8.0.4+)
$.items[?(@.price > 10)]
? 引入過濾表達式
@ 表示當前元素
2. 路徑范圍 (MySQL 8.0.26+)
$[1 to 3] // 第1到第3個元素 $[last-1] // 倒數(shù)第二個元素 $[last-2 to last] // 最后三個元素
實際示例
簡單路徑
-- 提取標量值
SELECT JSON_EXTRACT('{"name": "張三", "age": 30}', '$.name');
-- 數(shù)組元素, 輸出 "b", 注意是帶雙引號的
SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1]');復雜路徑
-- 嵌套對象
SELECT JSON_EXTRACT('{"store": {"book": {"title": "MySQL指南"}}}', '$.store.book.title');
-- 對象數(shù)組
SELECT JSON_EXTRACT('{"items": [{"id": 1}, {"id": 2}]}', '$.items[*].id');簡寫操作符
MySQL 提供常用操作的簡寫形式
->: 等同于JSON_EXTRACT()- ???????
->>: 等同于JSON_UNQUOTE(JSON_EXTRACT())
-- 以下兩種寫法等價： SELECT json_column->'$.name'; SELECT JSON_EXTRACT(json_column, '$.name'); -- 以下兩種寫法等價（返回去除引號的字符串）： SELECT json_column->>'$.name'; SELECT JSON_UNQUOTE(JSON_EXTRACT(json_column, '$.name'));
注意
- 路徑表達式區(qū)分大小寫
- 不存在的路徑返回 NULL（不會報錯）
**遞歸操作符可能影響性能- 過濾表達式支持比較運算符：
=、!=、<、>等
MySQL 的 JSON_TABLE 函數(shù)
使用過 JSON_EXTRACT 函數(shù)都知道, 這樣獲取的結(jié)果還不是真正的行列結(jié)構(gòu), MySQL 8.0 引入的 JSON_TABLE 函數(shù)可以將 JSON 數(shù)據(jù)轉(zhuǎn)換為關(guān)系型表格格式, 將數(shù)組中的每個元素轉(zhuǎn)換成表格中的一行數(shù)據(jù).
JSON_TABLE 的功能
- 將 JSON 數(shù)組展開為多行記錄
- 提取嵌套的 JSON 對象屬性
- 將半結(jié)構(gòu)化數(shù)據(jù)轉(zhuǎn)為結(jié)構(gòu)化數(shù)據(jù)
JSON_TABLE 用法
JSON_TABLE(
json_doc, -- JSON 類型的字段或值
path_expression -- JSON 路徑表達式
COLUMNS( -- 新表的列定義
column_name column_type PATH json_path [on_empty] [on_error],
...
)
) [AS] alias參數(shù)說明
- json_doc：可以是 JSON 字符串字面量, 或者表中的 JSON 類型列
- path_expression：指向要展開的 JSON 數(shù)組的路徑
- COLUMNS：定義輸出列的結(jié)構(gòu)
column_name：生成的列名column_type：數(shù)據(jù)類型（如 VARCHAR, INT, JSON 等）PATH：指定數(shù)據(jù)提取路徑- alias：必須提供的表別名
實際案例
將整數(shù)數(shù)組展開為一列多行
SELECT *
FROM JSON_TABLE(
'[1, 2, 3]',
'$[*]' COLUMNS(
rowid FOR ORDINALITY,
value INT PATH '$'
)
) AS t;輸出
rowid | value
------+-------
1 | 1
2 | 2
3 | 3
將對象數(shù)組展開為多列多行
SELECT *
FROM JSON_TABLE(
'[{"name":"張三","age":25},{"name":"李四","age":30}]',
'$[*]' COLUMNS(
name VARCHAR(20) PATH '$.name',
age INT PATH '$.age',
adult VARCHAR(3) PATH '$.age' DEFAULT '否' ON EMPTY
)
) AS t;輸出
name | age | adult
-----+-----+------
張三 | 25 | 否
李四 | 30 | 否
在數(shù)據(jù)表中展開
如果JSON是表中的一個字段, 可以使用 table_1 CROSS JOIN JSON_TABLE(...) 展開, 例如一個表 v_video 的字段 result 為 JSON 字段, 需要展開 result 中的一個成員 sequences, 寫成SQL如下
SELECT
e.id,
e.match_id,
e.result->>'$.id' AS json_id,
j.tag->>'$.sf' AS sf_value,
j.tag->>'$.ef' AS ef_value,
j.tag->>'$.ef' - j.tag->>'$.sf'AS duration
FROM
v_video e
CROSS JOIN JSON_TABLE(
e.result->'$.sequences',
'$[*]' COLUMNS (
tag JSON PATH '$'
)
) AS j ON e.match_id = 294上面的SQL, 通過 CROSS JOIN JSON_TABLE 將每一行 e.result 字段下的 sequences 數(shù)組展開, 每個數(shù)組元素成為新字段 tag, 這時候還是一個 JSON, 然后在SELECT 中通過->>抽取其中的值, 得到完全展開的一個新表.
高級用法
FOR ORDINALITY 子句
生成自增的行號列
COLUMNS(
id FOR ORDINALITY,
...
)嵌套路徑處理
COLUMNS(
NESTED PATH '$.nested_obj' COLUMNS(
sub_col1 INT PATH '$.prop1',
sub_col2 VARCHAR(10) PATH '$.prop2'
)
)上面的例子用嵌套可以改寫為
SELECT
j.id,
j.sf,
j.ef,
j.ef - j.sf AS duration
FROM
v_video e
CROSS JOIN
JSON_TABLE(
e.result->'$.sequences',
'$[*]' COLUMNS (
id FOR ORDINALITY,
NESTED PATH '$' COLUMNS(
ef INT PATH '$.ef',
sf INT PATH '$.sf'
)
)
) AS j ON e.match_id = 294上面的SQL, 通過 NESTED PATH ... COLUMNS(...) 將展開后數(shù)組中的一個JSON元素進一步展開為多個字段.
錯誤處理
COLUMNS(
ef INT PATH '$.ef' NULL ON EMPTY NULL ON ERROR,
sf INT PATH '$.sf' DEFAULT '0' ON EMPTY NULL ON ERROR
)格式是
on_empty:
{NULL | DEFAULT json_string | ERROR} ON EMPTY
on_error:
{NULL | DEFAULT json_string | ERROR} ON ERROR注意事項
- MySQL 版本要高于8.0
- 路徑表達式必須指向 JSON 數(shù)組, 注意是數(shù)組
- 必須為結(jié)果集指定別名
- 在 FROM 子句和 JOIN 子句中都可以使用
- 在性能上, 對大數(shù)據(jù)集使用 JSON_TABLE 可能較慢, 可以為 JSON 列創(chuàng)建函數(shù)索引提高查詢性能
到此這篇關(guān)于MySQL 中的 JSON 查詢案例詳解的文章就介紹到這了,更多相關(guān)MySQL JSON 查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL最常用也最需要優(yōu)化的就是limit，MySQL的limit給分頁帶來了極大的方便，但數(shù)據(jù)量一大的時候，limit的性能就急劇下降,需要的朋友可以參考下2016-12-12
mysql group_concat 實現(xiàn)把分組字段寫成一行的方法示例
這篇文章主要介紹了mysql group_concat實現(xiàn)把分組字段寫成一行的方法,結(jié)合實例形式分析了group_concat函數(shù)的功能、查詢用法及相關(guān)操作技巧,需要的朋友可以參考下2019-10-10
這篇文章主要為大家詳細介紹了linux二進制通用包安裝mysql5.6.20的相關(guān)資料，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-01-01
這篇文章主要給大家介紹了關(guān)于MySQL用戶賬戶管理和權(quán)限管理的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2018-12-12
MySQL中查詢?nèi)罩九c慢查詢?nèi)罩镜幕緦W習教程
這篇文章主要介紹了MySQL中查詢?nèi)罩九c慢查詢?nèi)罩镜幕緦W習教程,文中還提到了MySQL自帶的Mysqldumpslow日志分析工具的使用,需要的朋友可以參考下2015-12-12
在開發(fā)中我們的業(yè)務需求有時候是復雜的,多張表聯(lián)合查詢的時候是有多種方式的,面對不同的需求,靈活使用不同的表連接方式，這篇文章主要給大家介紹了關(guān)于mysql表內(nèi)連和外連的相關(guān)資料,需要的朋友可以參考下2024-01-01
本文介紹了如何在Linux系統(tǒng)中使用logrotate工具對MySQL慢查詢?nèi)罩具M行自動輪轉(zhuǎn)和管理配置,包括創(chuàng)建配置文件、設置輪轉(zhuǎn)頻率、文件命名、權(quán)限、執(zhí)行腳本以及測試配置的方法,感興趣的朋友一起看看吧2025-03-03
這篇文章主要介紹了MySQL中的樂觀鎖和悲觀鎖和MVCC全面解析的相關(guān)資料，幫助大家更好的理解和學習MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2021-01-01
MGR是MySQL數(shù)據(jù)庫未來發(fā)展的一個重要方向，本文重點給大家介紹MGR集群搭建過程分析，對MGR集群搭建相關(guān)知識感興趣的朋友一起看看吧2022-02-02
ySQL數(shù)據(jù)庫在運行過程中可能會出現(xiàn)空間碎片的問題,本文就來介紹一下MySQL 空間碎片的查看與回收 ,具有一定的參考價值,感興趣的可以了解一下2025-02-02