MariaDB – Remove JSON None (or null) attributes

測試環境為 CentOS 8 x86_64 (虛擬機) , MariaDB 10.3.28

要怎麼移除 JSON 格式,值 (Attributes) 為 None (文字) 或是 NULL (空值) 的.

JSON 範例.

SET @J1='{
    "Number": " 1133033",
    "Level": "High",
    "Status": "None",
    "Date": null,
    "Department": "None"}';

JSON_SEARCH + JSON_REMOVE

先透過 JSON_SEARCH 找出值為 None 的.

MariaDB [(none)]> SELECT JSON_SEARCH(@J1 , 'all' , 'None');
+-----------------------------------+
| JSON_SEARCH(@J1 , 'all' , 'None') |
+-----------------------------------+
| ["$.Status", "$.Department"]      |
+-----------------------------------+
1 row in set (0.000 sec)

或是值為 NULL 的.

MariaDB [(none)]> SELECT JSON_SEARCH(@J1 , 'all' , 'null');
+-----------------------------------+
| JSON_SEARCH(@J1 , 'all' , 'null') |
+-----------------------------------+
| "$.Date"                          |
+-----------------------------------+
1 row in set (0.000 sec)

再把剛剛透過 JSON_SEARCH 得到的 KEY值 帶入到 JSON_REMOVE ,就可以移除 值 (Attributes) 為 None (文字) 或是 NULL (空值) 的.

MariaDB [(none)]> SELECT JSON_REMOVE(@J1 , "$.Status", "$.Department");
+-------------------------------------------------------+
| JSON_REMOVE(@J1 , "$.Status", "$.Department")         |
+-------------------------------------------------------+
| {"Number": " 1133033", "Level": "High", "Date": null} |
+-------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT JSON_DETAILED(JSON_REMOVE(@J1 , "$.Status", "$.Department"));
+---------------------------------------------------------------------+
| JSON_DETAILED(JSON_REMOVE(@J1 , "$.Status", "$.Department"))        |
+---------------------------------------------------------------------+
| {
    "Number": " 1133033",
    "Level": "High",
    "Date": null
} |
+---------------------------------------------------------------------+
1 row in set (0.000 sec)

Procedure

如果是要寫到 Procedure ,可以透過 Prepared Statements 來完成.

首先把透過 JSON_SEARCH 得到 值 為 None 的 Key 儲存取來.

MariaDB [(none)]> SELECT REPLACE(REPLACE(REPLACE(REPLACE(JSON_SEARCH(@J1  , 'all' , 'None') , '[' ,'') , ']' ,'')  , '.' ,'') ,'$', '$.') INTO @J1_None;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> SELECT @J1_None;
+----------------------------+
| @J1_None                   |
+----------------------------+
| "$.Status", "$.Department" |
+----------------------------+
1 row in set (0.000 sec)

上面用了 REPLACE 主要是要解決以下問題.

• 這邊會把 JSON_SEARCH 的陣列 ‘[‘ 與 ‘]’ 移除.
• 還會移除在搜尋字串中包含 ‘.’ ( 因為 ‘$.’ 會被影響所以需再補上 ‘.’ 成為 ‘$.’ ), 這是目前 JSON_REMOVE 的 Bug.

  MariaDB [(none)]> SELECT JSON_REMOVE('{"A": { "B": 1 }}', '$.A.C.D');
  +---------------------------------------------+
  | JSON_REMOVE('{"A": { "B": 1 }}', '$.A.C.D') |
  +---------------------------------------------+
  | NULL                                        |
  +---------------------------------------------+
  1 row in set, 1 warning (0.000 sec)
  

Prepared Statements

MariaDB [(none)]> SET @sqlcmd1= CONCAT( 'SELECT JSON_REMOVE(@J1 , ' , @J1_None , ') ') ;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT @sqlcmd1;
+-------------------------------------------------------+
| @sqlcmd1                                              |
+-------------------------------------------------------+
| SELECT JSON_REMOVE(@J1 , "$.Status", "$.Department")  |
+-------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> PREPARE stmt FROM @sqlcmd1;
Query OK, 0 rows affected (0.000 sec)
Statement prepared

MariaDB [(none)]> EXECUTE stmt;
+-------------------------------------------------------+
| JSON_REMOVE(@J1 , "$.Status", "$.Department")         |
+-------------------------------------------------------+
| {"Number": " 1133033", "Level": "High", "Date": null} |
+-------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.000 sec)