MySQL for JSON

Lostgreen發表於2024-11-27
MySqlJSON

SQL for JSON

MySQL 提供了強大的 JSON 支援，使開發者能夠高效地儲存和查詢結構化資料。下面，我們透過例項來介紹 MySQL 的 JSON 基本用法，包括如何儲存、查詢、更新和操作 JSON 資料。

1. 建立一個帶有 JSON 列的表

我們可以將 JSON 資料型別用於表中的欄位，例如：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    profile JSON
);

解釋：

  • profile 列的型別為 JSON，專門用於儲存 JSON 資料。
  • name 列用於儲存使用者的名稱。

2. 插入 JSON 資料

可以直接插入符合 JSON 格式的資料：

INSERT INTO users (name, profile) 
VALUES 
('Alice', '{"age": 25, "city": "New York", "skills": ["MySQL", "Python"]}'),
('Bob', '{"age": 30, "city": "San Francisco", "skills": ["Java", "C++"]}');

注意：插入的資料必須是有效的 JSON 格式，否則 MySQL 會報錯。

3. 查詢 JSON 資料

MySQL 提供了很多內建函式來操作 JSON 資料。

(1) 提取 JSON 資料 (-> 和 ->>)

  • -> 用於獲取 JSON 物件中的子物件。
  • ->> 用於獲取 JSON 資料的字串值。
-- 獲取 Alice 的城市
SELECT name, profile->>'$.city' AS city 
FROM users 
WHERE name = 'Alice';

結果：

+-------+-----------+
| name  | city      |
+-------+-----------+
| Alice | New York  |
+-------+-----------+

(2) 查詢巢狀屬性

JSON 路徑支援深層巢狀查詢：

-- 查詢技能中的第一個值
SELECT name, profile->'$.skills[0]' AS first_skill 
FROM users;

結果：

+-------+-------------+
| name  | first_skill |
+-------+-------------+
| Alice | "MySQL"     |
| Bob   | "Java"      |
+-------+-------------+

也可以使用JSON_EXTRACT獲取指定的資料

例1：mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
結果： 20 
例2：mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]'); 
結果： [20, 10] --多個值拼接為陣列
例3：mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]'); 
結果： [30, 40]

JSON不同於字串，如果直接和整個JSON欄位比較，不會查詢到結果

SELECT * FROM muscleape WHERE category = '{"id": 1,"name": "muscleape"}';----結果查詢不到資料。

這時候需要利用CAST把字串轉化成為JSON

SELECT * FROM muscleape WHERE category = CAST('{"id": 1,"name": "muscleape"}' AS JSON);
這樣就可以正常找到結果了

還可以利用JSON_CONTAINS查詢內部值

SELECT * FROM muscleape 
WHERE JSON_CONTAINS(category,  '1',  '$.id');-- 可以查詢到資料
注意第二個引數只能是字串

4. 更新 JSON 資料

可以使用 JSON_SET、JSON_REMOVE 等函式更新 JSON 資料。

(1) 更新 JSON 中的值

-- 將 Alice 的城市改為 "Los Angeles"
UPDATE users 
SET profile = JSON_SET(profile, '$.city', 'Los Angeles') 
WHERE name = 'Alice';

JSON_SET( )函式——插入新值，並覆蓋已存在的值
UPDATE muscleape SET category = JSON_SET(category,
'$.host’,  'localhost’,   '$.url',  'www.muscleape.com') 
WHERE id = 1;

JSON_REPLACE( )函式——只替換已存在的值
UPDATE muscleape SET category = JSON_REPLACE(category,
'$.host',  '127.0.0.1',   '$.address',  'shandong') 
WHERE id = 1;

(2) 刪除 JSON 中的屬性

-- 刪除 Bob 的技能資訊
UPDATE users 
SET profile = JSON_REMOVE(profile, '$.skills') 
WHERE name = 'Bob';

(3) 插入值

UPDATE muscleape SET category = JSON_INSERT(category,
    '$.name', 'muscleape_new',  '$.url', 'muscleape.com')  WHERE id = 1;
-----當JSON資料中已經存在name屬性而沒有url屬性時，name值不會被修改，而url的值被新增進去。

