admin

转换JSON数组的数据结构以堆积条形图

sql

背景:对于堆积的条形图,我试图代表一系列医院,这些医院显示每位患者的诊断所属的类别总数(每家医院)。我通过使用SQL查询生成了JSON文件并将其导出。

问题:如何更改JSON数组数据结构以合并同一家医院的多个类别?

我的堆叠条形图的JSON数组数据结构(一小部分)如下所示:

[{
  "hospitalName": "hospital1",
  "category": "Injury & Poisoning",        
  "Females": "0",
  "Males": "4",
  "Unknown": "0",
  "count": "4"
},
{
  "hospitalName": "hospital1",
  "category": "Symptoms, Signs, & Ill-Defined Conditions",
  "Females": "1",
  "Males": "1",
  "Unknown": "0",
  "count": "2"
},
{
   "hospitalName": "hospital2",
   "category": "Mental Disorders",
    "Females": "0",
    "Males": "1",
    "Unknown": "0",
    "count": "1"
}]

所需的JSON数组数据结构:

[{
  "hospitalName": "hospital1",
  "Injury & Poisoning": "4",
  "Symptoms, Signs, & Ill-Defined Conditions": "2"        
  "Females": "1",  <--- the count of females is increased
  "Males": "5",    <--- the count of males is increased 
  "Unknown": "0",
  "count": "6"
},
{
   "hospitalName": "hospital2",
   "category": "Mental Disorders",
    "Females": "0",
    "Males": "1",
    "Unknown": "0",
    "count": "1"
}]

是否可以使用SQL查询来产生?还是可以改用PLSQL?


阅读 136

收藏
2021-07-01

共1个答案

admin

只需创建一个新的字典,其关键字等于医院名称即可。然后从字典中检索所有元素。

如果您需要将计数作为字符串转换,就在将它们添加回数据列表之前对其进行转换。

var newdata = {};

data.forEach(element => {
    var name = element.hospitalName;
    var hospital = newdata[name];
    if (!hospital) {
        hospital = { hospitalName: name, Females: 0, Males: 0, Unknown: 0, count: 0};
        newdata[name] = hospital;
    }
    hospital[element.category] = +element.count;
    hospital.Females += +element.Females;
    hospital.Males   += +element.Males;
    hospital.Unknown += +element.Unknown;
    hospital.count   += +element.count;
});

data = [];

for (const key in newdata) {
    if (newdata.hasOwnProperty(key)) {
        data.push(newdata[key]);
    }
}

作为一个 庞大的 数据集的小型运行示例。

var data = [{

  "hospitalName": "hospital1",

  "category": "Injury & Poisoning",

  "Females": "0",

  "Males": "4",

  "Unknown": "0",

  "count": "4"

},

{

  "hospitalName": "hospital1",

  "category": "Symptoms, Signs, & Ill-Defined Conditions",

  "Females": "1",

  "Males": "1",

  "Unknown": "0",

  "count": "2"

},

{

   "hospitalName": "hospital2",

   "category": "Mental Disorders",

    "Females": "0",

    "Males": "1",

    "Unknown": "0",

    "count": "1"

}];



var newdata = {};



data.forEach(element => {

    var name = element.hospitalName;

    var hospital = newdata[name];

    if (!hospital) {

        hospital = { hospitalName: name, Females: 0, Males: 0, Unknown: 0, count: 0};

        newdata[name] = hospital;

    }

    hospital[element.category] = +element.count;

    hospital.Females += +element.Females;

    hospital.Males   += +element.Males;

    hospital.Unknown += +element.Unknown;

    hospital.count   += +element.count;

});



data = [];



for (const key in newdata) {

    if (newdata.hasOwnProperty(key)) {

        data.push(newdata[key]);

    }

}



console.log(data);
2021-07-01