後端回傳的 JSON 通常為「長格式」(每筆一列),前端圖表庫(如 ECharts、Highcharts)常需「寬格式」(每欄一個序列)。本文提供通用的 CovertData 函數,將依據 idField 分組,將 fromField 指定的多個數值欄位,依 toField 的值轉為多欄位(如 公司-指標)。
/**
* JSON 行轉列(透視)
* @param {Array} jsonData 原始資料陣列
* @param {string} idField 分組鍵(如 'yearmonth')
* @param {string} fromField 要轉為欄位的數值欄,逗號分隔(如 'ppq,spq')
* @param {string} toField 欄位名稱來源欄位(如 'company')
* @returns {Array} 轉換後陣列,每物件包含 idField 及動態欄位
*/
function CovertData(jsonData, idField, fromField, toField) {
const result = [];
const fromFields = fromField.split(',');
// 輔助:尋找 result 中 idField 等於 value 的索引
const findIdx = (arr, col, val) => arr.findIndex(o => o[col] === val);
for (const row of jsonData) {
const idx = findIdx(result, idField, row[idField]);
const curRecord = idx !== -1 ? result[idx] : {};
for (const key in row) {
// 處理數值欄位
for (const f of fromFields) {
if (key === f) {
curRecord[`${row[toField]}-${f}`] = row[key];
break;
}
}
// 保留分組鍵
if (key === idField) curRecord[key] = row[key];
}
if (idx === -1) result.push(curRecord);
}
return result;
}
TestCase("Test json data row to column", {
setUp: function() {
this.jsonData = [
{yearmonth:201201, ppq:23, spq:27, company:'dfsoft'},
{yearmonth:201202, ppq:33, spq:38, company:'dfsoft'},
{yearmonth:201203, ppq:43, spq:49, company:'dfsoft'},
{yearmonth:201204, ppq:53, spq:51, company:'dfsoft'},
{yearmonth:201201, ppq:29, spq:26, company:'vcom'},
{yearmonth:201202, ppq:34, spq:38, company:'vcom'},
{yearmonth:201203, ppq:48, spq:43, company:'vcom'},
{yearmonth:201204, ppq:52, spq:59, company:'vcom'}
];
this.fromField = 'ppq,spq';
this.toField = 'company';
this.idField = 'yearmonth';
this.resultData = CovertData(this.jsonData, this.idField, this.fromField, this.toField);
},
"test store has columns": function() {
const find = (arr, col, val) => arr.findIndex(o => o[col] === val);
const m1 = this.resultData[find(this.resultData, 'yearmonth', 201201)];
const m2 = this.resultData[find(this.resultData, 'yearmonth', 201202)];
const m3 = this.resultData[find(this.resultData, 'yearmonth', 201203)];
const m4 = this.resultData[find(this.resultData, 'yearmonth', 201204)];
assertEquals(4, this.resultData.length);
assertEquals('23', m1['dfsoft-ppq']); assertEquals('29', m1['vcom-ppq']);
assertEquals('33', m2['dfsoft-ppq']); assertEquals('34', m2['vcom-ppq']);
assertEquals('43', m3['dfsoft-ppq']); assertEquals('48', m3['vcom-ppq']);
assertEquals('53', m4['dfsoft-ppq']); assertEquals('52', m4['vcom-ppq']);
assertEquals('27', m1['dfsoft-spq']); assertEquals('26', m1['vcom-spq']);
assertEquals('38', m2['dfsoft-spq']); assertEquals('38', m2['vcom-spq']);
assertEquals('49', m3['dfsoft-spq']); assertEquals('43', m3['vcom-spq']);
assertEquals('51', m4['dfsoft-spq']); assertEquals('59', m4['vcom-spq']);
}
});
[
{ yearmonth: 201201, 'dfsoft-ppq': 23, 'dfsoft-spq': 27, 'vcom-ppq': 29, 'vcom-spq': 26 },
{ yearmonth: 201202, 'dfsoft-ppq': 33, 'dfsoft-spq': 38, 'vcom-ppq': 34, 'vcom-spq': 38 },
{ yearmonth: 201203, 'dfsoft-ppq': 43, 'dfsoft-spq': 49, 'vcom-ppq': 48, 'vcom-spq': 43 },
{ yearmonth: 201204, 'dfsoft-ppq': 53, 'dfsoft-spq': 51, 'vcom-ppq': 52, 'vcom-spq': 59 }
]