5. 查詢包含特定 JSON 資料的行

使用 JSON_CONTAINS 函式檢查 JSON 中是否包含特定值。

-- 查詢擁有 "Python" 技能的使用者
SELECT name 
FROM users 
WHERE JSON_CONTAINS(profile->'$.skills', '"Python"');

結果：

+-------+
| name  |
+-------+
| Alice |
+-------+

7. 聚合和複雜查詢

MySQL 的 JSON 函式可以與其他 SQL 語法結合，進行復雜的資料分析。

-- 統計使用者的年齡總和
SELECT SUM(profile->>'$.age') AS total_age 
FROM users;

在 MySQL 中，JSON_ARRAYAGG 和 JSON_OBJECTAGG 是兩個用於聚合資料的函式，它們的輸出形式分別為 JSON 陣列和 JSON 物件。這兩個函式常用於將關係型資料轉化為 JSON 格式，便於前端處理或其他用途。

1. JSON_ARRAYAGG：聚合為 JSON 陣列

功能

將查詢結果中的每一行的資料聚合成一個 JSON 陣列。

語法

JSON_ARRAYAGG(expression)
  • expression: 要放入陣列的值，可以是列、表示式或函式的返回值。

示例 1：簡單使用

假設有一個表 employees：

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department VARCHAR(50)
);

INSERT INTO employees VALUES 
(1, 'Alice', 'HR'),
(2, 'Bob', 'Engineering'),
(3, 'Charlie', 'HR');

我們希望將所有員工的姓名聚合成一個 JSON 陣列：

SELECT JSON_ARRAYAGG(name) AS employee_names 
FROM employees;

結果：

+----------------------+
| employee_names       |
+----------------------+
| ["Alice", "Bob", "Charlie"] |
+----------------------+

示例 2：按部門分組

將員工的姓名按部門聚合：

SELECT 
    department,
    JSON_ARRAYAGG(name) AS employee_names 
FROM employees
GROUP BY department;

結果：

+-------------+--------------------+
| department  | employee_names     |
+-------------+--------------------+
| HR          | ["Alice", "Charlie"] |
| Engineering | ["Bob"]            |
+-------------+--------------------+

2. JSON_OBJECTAGG：聚合為 JSON 物件

功能

將查詢結果中的每一行的兩列資料聚合為鍵值對形式的 JSON 物件。

語法

JSON_OBJECTAGG(key_expression, value_expression)
  • key_expression: 物件的鍵。
  • value_expression: 物件的值。

示例 1：簡單使用

繼續使用 employees 表，我們希望建立一個以員工 ID 為鍵、姓名為值的 JSON 物件：

SELECT JSON_OBJECTAGG(id, name) AS employee_map 
FROM employees;

結果：

+-------------------------------+
| employee_map                 |
+-------------------------------+
| {"1": "Alice", "2": "Bob", "3": "Charlie"} |
+-------------------------------+

示例 2：按部門分組

將每個部門的員工 ID 和姓名聚合為 JSON 物件：

SELECT 
    department,
    JSON_OBJECTAGG(id, name) AS employee_details 
FROM employees
GROUP BY department;

結果：

+-------------+-----------------------------------+
| department  | employee_details                 |
+-------------+-----------------------------------+
| HR          | {"1": "Alice", "3": "Charlie"}   |
| Engineering | {"2": "Bob"}                     |
+-------------+-----------------------------------+

3. 比較與應用場景

函式 輸出格式 適用場景
JSON_ARRAYAGG JSON 陣列 將多行資料組合成一個 JSON 陣列，適用於無鍵值對的簡單場景。
JSON_OBJECTAGG JSON 物件（鍵值對） 生成鍵值對結構的 JSON 資料，例如主鍵-值對映或配置表等。

4. 結合使用的場景

假設我們有一個包含員工及其技能的表 employee_skills：

CREATE TABLE employee_skills (
    employee_id INT,
    skill VARCHAR(50)
);

INSERT INTO employee_skills VALUES 
(1, 'Management'),
(1, 'Recruitment'),
(2, 'Java'),
(2, 'MySQL'),
(3, 'Communication');

