Application of JSON data row to column

Background

First of all, why do you want to get a row?

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

　　

Hard to get out of the form, only to find that the daily water and electricity, but test data, do not care about these details.

Many times we use SQL statements in the database to query the above data, so when the page is displayed, it is bound to become the following format

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

　　

　　

Let's loop to generate the HTML for the table.

Some friends who are good at asking questions may ask, since this is the case, you can store the electricity and water bills as columns in the table. This topic is not much discussed here, because China has more and more fees, property fees, protection fees, taxes, natural gas and so on various names ...

Therefore, it is hoped that regardless of the cost category, it can be automatically converted to column name information, presented in tabular form in front of the user.

Realize

The implementation is simple, specify the primary key field, the field to use as the column name, the Value field, corresponding to the above instance, "Time", "category", "cost".

The main idea is to iterate through the JSON, taking the value of the category of each row as the column name store.

This adds a default value that is intended to solve the problem of incomplete data.

Again using the above example, the correct practice is that the water meter and the meter to calculate the cost of every day, then in case of the day did not write how to do, then the transformation of the structure is not complete, such as the 2014-07-09 number only electricity, even water water this line of data are not, so after successful conversion, Deliberately detects if such a condition exists and sets the default value if it exists.

    /*JSON data Column conversion * @jsonData JSON data source * @idField criteria field * @colField Generate column Name fields * @valueField fields that generate values * @empty Value defaults to avoid some data is not complete*/function Row2col (Jsondata, IDfield, Colfield, Valuefield, emptyvalue) {varresult = [],//Store the returned dataIdindexdata = {},//Store ID information in the array (location)Resultcolumns = {},//Store column name DataCurrecord =NULL;//Store Current Data        varColfields = Colfield.split (',');//        //Loop through the entire JSON array: [{...},{...},{},...]          for(varIDX =0; IDX < jsondata.length; idx++) {            //current JSON data Object            varCDATA =Jsondata[idx]; //finds the index number in the result array, based on the primary key value            varIdvalue =Cdata[idfield]; varnum = Idindexdata[idvalue];//gets the array index number that stores the ID            if(num! =NULL) {Currecord=Result[num]; } Else {                //maintain complete structure information when initializing data avoid lack of data and the absence of specified column dataCurrecord = {}; }            //The data under the specified Colfields column is used as the y-axis, then the data for that column is taken as the y-axis             for(varIinchcolfields) {                varKey =Colfields[i]; //gets the value to Colfield as the column name                varValue =Cdata[valuefield]; Currecord[value]=Cdata[key]; //Store column namesResultcolumns[value] =NULL;  Break; }            //In addition to the data content, you need to add primary key dataCurrecord[idfield] =Idvalue; //Add an array if the object is new            if(num = =NULL) {Idindexdata[idvalue]= Result.push (Currecord)-1; }        }        //data Check because the row data is the column name, there may be some rows missing other column data, if missing, specify the default value         for(varIinchresult) {             for(varNameinchresultcolumns) {                if(!result[i].hasownproperty (name)) Result[i][name] =Emptyvalue; }        }        returnresult; }

　　

Full sample Download