MariaDB 資料庫 – 使用 Nested Object 的 JSON

adminLinux, MariaDB, Mysql, RedHat / CentOS

Loading

測試環境為 CentOS 8 x86_64

這邊有 JSON 無使用 Nested Object 簡單版可以參考 – https://benjr.tw/103200

使用關聯式資料庫（ RDBMS : Relational Database Management System ）的 MariaDB 也可以儲存與查詢 NoSQL ( Not Only SQL : 使用非關聯式資料庫的資料庫,資料儲存不需要固定的欄位 ) 的 JSON ( JavaScript Object Notation ）資料格式.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Database changed

定義包含 json 欄位 (notebook) 的資料表 (json_tb).

CREATE TABLE json_tb ( 
id int(10) unsigned NOT NULL AUTO_INCREMENT, 
notebook JSON, PRIMARY KEY (id) , CHECK (JSON_VALID(notebook))
);

使用 JSON_VALID 函數檢查 JSON 格式是否正確,回傳 1 表示正確,0 (下面範例少了 ” )表示不正確.

MariaDB [testdb]> SELECT JSON_VALID('{"CPU" :"Intel"}') AS 'JSON Status';
+-------------+
| JSON Status |
+-------------+
|           1 |
+-------------+
1 row in set (0.000 sec)

MariaDB [testdb]> SELECT JSON_VALID('{"CPU" :"Intel}') AS 'JSON Status';
+-------------+
| JSON Status |
+-------------+
|           0 |
+-------------+
1 row in set (0.000 sec)

MariaDB [testdb]> CREATE TABLE json_tb ( id int(10) unsigned NOT NULL AUTO_INCREMENT, notebook JSON, PRIMARY KEY (id) , CHECK (JSON_VALID(notebook)));
Query OK, 0 rows affected (0.005 sec)

MariaDB [testdb]> DESCRIBE json_tb;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| notebook | longtext         | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
2 rows in set (0.002 sec)

JSON 資料類型：

  1. 物件 (Object) : 使用 { } , 無序成對 (pair) 的 {鍵 Key name :值 value} 方式來儲存,鍵值之間使用逗號來分隔.
  2. 陣列 (Array) : 使用 [ ] , 資料彼此間使用逗號來分割,如：[value, value] .
  3. 數字 (Number) : 直接使用整數或是浮點數.
  4. 字串 (String) : 使用 “” (括號)來表示字串.
  5. 布林函數 (Boolean) : 使用 TRUE 或 FALSE.
  6. NULL 類型.

依據 JSON 格式定義了 CPU 與 MEM (Memory) 資料.

{
     "CPU":
     [ {"CPU socket":1,"Vendor":"Intel","Processors":"Core i7"},
     {"CPU socket":2,"Vendor":"Intel","Processors":"Core i7"} ], 
     "MEM":
     [{"DIMM slot":1,"Type":"DDR4","Size":"16G"},
     {"DIMM slot":2,"Type":"DDR4","Size":"16G"}]
}
  • 輸入 JSON 資料 
    MariaDB [testdb]> INSERT INTO json_tb(notebook) VALUES ('{"CPU":[{"CPU socket":1,"Vendor":"Intel","Processors":"Core i7"},{"CPU socket":2,"Vendor":"Intel","Processors":"Core i7"}], "MEM":[{"DIMM slot":1,"Type":"DDR4","Size":"16G"},{"DIMM slot":2,"Type":"DDR4","Size":"16G"}]}');
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> INSERT INTO json_tb(notebook) VALUES ('{"CPU":[{"CPU socket":1,"Vendor":"Intel","Processors":"Core i5"},{"CPU socket":2,"Vendor":"Intel","Processors":"Core i5"}], "MEM":[{"DIMM slot":1,"Type":"DDR4","Size":"8G"},{"DIMM slot":2,"Type":"DDR4","Size":"8G"}]}');
Query OK, 1 row affected (0.003 sec)

MariaDB [testdb]> INSERT INTO json_tb(notebook) VALUES ('{"CPU":[{"CPU socket":1,"Vendor":"Intel","Processors":"Core i7"}], "MEM":[{"DIMM slot":1,"Type":"DDR4","Size":"8G"}]}');
Query OK, 1 row affected (0.003 sec)

    MariaDB [testdb]> SELECT * FROM json_tb;
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | notebook                                                                                                                                                                                                                   |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"CPU":[{"CPU socket":1,"Vendor":"Intel","Processors":"Core i7"},{"CPU socket":2,"Vendor":"Intel","Processors":"Core i7"}], "MEM":[{"DIMM slot":1,"Type":"DDR4","Size":"16G"},{"DIMM slot":2,"Type":"DDR4","Size":"16G"}]} |
|  2 | {"CPU":[{"CPU socket":1,"Vendor":"Intel","Processors":"Core i5"},{"CPU socket":2,"Vendor":"Intel","Processors":"Core i5"}], "MEM":[{"DIMM slot":1,"Type":"DDR4","Size":"8G"},{"DIMM slot":2,"Type":"DDR4","Size":"8G"}]}   |
|  3 | {"CPU":[{"CPU socket":1,"Vendor":"Intel","Processors":"Core i7"}], "MEM":[{"DIMM slot":1,"Type":"DDR4","Size":"8G"}]}                                                                                                      |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.001 sec)
  • 查詢 JSON 資料
    JSON 資料以 {鍵 Key name :值 value} 存放,使用 JSON_KEYS() 函數查詢資料的鍵 Key .

    MariaDB [testdb]> SELECT id , JSON_KEYS(notebook) FROM json_tb; 
