MySQL?中的?JSON?查詢案例詳解
MySQL 的 JSON 路徑格式
MySQL 使用特定的 JSON 路徑表達(dá)式語法來導(dǎo)航和提取 JSON 文檔中的數(shù)據(jù)
基本結(jié)構(gòu)
MySQL 中的 JSON 路徑遵循以下通用格式
$[路徑組件]
路徑組件詳解
| 操作符 | 描述 | 示例 | | ----------- | --------- | --------------------- | | $ | 根對象 | $ | | . 或 [] | 成員訪問 | $.name 或 $['name'] | | [*] | 數(shù)組通配符 | $.items[*] | | [n] | 數(shù)組索引 | $[0] | | [m to n] | 數(shù)組范圍 | $[1 to 3] | | ** | 遞歸通配符 | $**.price |
1. 根對象 ($)
$表示整個(gè) JSON 文檔
2. 成員訪問 (. 或 [])
- 點(diǎn)號表示法：
$.store.book - 括號表示法：
$['store']['book'] - 當(dāng)鍵名包含特殊字符或空格時(shí)使用括號表示法
3. 數(shù)組訪問
- 所有元素：
$[*]或$.array[*] - ???????指定索引：
$[0]計(jì)數(shù)是從0開始 - 范圍：
$[1 to 3]（MySQL 8.0.26+）
4. 通配符
*匹配當(dāng)前層級所有成員/元素**遞歸搜索所有路徑（MySQL 8.0.26+）
特殊語法元素
1. 過濾表達(dá)式 (MySQL 8.0.4+)
$.items[?(@.price > 10)]
? 引入過濾表達(dá)式
@ 表示當(dāng)前元素
2. 路徑范圍 (MySQL 8.0.26+)
$[1 to 3] // 第1到第3個(gè)元素 $[last-1] // 倒數(shù)第二個(gè)元素 $[last-2 to last] // 最后三個(gè)元素
實(shí)際示例
簡單路徑
-- 提取標(biāo)量值
SELECT JSON_EXTRACT('{"name": "張三", "age": 30}', '$.name');
-- 數(shù)組元素, 輸出 "b", 注意是帶雙引號的
SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1]');復(fù)雜路徑
-- 嵌套對象
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())
-- 以下兩種寫法等價(jià)： SELECT json_column->'$.name'; SELECT JSON_EXTRACT(json_column, '$.name'); -- 以下兩種寫法等價(jià)（返回去除引號的字符串）： SELECT json_column->>'$.name'; SELECT JSON_UNQUOTE(JSON_EXTRACT(json_column, '$.name'));
注意
- 路徑表達(dá)式區(qū)分大小寫
- 不存在的路徑返回 NULL（不會報(bào)錯(cuò)）
**遞歸操作符可能影響性能- 過濾表達(dá)式支持比較運(yùn)算符：
=、!=、<、>等
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ù)組中的每個(gè)元素轉(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 路徑表達(dá)式
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ù)數(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是表中的一個(gè)字段, 可以使用 table_1 CROSS JOIN JSON_TABLE(...) 展開, 例如一個(gè)表 v_video 的字段 result 為 JSON 字段, 需要展開 result 中的一個(gè)成員 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ù)組展開, 每個(gè)數(shù)組元素成為新字段 tag, 這時(shí)候還是一個(gè) JSON, 然后在SELECT 中通過->>抽取其中的值, 得到完全展開的一個(gè)新表.
高級用法
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ù)組中的一個(gè)JSON元素進(jìn)一步展開為多個(gè)字段.
錯(cuò)誤處理
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注意事項(xiàng)
- MySQL 版本要高于8.0
- 路徑表達(dá)式必須指向 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 5.7 安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-02-02
從創(chuàng)建數(shù)據(jù)庫到存儲過程與用戶自定義函數(shù)的小感
從創(chuàng)建數(shù)據(jù)庫到存儲過程與用戶自定義函數(shù)的小感，深入的學(xué)習(xí)mysql2011-09-09
MySQL與PHP的基礎(chǔ)與應(yīng)用專題之?dāng)?shù)據(jù)查詢
MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典MySQL?AB?公司開發(fā)，屬于?Oracle?旗下產(chǎn)品。MySQL?是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一，本系列將帶你掌握php與mysql的基礎(chǔ)應(yīng)用，本篇從數(shù)據(jù)的查詢開始2022-02-02
MySQL表鎖、頁面鎖和行鎖的作用及其優(yōu)缺點(diǎn)對比分析
MySQL中的表鎖、頁面鎖和行鎖各有特點(diǎn),適用于不同的場景,表鎖鎖定整個(gè)表,適用于批量操作和MyISAM存儲引擎,頁面鎖鎖定數(shù)據(jù)頁,適用于舊版本InnoDB,行鎖僅鎖定某一行,適用于高并發(fā)事務(wù),選擇合適的鎖機(jī)制可以顯著提升MySQL的并發(fā)性能和數(shù)據(jù)一致性,感興趣的朋友一起學(xué)習(xí)吧2025-03-03
引是為了加速對表中數(shù)據(jù)行的檢索而創(chuàng)建的一種分散存儲的數(shù)據(jù)結(jié)構(gòu),下面這篇文章主要給大家介紹了關(guān)于MySQL索引機(jī)制的詳細(xì)解析及原理的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-04-04
mysql查找刪除表中重復(fù)數(shù)據(jù)方法總結(jié)
在本篇文章中小編給大家整理了關(guān)于mysql查找刪除表中重復(fù)數(shù)據(jù)方法和相關(guān)知識點(diǎn)，需要的朋友們參考下。2019-05-05
MySQL賬號權(quán)限管理指南(創(chuàng)建賬戶與授權(quán))
在MySQL數(shù)據(jù)庫管理中,合理創(chuàng)建用戶賬號并分配精確權(quán)限是保障數(shù)據(jù)安全的核心環(huán)節(jié),直接使用root賬號進(jìn)行所有操作不僅危險(xiǎn)且難以審計(jì)操作行為,今天我們來全面解析MySQL賬號創(chuàng)建與權(quán)限分配的專業(yè)方法,需要的朋友可以參考下2025-06-06
數(shù)據(jù)庫的一個(gè)難題是無法將SQL語句的執(zhí)行結(jié)果轉(zhuǎn)換為想要的格式。本節(jié)，我們將通過學(xué)習(xí)格式轉(zhuǎn)換中具有代表性的行列轉(zhuǎn)換和嵌套式側(cè)欄的生成方法，深入理解一下其中有重要作用的外連接2022-04-04
本篇文章是對mysql查詢語句的使用進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-05-05
mysql 加了 skip-name-resolve不能鏈接數(shù)據(jù)庫問題的解決方法
這篇文章主要介紹了mysql 加了 skip-name-resolve不能鏈接數(shù)據(jù)庫問題的解決方法,需要的朋友可以參考下2016-04-04