測試環境為 CentOS 8 x86_64
使用關聯式資料庫（ RDBMS : Relational Database Management System ）的 MariaDB 也可以儲存與查詢 NoSQL ( Not Only SQL : 使用非關聯式資料庫的資料庫,資料儲存不需要固定的欄位 ) 的 JSON ( JavaScript Object Notation ）資料格式.
要怎麼合併 JSON 資料,可以透過 JSON_MERGE 或是 JSON_MERGE_PRESERVE (新版建議) 函數.
JSON_MERGE(json_doc, json_doc[, json_doc] ...)
[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.
- 合併相同 Key (Duplicate Keys)
MariaDB [testdb]> SELECT JSON_MERGE('{ "Name":"Alan" }', '{ "Name":"Cherry"}') Result; +------------------------------+ | Result | +------------------------------+ | {"Name": ["Alan", "Cherry"]} | +------------------------------+ 1 row in set (0.000 sec) - 陣列 (Arrays)
MariaDB [testdb]> SELECT JSON_MERGE('{ "Name":["Alan","Apple","Cherry"] }', '{ "Name":"Cherry"}') Result; +-------------------------------------------------+ | Result | +-------------------------------------------------+ | {"Name": ["Alan", "Apple", "Cherry", "Cherry"]} | +-------------------------------------------------+ 1 row in set (0.000 sec)
從 SELECT 出來的 JSON 資料,可以透過 JSON_MERGE 函數來合併嗎? 下面來試試看.
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)) );
執行結果.
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 格式簡單定義了 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_MERGE
來試試看 JSON_MERGE 資料來自 SELECT ,結果居然不行.MariaDB [testdb]> SELECT JSON_MERGE(Member) FROM project; ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_MERGE'
- CONCAT + GROUP_CONCAT
網路有提供一些其他方式 – https://dba.stackexchange.com/questions/153000/mysql-5-7-json-merge-combine-with-group-concat 但還要依據你的 JSON 格式再去調整 SQL 語法才能達成.SQL 語法
SELECT CONCAT('{', GROUP_CONCAT(SUBSTRING(Member, 2, length(Member) - 2)), '}') Member_Name FROM project WHERE Member != JSON_OBJECT();執行結果
MariaDB [testdb]> SELECT CONCAT('{' , GROUP_CONCAT(SUBSTRING(Member, 2, length(Member) - 2)) , '}') Member_Name FROM project WHERE Member != JSON_OBJECT(); +-------------------------------------------------------------------------------------------------+ | Member_Name | +-------------------------------------------------------------------------------------------------+ | {"Name":["Alan","Joy","Art","Jimmy","Orion","Katrina"],"Name":["Cherry","Mark","Jerry","Rick"]} | +-------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec)可以看到就單純把兩個 JSON 資料合在一起而已.
- Prepared Statement
更多關於使用 Prepared Statement 請參考 – https://benjr.tw/102000MariaDB [testdb]> SET @sql = NULL; Query OK, 0 rows affected (0.001 sec) MariaDB [testdb]> SELECT GROUP_CONCAT(CONCAT('\'',Member,'\'')) INTO @sql FROM project; Query OK, 1 row affected (0.002 sec) MariaDB [testdb]> SET @sql = CONCAT('SELECT JSON_MERGE(', @sql, ')'); Query OK, 0 rows affected (0.000 sec) MariaDB [testdb]> SELECT @sql; +--------------------------------------------------------------------------------------------------------------------------+ | @sql | +--------------------------------------------------------------------------------------------------------------------------+ | SELECT JSON_MERGE('{"Name":["Alan","Joy","Art","Jimmy","Orion","Katrina"]}','{"Name":["Cherry","Mark","Jerry","Rick"]}') | +--------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [testdb]> MariaDB [testdb]> PREPARE stmt FROM @sql; Query OK, 0 rows affected (0.000 sec) Statement prepared的確達到所需的效果.
MariaDB [testdb]> EXECUTE stmt; +-------------------------------------------------------------------------------------------------------------------+ | JSON_MERGE('{"Name":["Alan","Joy","Art","Jimmy","Orion","Katrina"]}','{"Name":["Cherry","Mark","Jerry","Rick"]}') | +-------------------------------------------------------------------------------------------------------------------+ | {"Name": ["Alan", "Joy", "Art", "Jimmy", "Orion", "Katrina", "Cherry", "Mark", "Jerry", "Rick"]} | +-------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [testdb]> DEALLOCATE PREPARE stmt; Query OK, 0 rows affected (0.000 sec)
沒有解決問題,試試搜尋本站其他內容