MariaDB 資料庫 – JSON_CONTAINS 函數

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

SQL 語法:

CREATE TABLE project ( 
ID int(10) unsigned NOT NULL AUTO_INCREMENT, 
Name VARCHAR(200),
Member JSON, PRIMARY KEY (ID) , CHECK (JSON_VALID(Member))
);

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

MariaDB [testdb]> SELECT JSON_VALID('{"Name":["Cherry","Mark","Jerry","Rick"]}') AS 'JSON Status';
+-------------+
| JSON Status |
+-------------+
|           1 |
+-------------+
1 row in set (0.001 sec)

MariaDB [testdb]> SELECT JSON_VALID('{"Name":["Cherry","Mark","Jerry","Rick]}') AS 'JSON Status';
+-------------+
| JSON Status |
+-------------+
|           0 |
+-------------+
1 row in set (0.001 sec)

執行結果.

MariaDB [testdb]> CREATE TABLE project ( 
    -> ID int(10) unsigned NOT NULL AUTO_INCREMENT, 
    -> Name VARCHAR(200),
    -> Member JSON, PRIMARY KEY (ID) , CHECK (JSON_VALID(Member))
    -> );
Query OK, 0 rows affected (0.008 sec)

建立兩筆資料 Project A 與 B .

JSON 資料類型：

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

依據 JSON 格式簡單定義了 Project 的成員資料(成員為非固定數量,很適合使用 JSON 格式來儲存).

{ "Name":["Alan","Joy","Art","Jimmy","Orion","Katrina"] }
{ "Name":["Cherry","Mark","Jerry","Rick"] }

MariaDB [testdb]> INSERT INTO project (Name,Member) VALUES ('Project A','{"Name":["Alan","Joy","Art","Jimmy","Orion","Katrina"]}') ;
Query OK, 1 row affected (0.002 sec)

MariaDB [testdb]> INSERT INTO project (Name,Member) VALUES ('Project B','{"Name":["Cherry","Mark","Jerry","Rick"]}') ;
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> SELECT * FROM project;
+----+-----------+---------------------------------------------------------+
| ID | Name      | Member                                                  |
+----+-----------+---------------------------------------------------------+
|  1 | Project A | {"Name":["Alan","Joy","Art","Jimmy","Orion","Katrina"]} |
|  2 | Project B | {"Name":["Cherry","Mark","Jerry","Rick"]}               |
+----+-----------+---------------------------------------------------------+
2 rows in set (0.001 sec)

要找哪一個成員 (儲存在 JSON array )是屬於哪一個專案時可以透過JSON_CONTAINS 函數.
SQL 語法:

JSON_CONTAINS(json_doc, val[, path])

MariaDB [testdb]> SELECT JSON_CONTAINS('{"Name":["Joy","Jimmy"]}', '"Jimmy"', '$.Name');
+----------------------------------------------------------------+
| JSON_CONTAINS('{"Name":["Joy","Jimmy"]}', '"Jimmy"', '$.Name') |
+----------------------------------------------------------------+
|                                                              1 |
+----------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [testdb]> SELECT JSON_CONTAINS('{"Name":["Joy","Jimmy"]}', '"Ben"', '$.Name');
+--------------------------------------------------------------+
| JSON_CONTAINS('{"Name":["Joy","Jimmy"]}', '"Ben"', '$.Name') |
+--------------------------------------------------------------+
|                                                            0 |
+--------------------------------------------------------------+
1 row in set (0.000 sec)

搜尋哪個專案有 Jimmy 這個成員.

MariaDB [testdb]> SELECT Name , JSON_QUERY(Member,'$.Name') FROM project WHERE JSON_CONTAINS(Member, '"Jimmy"', '$.Name');
+-----------+------------------------------------------------+
| Name      | JSON_QUERY(Member,'$.Name')                    |
+-----------+------------------------------------------------+
| Project A | ["Alan","Joy","Art","Jimmy","Orion","Katrina"] |
+-----------+------------------------------------------------+
1 row in set (0.001 sec)

搜尋哪個專案有 Alan 這個成員.

MariaDB [testdb]> SELECT Name , JSON_QUERY(Member,'$.Name') FROM project WHERE JSON_CONTAINS(Member, '"Alan"', '$.Name');
+-----------+------------------------------------------------+
| Name      | JSON_QUERY(Member,'$.Name')                    |
+-----------+------------------------------------------------+
| Project A | ["Alan","Joy","Art","Jimmy","Orion","Katrina"] |
+-----------+------------------------------------------------+
1 row in set (0.001 sec)

可以同時包含多個資料,搜尋哪個專案有 Cherry 與 Jerry 這兩個成員.

MariaDB [testdb]> SELECT Name , JSON_QUERY(Member,'$.Name') FROM project WHERE JSON_CONTAINS(Member, '["Cherry","Jerry"]', '$.Name');
+-----------+----------------------------------+
| Name      | JSON_QUERY(Member,'$.Name')      |
+-----------+----------------------------------+
| Project B | ["Cherry","Mark","Jerry","Rick"] |
+-----------+----------------------------------+
1 row in set (0.001 sec)

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