MariaDB 資料庫 – JSON_MERGE , JSON_MERGE_PRESERVE 函數

測試環境為 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.
  1. 合併相同 Key (Duplicate Keys)
    MariaDB [testdb]> SELECT JSON_MERGE('{ "Name":"Alan" }', '{ "Name":"Cherry"}') Result;
    +------------------------------+
    | Result                       |
    +------------------------------+
    | {"Name": ["Alan", "Cherry"]} |
    +------------------------------+
    1 row in set (0.000 sec)
    
  2. 陣列 (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)
  1. 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'
    
  2. 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 資料合在一起而已.

  3. Prepared Statement
    更多關於使用 Prepared Statement 請參考 – http://benjr.tw/102000

    MariaDB [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)
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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