腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

MySQL進(jìn)行JSON查詢的詳細(xì)教程

更新時(shí)間：2025年03月28日 08:46:02 作者：Milton

在MySQL中,一般會(huì)使用特定的 JSON 路徑表達(dá)式語(yǔ)法來(lái)導(dǎo)航和提取 JSON 文檔中的數(shù)據(jù),本文將為大家詳細(xì)介紹一下具體的查詢方法,希望對(duì)大家有所幫助

MySQL 的 JSON 路徑格式

MySQL 使用特定的 JSON 路徑表達(dá)式語(yǔ)法來(lái)導(dǎo)航和提取 JSON 文檔中的數(shù)據(jù)

基本結(jié)構(gòu)

MySQL 中的 JSON 路徑遵循以下通用格式

$[路徑組件]

路徑組件詳解

| 操作符 | 描述 | 示例 |
| ----------- | --------- | --------------------- |
| $ \| 根對(duì)象 \| $ |
| . 或 [] | 成員訪問(wèn) | $.name 或 $['name'] |
| [*] | 數(shù)組通配符 | $.items[*] |
| [n] | 數(shù)組索引 | $[0] |
| [m to n] | 數(shù)組范圍 | $[1 to 3] |
| ** | 遞歸通配符 | $**.price |

1. 根對(duì)象 ($)

$ 表示整個(gè) JSON 文檔

2. 成員訪問(wèn) (. 或 [])

點(diǎn)號(hào)表示法：$.store.book
括號(hào)表示法：$['store']['book']
當(dāng)鍵名包含特殊字符或空格時(shí)使用括號(hào)表示法

3. 數(shù)組訪問(wèn)

所有元素：$[*] 或 $.array[*]
指定索引：$[0] 計(jì)數(shù)是從0開(kāi)始
范圍：$[1 to 3]（MySQL 8.0.26+）

4. 通配符

* 匹配當(dāng)前層級(jí)所有成員/元素
** 遞歸搜索所有路徑（MySQL 8.0.26+）

特殊語(yǔ)法元素

1. 過(guò)濾表達(dá)式 (MySQL 8.0.4+)

$.items[?(@.price > 10)]

? 引入過(guò)濾表達(dá)式
@ 表示當(dāng)前元素

2. 路徑范圍 (MySQL 8.0.26+)

$[1 to 3]       // 第1到第3個(gè)元素
$[last-1]       // 倒數(shù)第二個(gè)元素
$[last-2 to last] // 最后三個(gè)元素

實(shí)際示例

簡(jiǎn)單路徑

-- 提取標(biāo)量值
SELECT JSON_EXTRACT('{"name": "張三", "age": 30}', '$.name');
 
-- 數(shù)組元素, 輸出 "b", 注意是帶雙引號(hào)的
SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1]');

復(fù)雜路徑

-- 嵌套對(duì)象
SELECT JSON_EXTRACT('{"store": {"book": {"title": "MySQL指南"}}}', '$.store.book.title');
 
-- 對(duì)象數(shù)組
SELECT JSON_EXTRACT('{"items": [{"id": 1}, {"id": 2}]}', '$.items[*].id');

簡(jiǎn)寫(xiě)操作符

MySQL 提供常用操作的簡(jiǎn)寫(xiě)形式

-> : 等同于 JSON_EXTRACT()
->> : 等同于 JSON_UNQUOTE(JSON_EXTRACT())

-- 以下兩種寫(xiě)法等價(jià)：
SELECT json_column->'$.name';
SELECT JSON_EXTRACT(json_column, '$.name');
 
-- 以下兩種寫(xiě)法等價(jià)（返回去除引號(hào)的字符串）：
SELECT json_column->>'$.name';
SELECT JSON_UNQUOTE(JSON_EXTRACT(json_column, '$.name'));

注意

路徑表達(dá)式區(qū)分大小寫(xiě)
不存在的路徑返回 NULL（不會(huì)報(bào)錯(cuò)）
** 遞歸操作符可能影響性能
過(guò)濾表達(dá)式支持比較運(yùn)算符：=、!=、<、> 等

MySQL 的 JSON_TABLE 函數(shù)

使用過(guò) 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ù)組展開(kāi)為多行記錄
提取嵌套的 JSON 對(duì)象屬性
將半結(jié)構(gòu)化數(shù)據(jù)轉(zhuǎn)為結(jié)構(gòu)化數(shù)據(jù)

JSON_TABLE 用法

JSON_TABLE(
    json_doc,       -- JSON 類(lèi)型的字段或值
    path_expression -- JSON 路徑表達(dá)式
    COLUMNS(        -- 新表的列定義
        column_name column_type PATH json_path [on_empty] [on_error],
        ...
    )
) [AS] alias

參數(shù)說(shuō)明

json_doc：可以是 JSON 字符串字面量, 或者表中的 JSON 類(lèi)型列
path_expression：指向要展開(kāi)的 JSON 數(shù)組的路徑
COLUMNS：定義輸出列的結(jié)構(gòu)
- column_name：生成的列名
- column_type：數(shù)據(jù)類(lèi)型（如 VARCHAR, INT, JSON 等）
- PATH：指定數(shù)據(jù)提取路徑
alias：必須提供的表別名

實(shí)際案例

將整數(shù)數(shù)組展開(kāi)為一列多行

SELECT *
FROM JSON_TABLE(
    '[1, 2, 3]',
    '$[*]' COLUMNS(
        rowid FOR ORDINALITY,
        value INT PATH '$'
    )
) AS t;

輸出

rowid | value
------+-------
1 | 1
2 | 2
3 | 3

將對(duì)象數(shù)組展開(kāi)為多列多行

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ù)表中展開(kāi)

如果JSON是表中的一個(gè)字段, 可以使用 table_1 CROSS JOIN JSON_TABLE(...) 展開(kāi), 例如一個(gè)表 v_video 的字段 result 為 JSON 字段, 需要展開(kāi) result 中的一個(gè)成員 sequences, 寫(xiě)成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, 通過(guò) CROSS JOIN JSON_TABLE 將每一行 e.result 字段下的 sequences 數(shù)組展開(kāi), 每個(gè)數(shù)組元素成為新字段 tag, 這時(shí)候還是一個(gè) JSON, 然后在SELECT 中通過(guò)->>抽取其中的值, 得到完全展開(kāi)的一個(gè)新表.

高級(jí)用法

FOR ORDINALITY 子句

生成自增的行號(hào)列

COLUMNS(
    id FOR ORDINALITY,
    ...
)

嵌套路徑處理

COLUMNS(
    NESTED PATH '$.nested_obj' COLUMNS(
        sub_col1 INT PATH '$.prop1',
        sub_col2 VARCHAR(10) PATH '$.prop2'
    )
)

上面的例子用嵌套可以改寫(xiě)為

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, 通過(guò) NESTED PATH ... COLUMNS(...) 將展開(kāi)后數(shù)組中的一個(gè)JSON元素進(jìn)一步展開(kāi)為多個(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 子句中都可以使用
在性能上, 對(duì)大數(shù)據(jù)集使用 JSON_TABLE 可能較慢, 可以為 JSON 列創(chuàng)建函數(shù)索引提高查詢性能

到此這篇關(guān)于MySQL進(jìn)行JSON查詢的詳細(xì)教程的文章就介紹到這了,更多相關(guān)MySQL JSON查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: