測試環境為 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_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)
除來可以直接搜尋外,還可以指定路徑.
MariaDB [astl1]> SELECT JSON_SEARCH(Classmate,'one','Ben', NULL , '$[0].Students[*].Name') FROM School WHERE Class LIKE '5A '; +--------------------------------------------------------------------+ | JSON_SEARCH(Classmate,'one','Ben', NULL , '$[0].Students[*].Name') | +--------------------------------------------------------------------+ | "$.Students[0].Name" | +--------------------------------------------------------------------+ 1 row in set (0.000 sec)
大小寫
JSON_SEARCH 搜尋關鍵字名稱,有區分大小寫.可以利用 UPPER(轉換成大寫) 或是 LOWER(轉換成小寫) 函數來改變大小寫來搜尋.
MariaDB [astl1]> SELECT JSON_SEARCH(Classmate,'one','ben') FROM School WHERE Class LIKE '5A';
+------------------------------------+
| JSON_SEARCH(Classmate,'one','ben') |
+------------------------------------+
| NULL |
+------------------------------------+
1 row in set (0.000 sec)
MariaDB [astl1]> SELECT JSON_SEARCH(UPPER(Classmate) ,'one', UPPER('ben')) FROM School WHERE Class LIKE '5A';
+----------------------------------------------------+
| JSON_SEARCH(UPPER(Classmate) ,'one', UPPER('ben')) |
+----------------------------------------------------+
| "$.STUDENTS[0].NAME" |
+----------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [astl1]> SELECT JSON_SEARCH(LOWER(Classmate) ,'one', LOWER('ben')) FROM School WHERE Class LIKE '5A';
+----------------------------------------------------+
| JSON_SEARCH(LOWER(Classmate) ,'one', LOWER('ben')) |
+----------------------------------------------------+
| "$.students[0].name" |
+----------------------------------------------------+
1 row in set (0.000 sec)
或是 透過 CONVERT( ‘String’ USING utf8) 將預設 JSON 的欄位(utf8mb4_bin 分大小寫)轉成 utf8(不分大小寫).
MariaDB [astl1]> SELECT JSON_SEARCH( CONVERT(Classmate USING utf8),'one','ben') FROM School WHERE Class LIKE '5A'; +---------------------------------------------------------+ | JSON_SEARCH( CONVERT(Classmate USING utf8),'one','ben') | +---------------------------------------------------------+ | "$.Students[0].Name" | +---------------------------------------------------------+ 1 row in set (0.001 sec)
JSON_SEARCH+JSON_VALUE
知道資料陣列行數後,就可以直接透過 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 嗎?
可以透過以下的方式來做.
MariaDB [testdb]> SELECT JSON_VALUE(Classmate, (SELECT REPLACE(JSON_SEARCH(Classmate,'one','Ben'), '\"' ,'') FROM School WHERE Class LIKE '5A') ) FROM School WHERE Class LIKE '5A'; +--------------------------------------------------------------------------------------------------------------------------+ | JSON_VALUE(Classmate, (SELECT REPLACE(JSON_SEARCH(Classmate,'one','Ben'), '\"' ,'') FROM School WHERE Class LIKE '5A') ) | +--------------------------------------------------------------------------------------------------------------------------+ | Ben | +--------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec) MariaDB [testdb]> SELECT JSON_VALUE(Classmate, (SELECT REPLACE(REPLACE(JSON_SEARCH(Classmate,'one','Ben'), '\"' ,'') , 'Name' ,'Phone') FROM School WHERE Class LIKE '5A') ) FROM School WHERE Class LIKE '5A'; +-----------------------------------------------------------------------------------------------------------------------------------------------------+ | JSON_VALUE(Classmate, (SELECT REPLACE(REPLACE(JSON_SEARCH(Classmate,'one','Ben'), '\"' ,'') , 'Name' ,'Phone') FROM School WHERE Class LIKE '5A') ) | +-----------------------------------------------------------------------------------------------------------------------------------------------------+ | 0933-xxxxxx | +-----------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec)
或是使用 Prepared Statements ,詳細請參考 – https://benjr.tw/102000
沒有解決問題,試試搜尋本站其他內容