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