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

Source: Internet
Author: User

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.

 
 
  1. /* Json data row/column Conversion
  2. * @ JsonData json Data Source
  3. * @ IdField condition Field
  4. * @ ColField: Field of the column name generated
  5. * @ ValueField: Field of the generated value
  6. * @ EmptyValue default value to avoid incomplete data
  7. */
  8. Function row2col (jsonData, idField, colField, valueField, emptyValue ){
  9. Var result = [], // store the returned data
  10. IdIndexData ={}, // store the id information in the array (location)
  11. ResultColumns ={}, // store column Name Data
  12. CurRecord = null; // store the current data
  13.  
  14. Var colFields = colField. split (',');//
  15.  
  16. // Loop the entire JSON array: [{...},...]
  17. For (var idx = 0; idx <jsonData. length; idx ++ ){
  18.  
  19. // Current json Data Object
  20. Var cdata = jsonData [idx];
  21.  
  22. // Locate the index number in the result Array Based on the primary key value
  23. Var idValue = cdata [idField];
  24. Var num = idIndexData [idValue]; // gets the array index number that stores the id
  25. If (num! = Null ){
  26. CurRecord = result [num];
  27. } Else {
  28. // Keep the complete structure information during data initialization to avoid the lack of data and the lack of specified column data
  29. CurRecord = {};
  30. }
  31.  
  32. // 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.
  33. For (var I in colFields ){
  34. Var key = colFields [I];
  35.  
  36. // Obtain the colField value as the column name
  37. Var value = cdata [valueField];
  38. CurRecord [value] = cdata [key];
  39.  
  40. // Store the column name
  41. ResultColumns [value] = null;
  42. Break;
  43. }
  44.  
  45. // In addition to data content, you also need to add primary key data
  46. CurRecord [idField] = idValue;
  47.  
  48. // If the object is newly created, the array will be upgraded.
  49. If (num = null ){
  50. IdIndexData [idValue] = result. push (curRecord)-1;
  51. }
  52. }
  53.  
  54. // 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.
  55. For (var I in result ){
  56. For (var name in resultColumns ){
  57. If (! Result [I]. hasOwnProperty (name) result [I] [name] = emptyValue;
  58. }
  59. }
  60. Return result;
  61. }

Download complete example

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.