Download a complete example of a JSON data row-to-column conversion application

Download a complete example of a JSON data row-to-column conversion application

Background

First, let's explain why we want to convert columns from rows to columns.

Time	Category	Cost
2014-07-08	Electricity	120
2014-07-08	Water Fee	23
2014-07-09	Electricity	44
2014-07-09	Water Fee	77
2014-07-10	Electricity	45
2014-07-10	Water Fee	21
2014-07-11	Electricity	34
2014-07-11	Water Fee	27

 

 

 

 

 

 

 

It was hard to figure out the form and find that the daily utility fee was just a test data, so don't care about these details.

Most of the time, we use SQL statements to query the above data in the database. when the data is displayed on the page, the following format is required.

Time	Electricity	Water Fee
2014-07-08	120	23
2014-07-08	44	77
2014-07-09	45	66
2014-07-09	43	77
2014-07-10	21	45
2014-07-10	54	21
2014-07-11	65	34
2014-07-11	65	27

 

 

 

 

 

 

 

 

Let's generate the table's html in a loop.

Some friends who are good at asking questions may ask, if this is the case, you can store the electricity and water fees in the table as a column. This topic is not discussed here, because China is paying more and more bills, such as license fees, protection fees, taxes, and natural gas ......

Therefore, no matter how many types of fee categories there are, you can automatically convert them into column names and present them in a table.

Implementation

It is easy to implement. The primary key field is used as the column Name field, Value Field, corresponding to the above instance for "time", "category", "cost ".

The main idea is to traverse JSON and obtain the category value of each row as the column name.

A default value is added to solve the problem of incomplete data.

When I use the above example again, the correct method is to calculate the meter reading fee for the water meter and electric meter every day. So what should I do if I did not write it one day? Isn't the structure incomplete after the conversion, for example, on the 1949th, only the electricity fee is charged, and no data is charged for the water fee. Therefore, after the conversion is successful, we checked whether such a situation exists. If so, we set the default value.

 
 

  
  
/* Json data row/column Conversion
  
  
* @ JsonData json Data Source
  
  
* @ IdField condition Field
  
  
* @ ColField: Field of the column name generated
  
  
* @ ValueField: Field of the generated value
  
  
* @ EmptyValue default value to avoid incomplete data
  
  
*/
  
  
Function row2col (jsonData, idField, colField, valueField, emptyValue ){
  
  
Var result = [], // store the returned data
  
  
IdIndexData ={}, // store the id information in the array (location)
  
  
ResultColumns ={}, // store column Name Data
  
  
CurRecord = null; // store the current data
  
  
 
  
  
Var colFields = colField. split (',');//
  
  
 
  
  
// Loop the entire JSON array: [{...},...]
  
  
For (var idx = 0; idx <jsonData. length; idx ++ ){
  
  
 
  
  
// Current json Data Object
  
  
Var cdata = jsonData [idx];
  
  
 
  
  
// Locate the index number in the result Array Based on the primary key value
  
  
Var idValue = cdata [idField];
  
  
Var num = idIndexData [idValue]; // gets the array index number that stores the id
  
  
If (num! = Null ){
  
  
CurRecord = result [num];
  
  
} Else {
  
  
// Keep the complete structure information during data initialization to avoid the lack of data and the lack of specified column data
  
  
CurRecord = {};
  
  
}
  
  
 
  
  
// If the data in the specified colFields column is used as the Y axis, the data in the column is taken out as the Y axis.
  
  
For (var I in colFields ){
  
  
Var key = colFields [I];
  
  
 
  
  
// Obtain the colField value as the column name
  
  
Var value = cdata [valueField];
  
  
CurRecord [value] = cdata [key];
  
  
 
  
  
// Store the column name
  
  
ResultColumns [value] = null;
  
  
Break;
  
  
}
  
  
 
  
  
// In addition to data content, you also need to add primary key data
  
  
CurRecord [idField] = idValue;
  
  
 
  
  
// If the object is newly created, the array will be upgraded.
  
  
If (num = null ){
  
  
IdIndexData [idValue] = result. push (curRecord)-1;
  
  
}
  
  
}
  
  
 
  
  
// Data check because the row data is used as the column name, some rows may lack other column data. If the column data is missing, the default value is specified.
  
  
For (var I in result ){
  
  
For (var name in resultColumns ){
  
  
If (! Result [I]. hasOwnProperty (name) result [I] [name] = emptyValue;
  
  
}
  
  
}
  
  
Return result;
  
  
}
 
 

Download complete example