我們希望為每個員工生成一個以員工 ID 為鍵、技能列表為值的 JSON 物件：

SELECT 
    JSON_OBJECTAGG(employee_id, skills) AS employee_skills_map
FROM (
    SELECT 
        employee_id,
        JSON_ARRAYAGG(skill) AS skills
    FROM employee_skills
    GROUP BY employee_id
) AS aggregated_skills;

結果：

+-----------------------------------------+
| employee_skills_map                     |
+-----------------------------------------+
| {"1": ["Management", "Recruitment"],    |
|  "2": ["Java", "MySQL"],                |
|  "3": ["Communication"]}                |
+-----------------------------------------+

JSON_TABLE的使用

exp1:將陣列中的每個物件元素轉為一個關係表中的一行，表中的列對應了每個物件中的成員，其中for ordinality定義了自增長計數器列。

SELECT  * FROM  JSON_TABLE(
    '[{"x": 10, "y": 11}, {"x": 20, "y": 21}]','$[*]'   //表示陣列中的所有元素
    COLUMNS (   id FOR ORDINALITY,
                x INT PATH '$.x',
                y INT PATH '$.y'  
            )    
        ) AS t;

exp2:提取陣列中第2行，設定值為空時的預設值

SELECT  * FROM  JSON_TABLE(  
    '[{"x": 10, "y": 11}, {"x": 20}]','$[1]'   //取陣列中的第2個元素
            COLUMNS (  id FOR ORDINALITY,
            x INT PATH '$.x',
            y INT PATH '$.y'  DEFAULT '100' ON EMPTY 
            )    
        ) AS t;

exp3:拉平內嵌的陣列

SELECT *  FROM  JSON_TABLE(   
    '[{"x":10,"y":[11, 12]},{"x":20,"y":[21, 22]}]',
        '$[*]'  COLUMNS (  
            x INT PATH '$.x',
            NESTED PATH '$.y[*]' COLUMNS (y INT PATH '$') 
         //展開 y 對應的陣列，並將 y 陣列中的每個元素放入名稱為 y 的列中  
         )
) AS t;

res:
x     y
10    11
10    12
20    21
20    22

exp4:拉平內嵌的物件

SELECT  *   FROM   JSON_TABLE(    
    '[{"x":10,"y":{"a":11,"b":12}},{"x":20,"y":{"a":21,"b":22}}]',
        '$[*]'  COLUMNS (   x INT PATH '$.x',
            NESTED PATH '$.y' COLUMNS (   
                ya INT PATH '$.a',  
                yb INT PATH '$.b'  
                )          
            )
      ) AS t; 

res:
x   ya  yb
10  11  12
20  21  22

exp5:實際查詢時應用

SELECT c1, c2, t.at, tt.bt, tt.ct, JSON_EXTRACT(c3, '$.*') 
FROM t1 AS m 
JOIN 
JSON_TABLE(   m.c3, 
  '$.*'  COLUMNS(
    at VARCHAR(10) PATH '$.a' DEFAULT '1' ON EMPTY, 
    bt VARCHAR(10) PATH '$.b' DEFAULT '2' ON EMPTY, 
    ct VARCHAR(10) PATH '$.c' DEFAULT '3' ON EMPTY
  )
) AS tt
ON m.c1 > tt.at;

例題：設MySQL資料庫中有包含json資料列的關係R1（sno int, cno int , eval json）記錄學生學習具體課程的學習記錄的學號、課號、教師評語，完成下列小題：
（1）若要插入記錄：1號學生學了3號課程，評語列的json物件由屬性名“evaluation”和體教師評語的記錄陣列作為“evaluation”屬性的值構成，該陣列包含三條記錄，分別是{ “stage”：1, “result”:”ok”}、{ “stage”：2, “result”:”good”}、{ “stage”：3, “result”:” excellent”}，描述該生在該課程的3個階段的教師評語。請寫出MySQL中插入該記錄的面向json擴充的SQL語句。

INSERT INTO R1 (sno, cno, eval) VALUES
 (1, 3, '{"evaluation":  [{"stage":1, "result":"ok"},
 {"stage":2, "result":"good"}, {"stage":3, "result":" excellent"}]  }');

