json 列表數(shù)據(jù)受楼，可以通過 sql 進(jìn)行數(shù)據(jù)統(tǒng)計(jì)甥角，比如：
WITH json_data AS ( SELECT '{"total": 741, "rows": [{"folderName": "aocdb", "fileName": "aocdb2_20241222_01.dmp", "fileSize": 55647989792, "fileDate": "2024-12-22", "invalidFile": "否"}, {"folderName": "aocdb", "fileName": "aocdb2_20241222_02.dmp", "fileSize": 53734613024, "fileDate": "2024-12-22", "invalidFile": "否"}], "code": 200, "msg": "查詢成功"}' AS scan_record ) SELECT
min( file_date ) min_file_date,
max( file_date ) max_file_date,
count( DISTINCT FOLDER_NAME ) cnt_folder_name,
count( DISTINCT file_name ) cnt_file_name,
count( CASE WHEN file_date IS NULL THEN 1 ELSE NULL END ) cnt_disabled_file_name,
round( sum( file_size ) / 1024 / 1024 / 1024 / 1024, 2 ) sum_file_size
FROM
json_data,
JSON_TABLE (
scan_record,
"$.rows[*]" COLUMNS (
folder_Name VARCHAR ( 100 ) PATH "$.folderName",
file_Name VARCHAR ( 100 ) PATH "$.fileName",
file_Size BIGINT PATH "$.fileSize",
file_Date DATE PATH "$.fileDate",
invalid_File VARCHAR ( 10 ) PATH "$.invalidFile"
)
) AS jt;
妙巴稀！
不過這種方式不適合數(shù)據(jù)量很大的情況嗤无，比如列表里有30萬條數(shù)據(jù)震束，這個(gè)時(shí)候會(huì)對(duì)服務(wù)器的內(nèi)存和 CPU 造成災(zāi)難。
