SQL 語法 – JSON 轉成 Table (Rows & Columns)

測試環境為 CentOS 8 x86_64 , Mariadb 10.3.17-1 (虛擬機)

JSON 的資料要怎麼暫時轉成 Table (Rows & Columns) , Oracle (Mysql) 有提供 JSON_TABLE 函數,但 MariaDB 沒有怎麼辦.可以透過 Recursive Common Table Expressions (MariaDB 版本需要 10.2.2 以上才有支援,詳細使用說明請參考 – https://benjr.tw/103485 ).

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2774
Server version: 10.3.27-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

參考範例 – https://stackoverflow.com/questions/59598980/how-to-convert-an-array-of-object-to-a-table

這樣的 JSON 資料有辦法轉成 Table (Rows & Columns) 嗎？

{
  "product_id":10006683,
  "detail_id":72,
  "serial":125655
},
{
  "product_id":10006684,
  "detail_id":73,
  "serial":129655
}

如果後續要把 SELECT (整理後的 JSON 資料) 出來的資料存儲起來,須先建立相對應資料表(暫存表).

DROP TEMPORARY TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table
(
  id integer primary key auto_increment,
  product_id integer,
  detail_id integer, 
  serial integer
);

這邊把剛剛的 JSON 資料儲存到變數 @json.

MariaDB [testdb]> set @json = '[{"product_id":10006683,"detail_id":72,"serial":125655},{"product_id":10006684,"detail_id":73,"serial":129655}]';
Query OK, 0 rows affected (0.001 sec)

需使用 Recursive Common Table Expressions 語法來完成.

insert into tmp_table (product_id, detail_id, serial)
with recursive rcte_json as
(
  select 1 n
  , JSON_EXTRACT(@json, '$[0].product_id') as product_id
  , JSON_EXTRACT(@json, '$[0].detail_id') as detail_id
  , JSON_EXTRACT(@json, '$[0].serial') as serial

  union all

  select n+1
  , JSON_EXTRACT(@json, concat('$[',n,'].product_id')) as product_id
  , JSON_EXTRACT(@json, concat('$[',n,'].detail_id')) as detail_id
  , JSON_EXTRACT(@json, concat('$[',n,'].serial')) as serial
  from rcte_json
  where n < JSON_LENGTH(@json)
)
select product_id, detail_id, serial
from rcte_json
order by n;
select * from tmp_table;

程式說明:

如果 SELECT 出來的資料沒有存儲的必要,第一句語法可以省略.

insert into tmp_table (product_id, detail_id, serial)

Recursive Common Table Expressions 第一段程式去讀取第一筆 JSON 資料,並把 n 的值設定為 1 (第二段的時候會用到).

  select 1 n
  , JSON_EXTRACT(@json, '$[0].product_id') as product_id
  , JSON_EXTRACT(@json, '$[0].detail_id') as detail_id
  , JSON_EXTRACT(@json, '$[0].serial') as serial
+---+------------+-----------+--------+
| n | product_id | detail_id | serial |
+---+------------+-----------+--------+
| 1 | 10006683   | 72        | 125655 |
+---+------------+-----------+--------+

{
  "product_id":10006683,
  "detail_id":72,
  "serial":125655
}

第二段程式去讀取第二筆 JSON 資料.

 select n+1
  , JSON_EXTRACT(@json, concat('$[',n,'].product_id')) as product_id
  , JSON_EXTRACT(@json, concat('$[',n,'].detail_id')) as detail_id
  , JSON_EXTRACT(@json, concat('$[',n,'].serial')) as serial
  from rcte_json
  where n < JSON_LENGTH(@json)

這邊很厲害,利用 id (Integer , Primary Key) 欄位 auto_increment 的特性(每新增一筆資料,就自動 +1), 讓接下來的 $[‘,n,’] 都會增加(JSON 資料是從 0 開始 1,2,3…).

第一段已經把 n 定義為 1 , Recursive 的特性會使用前一次 SELECT 的值當做搜尋條件,上一次的 n 為 1 , 所以 讀到的是 concat(‘$[‘,1,’].product_id’) (JSON 資料是從 0 開始 1,2,3…),後續固定 n+1 ,讓 n 變成 n+1 , n+2 n+3 …

{
  "product_id":10006684,
  "detail_id":73,
  "serial":129655
}

直到 JSON 的最後一筆,透過 JSON_LENGTH 函數算出 JSON 的筆數 (JSON 資料是從 0 開始 1,2,3…)

MariaDB [testdb]> SELECT JSON_LENGTH(@json);
+--------------------+
| JSON_LENGTH(@json) |
+--------------------+
|                  2 |
+--------------------+
1 row in set (0.000 sec)

執行結果:

MariaDB [testdb]> insert into tmp_table (product_id, detail_id, serial)
    -> with recursive rcte_json as
    -> (
    ->   select 1 n
    ->   , JSON_EXTRACT(@json, '$[0].product_id') as product_id
    ->   , JSON_EXTRACT(@json, '$[0].detail_id') as detail_id
    ->   , JSON_EXTRACT(@json, '$[0].serial') as serial
    -> 
    ->   union all
    -> 
    ->   select n+1
    ->   , JSON_EXTRACT(@json, concat('$[',n,'].product_id')) as product_id
    ->   , JSON_EXTRACT(@json, concat('$[',n,'].detail_id')) as detail_id
    ->   , JSON_EXTRACT(@json, concat('$[',n,'].serial')) as serial
    ->   from rcte_json
    ->   where n < JSON_LENGTH(@json)
    -> )
    -> select product_id, detail_id, serial
    -> from rcte_json
    -> order by n;
Query OK, 2 rows affected (0.005 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [testdb]> select * from tmp_table;
+----+------------+-----------+--------+
| id | product_id | detail_id | serial |
+----+------------+-----------+--------+
|  1 |   10006683 |        72 | 125655 |
|  2 |   10006684 |        73 | 129655 |
+----+------------+-----------+--------+
2 rows in set (0.001 sec)