MariaDB 資料庫 – JSON

adminLinux, MariaDB, Mysql, RedHat / CentOS

Loading

測試環境為 CentOS 8 x86_64

使用關聯式資料庫（ 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 , Processors 與 MEM (Memory) 資料.

{
     "CPU" :"Intel",
     "Processors":"Core i7",
     "MEM":"16G"
}

使用 Nested Object 的 JSON 請參考 – https://benjr.tw/103211

  • 輸入 JSON 資料 
    MariaDB [testdb]> INSERT INTO json_tb(notebook) VALUES ('{"CPU" :"Intel", "Processors":"Core i7","MEM":"16G"}') ;
Query OK, 1 row affected (0.002 sec)
MariaDB [testdb]> INSERT INTO json_tb(notebook) VALUES ('{"CPU" :"Intel", "Processors":"Core i7","MEM":"8G"}') ;
Query OK, 1 row affected (0.002 sec)

    我們可以利用 JSON_OBJECT() 函數把資料轉成 JSON 格式：

    MariaDB [testdb]> INSERT INTO json_tb(notebook) VALUES (JSON_OBJECT("CPU","Intel","Processors","Core i5","MEM","8G")) ;
Query OK, 1 row affected (0.003 sec)

    MariaDB [testdb]> SELECT * FROM json_tb;
+----+--------------------------------------------------------+
| id | notebook                                               |
+----+--------------------------------------------------------+
|  1 | {"CPU" :"Intel", "Processors":"Core i7","MEM":"16G"}   |
|  2 | {"CPU" :"Intel", "Processors":"Core i7","MEM":"8G"}    |
|  3 | {"CPU": "Intel", "Processors": "Core i5", "MEM": "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", "Processors", "MEM"] |
|  2 | ["CPU", "Processors", "MEM"] |
|  3 | ["CPU", "Processors", "MEM"] |
+----+------------------------------+
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,'$.Processors') AS Processors , JSON_EXTRACT(notebook,'$.MEM') AS Memory FROM json_tb;
+----+---------+------------+--------+
| id | CPU     | Processors | Memory |
+----+---------+------------+--------+
|  1 | "Intel" | "Core i7"  | "16G"  |
|  2 | "Intel" | "Core i5"  | "8G"   |
|  3 | "Intel" | "Core i7"  | "8G"   |
+----+---------+------------+--------+
3 rows in set (0.001 sec)

    使用 JSON_VALUE() 函數查詢資料的純量值 (scalar).

    MariaDB [testdb]> SELECT id , JSON_VALUE(notebook,'$.CPU') AS CPU , JSON_VALUE(notebook,'$.Processors') AS Processors , JSON_VALUE(notebook,'$.MEM') AS Memory FROM json_tb;
+----+-------+------------+--------+
| id | CPU   | Processors | Memory |
+----+-------+------------+--------+
|  1 | Intel | Core i7    | 16G    |
|  2 | Intel | Core i5    | 8G     |
|  3 | Intel | Core i7    | 8G     |
+----+-------+------------+--------+
3 rows in set (0.001 sec)

    搭配 WHERE , ORDER 來使用.

    MariaDB [testdb]> SELECT id , JSON_VALUE(notebook,'$.CPU') AS CPU , JSON_VALUE(notebook,'$.Processors') AS Processors , JSON_VALUE(notebook,'$.MEM') AS Memory FROM json_tb WHERE JSON_VALUE(notebook,'$.Processors') LIKE 'Core i7' ORDER BY CAST(REPLACE(JSON_VALUE(notebook,'$.MEM'),'G','') AS UNSIGNED);
+----+-------+------------+--------+
| id | CPU   | Processors | Memory |
+----+-------+------------+--------+
|  3 | Intel | Core i7    | 8G     |
|  1 | Intel | Core i7    | 16G    |
+----+-------+------------+--------+
2 rows in set (0.001 sec)

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

    MariaDB [testdb]> UPDATE json_tb SET notebook = '{"CPU" :"Intel", "Processors":"Core i7"}' WHERE id = 1;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [testdb]> SELECT * FROM json_tb WHERE id=1;
+----+------------------------------------------+
| id | notebook                                 |
+----+------------------------------------------+
|  1 | {"CPU" :"Intel", "Processors":"Core i7"} |
+----+------------------------------------------+
1 row in set (0.001 sec)

MariaDB [testdb]> UPDATE json_tb SET notebook = '{"CPU" :"Intel", "Processors":"Core i7","MEM":"32G"}' WHERE id = 1;
Query OK, 1 row affected (0.006 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

    使用 JSON_INSERT 函數 新增內容,如該鍵值已存在則不新增.

    MariaDB [testdb]> UPDATE json_tb SET notebook = JSON_INSERT( notebook , '$.disk', '512G') WHERE id = 1;
Query OK, 1 row affected (0.006 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [testdb]> SELECT * FROM json_tb WHERE id=1;
+----+-------------------------------------------------------------------------+
| id | notebook                                                                |
+----+-------------------------------------------------------------------------+
|  1 | {"CPU": "Intel", "Processors": "Core i7", "MEM": "32G", "disk": "512G"} |
+----+-------------------------------------------------------------------------+
1 row in set (0.002 sec)

    MariaDB [testdb]> UPDATE json_tb SET notebook = JSON_INSERT( notebook , '$.disk', '1T') WHERE id = 1;
Query OK, 0 rows affected (0.001 sec)
Rows matched: 1  Changed: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM json_tb WHERE id=1;
+----+-------------------------------------------------------------------------+
| id | notebook                                                                |
+----+-------------------------------------------------------------------------+
|  1 | {"CPU": "Intel", "Processors": "Core i7", "MEM": "32G", "disk": "512G"} |
+----+-------------------------------------------------------------------------+
1 row in set (0.001 sec)

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

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

MariaDB [testdb]> SELECT * FROM json_tb WHERE id=1;
+----+-----------------------------------------------------------------------+
| id | notebook                                                              |
+----+-----------------------------------------------------------------------+
|  1 | {"CPU": "Intel", "Processors": "Core i7", "MEM": "32G", "disk": "1T"} |
+----+-----------------------------------------------------------------------+
1 row in set (0.001 sec)

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

    MariaDB [testdb]> UPDATE json_tb SET notebook = JSON_REPLACE( notebook , '$.disk', '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": "Intel", "Processors": "Core i7", "MEM": "32G", "disk": "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": "Intel", "Processors": "Core i7", "MEM": "32G"} |
+----+---------------------------------------------------------+
1 row in set (0.001 sec)

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

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

發佈留言

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

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