+----+---------------------+
| id | JSON_KEYS(notebook) |
+----+---------------------+
|  1 | ["CPU", "MEM"]      |
|  2 | ["CPU", "MEM"]      |
|  3 | ["CPU", "MEM"]      |
+----+---------------------+
3 rows in set (0.001 sec)

    我的 JSON 檔案定義為 Nested Object ,這時候可以指定 PATH 來查詢.如查詢 CPU 路徑下的鍵,因為我有使用 [ ] 陣列,所以需要再指定 $.CPU[#] (# 從 0 開始).

    MariaDB [testdb]> SELECT id , JSON_KEYS(notebook,'$.CPU[0]') FROM json_tb; 
+----+----------------------------------------+
| id | JSON_KEYS(notebook,'$.CPU[0]')         |
+----+----------------------------------------+
|  1 | ["CPU socket", "Vendor", "Processors"] |
|  2 | ["CPU socket", "Vendor", "Processors"] |
|  3 | ["CPU socket", "Vendor", "Processors"] |
+----+----------------------------------------+
3 rows in set (0.001 sec)

    第三筆資料沒定義第二個 CPU 所以顯示為 NULL .

    MariaDB [testdb]> SELECT id , JSON_KEYS(notebook,'$.CPU[1]') FROM json_tb; 
+----+----------------------------------------+
| id | JSON_KEYS(notebook,'$.CPU[1]')         |
+----+----------------------------------------+
|  1 | ["CPU socket", "Vendor", "Processors"] |
|  2 | ["CPU socket", "Vendor", "Processors"] |
|  3 | NULL                                   |
+----+----------------------------------------+
3 rows in set (0.001 sec)

    下面使用 JSON_VALUE 以及 JSON_EXTRACT 函數,來讀取 JSON 欄位的資料,回傳值會略微為不同,詳細請參考 https://benjr.tw/103237 .

    使用 JSON_EXTRACT() 函數查詢資料的值 value.

    MariaDB [testdb]> SELECT id , JSON_EXTRACT(notebook,'$.CPU') AS CPU , JSON_EXTRACT(notebook,'$.MEM') AS Memory FROM json_tb;
+----+--------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+
| id | CPU                                                                                                                            | Memory                                                                                             |
+----+--------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+
|  1 | [{"CPU socket": 1, "Vendor": "Intel", "Processors": "Core i7"}, {"CPU socket": 2, "Vendor": "Intel", "Processors": "Core i7"}] | [{"DIMM slot": 1, "Type": "DDR4", "Size": "16G"}, {"DIMM slot": 2, "Type": "DDR4", "Size": "16G"}] |
|  2 | [{"CPU socket": 1, "Vendor": "Intel", "Processors": "Core i5"}, {"CPU socket": 2, "Vendor": "Intel", "Processors": "Core i5"}] | [{"DIMM slot": 1, "Type": "DDR4", "Size": "8G"}, {"DIMM slot": 2, "Type": "DDR4", "Size": "8G"}]   |
|  3 | [{"CPU socket": 1, "Vendor": "Intel", "Processors": "Core i7"}]                                                                | [{"DIMM slot": 1, "Type": "DDR4", "Size": "8G"}]                                                   |
+----+--------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+
3 rows in set (0.001 sec)

    或是指定 Nested Object PATH 來查詢.

    MariaDB [testdb]> SELECT id , JSON_EXTRACT(notebook,'$.CPU[0].Vendor') AS CPU FROM json_tb;
+----+---------+
| id | CPU     |
+----+---------+
|  1 | "Intel" |
|  2 | "Intel" |
|  3 | "Intel" |
+----+---------+
3 rows in set (0.001 sec)

    使用 JSON_VALUE() 函數查詢資料的純量值 (scalar) value,需指定 Nested Object PATH 來查詢.

    MariaDB [testdb]> SELECT id , JSON_VALUE(notebook,'$.CPU[0].Vendor') AS CPU FROM json_tb;
+----+-------+
| id | CPU   |
+----+-------+
|  1 | Intel |
|  2 | Intel |
|  3 | Intel |
+----+-------+
3 rows in set (0.000 sec)

    MariaDB [testdb]> SELECT id , JSON_VALUE(notebook,'$.CPU[0].CPU socket') AS CPU FROM json_tb;
+----+------+
| id | CPU  |
+----+------+
|  1 | 1    |
|  2 | 1    |
|  3 | 1    |
+----+------+
3 rows in set (0.001 sec)

MariaDB [testdb]> SELECT id , JSON_VALUE(notebook,'$.CPU[1].CPU socket') AS CPU FROM json_tb;
+----+------+
| id | CPU  |
+----+------+
|  1 | 2    |
|  2 | 2    |
|  3 | NULL |
+----+------+
3 rows in set (0.001 sec)

    搭配 WHERE , ORDER 來使用.

    MariaDB [testdb]> SELECT id , JSON_VALUE(notebook,'$.CPU[0].Processors') AS CPU0 , JSON_VALUE(notebook,'$.CPU[1].Processors') AS CPU1 , CAST(IFNULL(REPLACE(JSON_VALUE(notebook,'$.MEM[0].Size'),'G',''),0) AS UNSIGNED) + CAST(IFNULL(REPLACE(JSON_VALUE(notebook,'$.MEM[1].Size'),'G',''),0) AS UNSIGNED) AS Memory FROM json_tb WHERE JSON_VALUE(notebook,'$.CPU[0].Processors') LIKE 'Core i7' ORDER BY Memory;
+----+---------+---------+--------+
| id | CPU0    | CPU1    | Memory |
+----+---------+---------+--------+
|  3 | Core i7 | NULL    |      8 |
|  1 | Core i7 | Core i7 |     32 |
+----+---------+---------+--------+
2 rows in set (0.001 sec)
  • 修改 JSON 資料
    一次更新全部內容.

    MariaDB [testdb]> UPDATE json_tb SET notebook = '{"CPU":[{"CPU socket":1,"Vendor":"Intel","Processors":"Core i7"},{"CPU socket":2,"Vendor":"Intel","Processors":"Core i7"}], "MEM":[{"DIMM slot":1,"Type":"DDR4","Size":"32G"},{"DIMM slot":2,"Type":"DDR4","Size":"32G"}] ,"DISK":[{"SATA":1,"Size":"1T"},{"SATA":2,"Size":"512G"}]}' WHERE id = 1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [testdb]> SELECT * FROM json_tb WHERE id=1;
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | notebook                                                                                                                                                                                                                                                                             |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"CPU":[{"CPU socket":1,"Vendor":"Intel","Processors":"Core i7"},{"CPU socket":2,"Vendor":"Intel","Processors":"Core i7"}], "MEM":[{"DIMM slot":1,"Type":"DDR4","Size":"32G"},{"DIMM slot":2,"Type":"DDR4","Size":"32G"}] ,"DISK":[{"SATA":1,"Size":"1T"},{"SATA":2,"Size":"512G"}]} |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

    使用 JSON_SET 函數 新增內容,會覆蓋已存在的鍵值.

    MariaDB [testdb]> UPDATE json_tb SET notebook = JSON_SET( notebook , '$.DISK[0].Size', '2T') WHERE id = 1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [testdb]> SELECT * FROM json_tb WHERE id=1;
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | notebook                                                                                                                                                                                                                                                                                                             |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"CPU": [{"CPU socket": 1, "Vendor": "Intel", "Processors": "Core i7"}, {"CPU socket": 2, "Vendor": "Intel", "Processors": "Core i7"}], "MEM": [{"DIMM slot": 1, "Type": "DDR4", "Size": "32G"}, {"DIMM slot": 2, "Type": "DDR4", "Size": "32G"}], "DISK": [{"SATA": 1, "Size": "2T"}, {"SATA": 2, "Size": "512G"}]} |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

    使用 JSON_REPLACE 函數更新部分內容,僅替換已存在的鍵值.

    MariaDB [testdb]> UPDATE json_tb SET notebook = JSON_REPLACE( notebook , '$.DISK[0].Size', '4T') WHERE id = 1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [testdb]> SELECT * FROM json_tb WHERE id=1;
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | notebook                                                                                                                                                                                                                                                                                                             |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"CPU": [{"CPU socket": 1, "Vendor": "Intel", "Processors": "Core i7"}, {"CPU socket": 2, "Vendor": "Intel", "Processors": "Core i7"}], "MEM": [{"DIMM slot": 1, "Type": "DDR4", "Size": "32G"}, {"DIMM slot": 2, "Type": "DDR4", "Size": "32G"}], "DISK": [{"SATA": 1, "Size": "4T"}, {"SATA": 2, "Size": "512G"}]} |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
  • 移除 JSON 資料
    使用 JSON_REMOVE 函數刪除指定的鍵值.

    MariaDB [testdb]> UPDATE json_tb SET notebook = JSON_REMOVE( notebook , '$.DISK') WHERE id = 1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [testdb]> SELECT * FROM json_tb WHERE id=1;
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | notebook                                                                                                                                                                                                                                           |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"CPU": [{"CPU socket": 1, "Vendor": "Intel", "Processors": "Core i7"}, {"CPU socket": 2, "Vendor": "Intel", "Processors": "Core i7"}], "MEM": [{"DIMM slot": 1, "Type": "DDR4", "Size": "32G"}, {"DIMM slot": 2, "Type": "DDR4", "Size": "32G"}]} |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

更多關於 Json 函數,請參考 – https://mariadb.com/kb/en/json-functions/

沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料。