MariaDB 資料庫 – JSON 常用函數

測試環境為 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_OBJECT

JSON_OBJECT 函數 可以把一對 key / value 轉成 JSON 格式.

MariaDB [testdb]> SELECT JSON_OBJECT("Timestamp", "2021-07-01 14:30:00" , "Value", 55);
+---------------------------------------------------------------+
| JSON_OBJECT("Timestamp", "2021-07-01 14:30:00" , "Value", 55) |
+---------------------------------------------------------------+
| {"Timestamp": "2021-07-01 14:30:00", "Value": 55}             |
+---------------------------------------------------------------+
1 row in set (0.000 sec)

JSON_DETAILED

以剛剛的範例來看不好閱讀,可以使用 JSON_DETAILED 函數來變成 Nested Structures (階層式)方便閱讀的格式.

MariaDB [testdb]> SELECT JSON_DETAILED(JSON_OBJECT("Timestamp", "2021-07-01 14:30:00" , "Value", 55));
+------------------------------------------------------------------------------+
| JSON_DETAILED(JSON_OBJECT("Timestamp", "2021-07-01 14:30:00" , "Value", 55)) |
+------------------------------------------------------------------------------+
| {
    "Timestamp": "2021-07-01 14:30:00",
    "Value": 55
}                  |
+------------------------------------------------------------------------------+
1 row in set (0.000 sec)

JSON_REMOVE

移除指定 JSON 的資料.

MariaDB [testdb]> SELECT JSON_REMOVE('{"Timestamp": "2021-07-01 14:30:00", "Value": 55} ' ,'$.Value');
+------------------------------------------------------------------------------+
| JSON_REMOVE('{"Timestamp": "2021-07-01 14:30:00", "Value": 55} ' ,'$.Value') |
+------------------------------------------------------------------------------+
| {"Timestamp": "2021-07-01 14:30:00"}                                         |
+------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [testdb]> SELECT JSON_REMOVE('{"Timestamp": "2021-07-01 14:30:00", "Value": 55} ' ,'$.[0]');
+----------------------------------------------------------------------------+
| JSON_REMOVE('{"Timestamp": "2021-07-01 14:30:00", "Value": 55} ' ,'$.[0]') |
+----------------------------------------------------------------------------+
| NULL                                                                       |
+----------------------------------------------------------------------------+
1 row in set, 1 warning (0.001 sec)