MariaDB 資料庫 – JSON_SEARCH 函數

測試環境為 CentOS 8 x86_64

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

下面來看一下透過 JSON_SEARCH 函數來找 JSON 資料.

[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 School ( 
ID int(10) unsigned NOT NULL AUTO_INCREMENT, 
Class VARCHAR(200),
Classmate JSON, PRIMARY KEY (ID) , CHECK (JSON_VALID(Classmate))
);

執行結果.

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

依據 JSON 格式簡單定義了 Students 學生的姓名與手機資料.

{
"Students":
[
{"Name":"Ben" ,"Phone":"0933-xxxxxx"},
{"Name":"Alex" ,"Phone":"0922-xxxxxx"},
{"Name":"Adele" ,"Phone":"0911-xxxxxx"}
]
}
MariaDB [testdb]> INSERT INTO School (Class,Classmate) VALUES ('5A','{"Students":[{"Name":"Ben" ,"Phone":"0933-xxxxxx"},{"Name":"Alex" ,"Phone":"0922-xxxxxx"},{"Name":"Adele" ,"Phone":"0911-xxxxxx"}]}') ;
Query OK, 1 row affected (0.002 sec)

檢視資料.

MariaDB [testdb]> SELECT * FROM School;
+----+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID | ClassName | Student                                                                                                                                               |
+----+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | 5A        | {"Students":[{"Name":"Ben" ,"Phone":"0933-xxxxxx"},{"Name":"Alex" ,"Phone":"0922-xxxxxx"},{"Name":"Adele" ,"Phone":"0911-xxxxxx"}]} |
+----+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

要擷取 JSON 的物件可以透過 JSON_QUERY -> 只會回傳 物件 (Object) 或陣列 (Array).

MariaDB [testdb]> SELECT JSON_QUERY(Classmate,'$.Students') FROM School WHERE Class LIKE '5A';
+------------------------------------------------------------------------------------------------------------------------+
| JSON_QUERY(Classmate,'$.Students')                                                                                     |
+------------------------------------------------------------------------------------------------------------------------+
| [{"Name":"Ben" ,"Phone":"0933-xxxxxx"},{"Name":"Alex" ,"Phone":"0922-xxxxxx"},{"Name":"Adele" ,"Phone":"0911-xxxxxx"}] |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

透過 JSON_KEYS 可以查詢資料的鍵 Key .

MariaDB [testdb]> SELECT JSON_KEYS(Classmate,'$.Students[0]') FROM School WHERE Class LIKE '5A';
+--------------------------------------+
| JSON_KEYS(Classmate,'$.Students[0]') |
+--------------------------------------+
| ["Name", "Phone"]                    |
+--------------------------------------+
1 row in set (0.001 sec)

想要看個別詳細資料,需指定陣列的行.

MariaDB [testdb]> SELECT JSON_QUERY(Classmate,'$.Students[0]') FROM School WHERE Class LIKE '5A';
+---------------------------------------+
| JSON_QUERY(Classmate,'$.Students[0]') |
+---------------------------------------+
| {"Name":"Ben" ,"Phone":"0933-xxxxxx"} |
+---------------------------------------+
1 row in set (0.001 sec)

但不清楚資料在陣列哪一行時,可以透過 JSON_SEARCH .

JSON_SEARCH(json_doc, return_arg, search_str[, escape_char[, path] ...])

return_arg 需指定為

  • one :
    只會回傳第一筆匹配值的路徑.
  • all :
    多筆資料皆匹配時,回傳成為一個陣列且不重覆的路徑.
MariaDB [testdb]> SELECT JSON_SEARCH(Classmate,'one','Ben') FROM School WHERE Class LIKE '5A';
+------------------------------------+
| JSON_SEARCH(Classmate,'one','Ben') |
+------------------------------------+
| "$.Students[0].Name"               |
+------------------------------------+
1 row in set (0.000 sec)

知道資料陣列行數後,就可以直接透過 JSON_VALUE -> 只會回傳 純量值 (scalar).

MariaDB [testdb]> SELECT JSON_VALUE(Classmate,'$.Students[0].Name') FROM School WHERE Class LIKE '5A';
+--------------------------------------------+
| JSON_VALUE(Classmate,'$.Students[0].Name') |
+--------------------------------------------+
| Ben                                        |
+--------------------------------------------+
1 row in set (0.001 sec)

MariaDB [testdb]> SELECT JSON_VALUE(Classmate,'$.Students[0].Phone') FROM School WHERE Class LIKE '5A';
+---------------------------------------------+
| JSON_VALUE(Classmate,'$.Students[0].Phone') |
+---------------------------------------------+
| 0933-xxxxxx                                 |
+---------------------------------------------+
1 row in set (0.001 sec)

上面指令有變法變成一個 SQL 嗎?

TBD

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

發佈留言

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

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