（2）如果要在關係R1中查出上述第（1）小題插入的記錄的前兩個stage的資訊，要求結果屬性列表為“學號、課號、階段號stage值及該階段的教師評語result值”，並且不同的階段輸出在不同的行，請寫出MySQL中插入該記錄的面向json擴充的SQL語句。

因為記錄只有一條，那麼需要用where篩選時需要進行匯出
SELECT sno，cno, tt.stage, tt.result FROM R1, 
JSON_TABLE (R1.eval,  '$.evauation[*]'   COLUMNS(
   stage int PATH '$.stage',
   result VARCHAR(20) PATH '$.result')
) AS tt  
WHERE tt.stage<3;

註釋：
SQL查詢中，R1 表與 JSON_TABLE 函式生成的虛擬表 tt 之間看似沒有進行顯式的連線操作，但實際上，JSON_TABLE 函式本身就是一個連線點。JSON_TABLE 函式用於解析 JSON 格式的資料，並將解析出來的資料轉換為關係表的形式，以便與 SQL 語句中的其他表進行聯合查詢。在這個過程中，JSON_TABLE 會自動將 R1 表中的每一行與 JSON 資料中的每一個元素進行關聯，從而實現了隱式的連線。

讓我們更詳細地看看這個查詢的各個部分：

  1. SELECT sno, cno, tt.stage, tt.result FROM R1,:

    • 這部分定義了查詢的欄位來源。sno 和 cno 來自 R1 表，而 tt.stage 和 tt.result 來自 JSON_TABLE 生成的虛擬表 tt。

  2. JSON_TABLE (R1.eval, '$.evauation[*]' COLUMNS(stage int PATH '$.stage', result VARCHAR(20) PATH '$.result')) AS tt:

    • JSON_TABLE 函式接收兩個引數：R1.eval 和一個 JSON 路徑表示式 '$.evauation[*]'。
    • R1.eval 是 R1 表中的一個 JSON 欄位，包含了需要解析的 JSON 資料。
    • '$.*evaluation[*]' 是一個 JSON 路徑表示式，用於遍歷 eval 欄位中的 evaluation 陣列中的每一個元素。
    • COLUMNS 子句定義了從每個 JSON 元素中提取的列及其路徑。stage 和 result 分別對應 JSON 元素中的 stage 和 result 欄位。
    • AS tt 將 JSON_TABLE 函式生成的虛擬表命名為 tt。

  3. WHERE tt.stage < 3:

    • 這個條件用於過濾 tt 表中的行，只保留 stage 值小於 3 的記錄。

隱式連線的實現

儘管在 FROM 子句中沒有顯式地使用 JOIN 關鍵字，JSON_TABLE 函式實際上已經將 R1 表中的每一行與 eval 欄位中的 JSON 陣列中的每一個元素進行了關聯。具體來說，JSON_TABLE 會為 R1 表中的每一行生成一個或多個虛擬行，每個虛擬行對應 eval 欄位中的一個 JSON 元素。這些虛擬行與 R1 表中的原始行一起構成了最終的查詢結果。

示例解釋

假設 R1 表的資料如下：

sno cno eval
1 101 {"evaluation": [{"stage": 1, "result": "pass"}, {"stage": 2, "result": "fail"}]}
2 102 {"evaluation": [{"stage": 1, "result": "pass"}, {"stage": 3, "result": "pass"}]}

執行上述查詢後，JSON_TABLE 會生成如下的虛擬表 tt：

sno cno stage result
1 101 1 pass
1 101 2 fail
2 102 1 pass
2 102 3 pass

然後，WHERE tt.stage < 3 條件會過濾掉 stage 值為 3 的行，最終結果為：

sno cno stage result
1 101 1 pass
1 101 2 fail
2 102 1 pass

總結

雖然在 FROM 子句中沒有顯式的 JOIN 關鍵字，但 JSON_TABLE 函式實際上已經實現了 R1 表與 JSON 資料的隱式連線。這種連線方式使得可以從 JSON 格式的資料中提取結構化資訊，並與表中的其他欄位一起進行查詢和過濾。

相關文章