169 lines
6.5 KiB
Plaintext
169 lines
6.5 KiB
Plaintext
DELIMITER //
|
|
|
|
CREATE OR REPLACE PROCEDURE Parse_JSON(
|
|
IN p_uuid VARCHAR(100),
|
|
IN json_input JSON
|
|
)
|
|
/*
|
|
call Parse_JSON('uuid-20250803',
|
|
'{"custcateid":"08","custcatecname":"8888","custcateename":"","memochar":"","mergetype":"","token":"c975931c-6f8d-11f0-9c5f-d8bbc15c1404","_$_tableName":"bascustcategory",
|
|
"_$_pk":"custcateid",
|
|
"_$_action":"D"}');
|
|
|
|
call Parse_JSON('uuid-20250803',
|
|
'{"sprintid":"Next-Sprint","sprint_title":"調整現行 PBI 緩衝用的 Sprint(2025)","sprint_startdate":"2025-01-01T00:00:00.000+08:00","sprint_enddate":"2099-12-30T16:00:00.000Z","time_range":"2099",
|
|
"token":"768f1c89-6fb9-11f0-9c5f-d8bbc15c1404",
|
|
"_$_tableName":"pms_sprint",
|
|
"_$_pk":"sprintid",
|
|
"_$_action":"U"}');
|
|
|
|
call Parse_JSON('uuid-20250803_3',
|
|
'{"rows":[{"custcateid":"09","custcatecname":"9999","custcateename":"9999-memo","memochar":"","mergetype":""}],
|
|
"rowsDiff":[{"custcateename":"9999-memo"}],"indexes":["7"],
|
|
"rowsOrigin":
|
|
[{"custcateid":"09","custcatecname":"9999","custcateename":"","memochar":"","mergetype":""}],
|
|
"unModifiedItems":[
|
|
{"custcateid":"01","custcatecname":"經銷商","custcateename":"333","memochar":"","mergetype":"0"},
|
|
{"custcateid":"02","custcatecname":"代理商","custcateename":"444","memochar":"","mergetype":"0"},
|
|
{"custcateid":"03","custcatecname":"大型企業","custcateename":"","memochar":"","mergetype":"0"},
|
|
{"custcateid":"04","custcatecname":"政府","custcateename":"","memochar":"","mergetype":"0"},
|
|
{"custcateid":"05","custcatecname":"中小企業","custcateename":"","memochar":"","mergetype":"0"},
|
|
{"custcateid":"06","custcatecname":"學校","custcateename":"","memochar":"","mergetype":"0"},
|
|
{"custcateid":"07","custcatecname":"竹科","custcateename":"","memochar":"","mergetype":"0"}],
|
|
"token":"d101363e-700c-11f0-9c5f-d8bbc15c1404","_$_tableName":"bascustcategory","_$_pk":"custcateid","_$_action":"U"}');
|
|
|
|
call Parse_JSON('uuid-20250820_1',
|
|
'{"projectclassid":"soft-dev","proj_boss":"120102",
|
|
"config":[{"label":"(GM) 總經理室",
|
|
"children":[
|
|
{"label":"(120101) 高光","value":"120101"},
|
|
{"label":"(121108) 沈秋月","value":"121108"},
|
|
{"label":"(200605) 陳明文","value":"200605"},
|
|
{"label":"(120102) 王俊傑","value":"120102"},
|
|
{"label":"(150201) 衛斯理","value":"150201"},
|
|
{"label":"(190802) 葉子龍","value":"190802"},
|
|
{"label":"(210301) 高國華","value":"210301"},
|
|
{"label":"(admin)admin","value":"admin"},
|
|
{"label":"(160805) 邱雅惠","value":"160805"},
|
|
{"label":"(170911) 劉強南","value":"170911"},
|
|
{"label":"(201001) 林志堅","value":"201001"},
|
|
{"label":"(120912) 陳進德","value":"120912"},
|
|
{"label":"(170109) 林玉玲","value":"170109"},
|
|
{"label":"(180210) 秦一明","value":"180210"},
|
|
{"label":"(191006) 歐陽龍","value":"191006"},
|
|
{"label":"(201036) 邱郁芳","value":"201036"},
|
|
{"label":"(demo) 測試用戶","value":"demo"}]
|
|
}],
|
|
"projectid":"IT-2025-Chaos","proj_cname":"MariaDB改寫專案","proj_status":"SASD","proj_startdate":"2025-08-01","proj_enddate":"2025-09-30",
|
|
"proj_year":"2025","token":"a85afca2-7dc5-11f0-9c5f-d8bbc15c1404",
|
|
"_$_tableName":"pmsproject",
|
|
"_$_pk":"projectid",
|
|
"_$_action":"C"}');
|
|
|
|
|
|
*/
|
|
BEGIN
|
|
DECLARE i INT DEFAULT 0;
|
|
DECLARE total_keys INT;
|
|
DECLARE key_name VARCHAR(255);
|
|
DECLARE val JSON;
|
|
|
|
DECLARE sub_key_name VARCHAR(255);
|
|
DECLARE sub_val JSON;
|
|
DECLARE sub_total_keys INT;
|
|
DECLARE j INT;
|
|
DECLARE x INT;
|
|
DECLARE y INT;
|
|
DECLARE arr_count,arr_idx INT;
|
|
DECLARE arr_obj JSON;
|
|
|
|
-- 取第一層 keys 數量
|
|
SET total_keys = JSON_LENGTH(JSON_KEYS(json_input));
|
|
SET x = 1;
|
|
SET y = 1;
|
|
|
|
WHILE i < total_keys DO
|
|
SET key_name = JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(json_input), CONCAT('$[', i, ']')));
|
|
SET val = JSON_EXTRACT(json_input, CONCAT('$.', key_name));
|
|
|
|
-- 插入第一層
|
|
-- INSERT INTO tmp_json_parse_result (level, parent_key, key_name, value)
|
|
INSERT INTO sys_jsondata (UUID, `NAME`, SequenceNo, parent_ID, StringValue, ValueType)
|
|
VALUES (p_uuid, key_name, x, NULL, JSON_UNQUOTE(val), JSON_TYPE(val));
|
|
|
|
SET y = x;
|
|
SET x = x + 1;
|
|
|
|
-- 第二層處理：若 value 是 object 或 array，解析其 key-value (當前版本只處理 2層, 多的暫時不管)
|
|
IF JSON_TYPE(val) IN ('OBJECT', 'ARRAY') THEN
|
|
-- 先處理 ARRAY 可能有多筆資料
|
|
IF JSON_TYPE(val) = 'ARRAY' THEN
|
|
SET arr_count = JSON_LENGTH(val);
|
|
SET arr_idx = 0;
|
|
|
|
WHILE arr_idx < arr_count DO
|
|
-- 取出陣列中的單一物件
|
|
SET arr_obj = JSON_EXTRACT(val, CONCAT('$[', arr_idx, ']'));
|
|
SET j = 0;
|
|
SET sub_total_keys = JSON_LENGTH(JSON_KEYS(arr_obj));
|
|
|
|
WHILE j < sub_total_keys DO
|
|
SET sub_key_name = JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(arr_obj), CONCAT('$[', j, ']')));
|
|
SET sub_val = JSON_EXTRACT(arr_obj, CONCAT('$.', sub_key_name));
|
|
|
|
INSERT INTO sys_jsondata (UUID, `NAME`, SequenceNo, parent_ID, Object_ID, StringValue, ValueType)
|
|
VALUES (p_uuid, sub_key_name, x, y, arr_idx+1, JSON_UNQUOTE(sub_val), JSON_TYPE(sub_val));
|
|
|
|
SET j = j + 1;
|
|
SET x = x + 1;
|
|
END WHILE;
|
|
|
|
SET arr_idx = arr_idx + 1;
|
|
END WHILE;
|
|
ELSE
|
|
-- 處理單一 OBJECT
|
|
SET j = 0;
|
|
SET sub_total_keys = JSON_LENGTH(JSON_KEYS(val));
|
|
|
|
WHILE j < sub_total_keys DO
|
|
SET sub_key_name = JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(val), CONCAT('$[', j, ']')));
|
|
SET sub_val = JSON_EXTRACT(val, CONCAT('$.', sub_key_name));
|
|
|
|
INSERT INTO sys_jsondata (UUID, `NAME`, SequenceNo, parent_ID, StringValue, ValueType)
|
|
VALUES (p_uuid, sub_key_name, x, y, JSON_UNQUOTE(sub_val), JSON_TYPE(sub_val));
|
|
|
|
SET j = j + 1;
|
|
SET x = x + 1;
|
|
END WHILE;
|
|
END IF;
|
|
END IF;
|
|
|
|
/*
|
|
-- 第二層處理：若 value 是 object，解析其 key-value
|
|
IF JSON_TYPE(val) in ('OBJECT','ARRAY') THEN
|
|
-- 先解決 rows Grid Edit 多筆資料, 後續應該還有其他狀況要一一排除
|
|
SET j = 0;
|
|
-- IF SELECT TRIM(BOTH '[]' FROM '[example]') AS result;
|
|
SET sub_total_keys = JSON_LENGTH(JSON_KEYS(val));
|
|
|
|
WHILE j < sub_total_keys DO
|
|
SET sub_key_name = JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(val), CONCAT('$[', j, ']')));
|
|
SET sub_val = JSON_EXTRACT(val, CONCAT('$.', sub_key_name));
|
|
|
|
-- INSERT INTO tmp_json_parse_result (level, parent_key, key_name, value)
|
|
INSERT INTO sys_jsondata (UUID, `NAME`, SequenceNo, parent_ID, StringValue, ValueType)
|
|
VALUES (p_uuid, sub_key_name, x, y , JSON_UNQUOTE(sub_val), JSON_TYPE(val));
|
|
|
|
SET j = j + 1;
|
|
SET x = x + 1;
|
|
END WHILE;
|
|
END IF;
|
|
*/
|
|
|
|
SET i = i + 1;
|
|
END WHILE;
|
|
|
|
END //
|
|
|
|
DELIMITER ;
|