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_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

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

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。

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