JSON data transformation from table-like format to JSON?

Question

I have data that's in this format:

{
  "columns": [
    {
      "values": [
        {
          "data": [
            "Project Name",
            "Owner",
            "Creation Date",
            "Completed Tasks"
          ]
        }
      ]
    }
  ],
  "rows": [
    {
      "values": [
        {
          "data": [
            "My Project 1",
            "Franklin",
            "7/1/2015",
            "387"
          ]
        }
      ]
    },
    {
      "values": [
        {
          "data": [
            "My Project 2",
            "Beth",
            "7/12/2015",
            "402"
          ]
        }
      ]
    }
  ]
}

Is there some super short/easy way I can format it like so:

{
  "projects": [
    {
      "projectName": "My Project 1",
      "owner": "Franklin",
      "creationDate": "7/1/2015",
      "completedTasks": "387"
    },
    {
      "projectName": "My Project 2",
      "owner": "Beth",
      "creationDate": "7/12/2015",
      "completedTasks": "402"
    }
  ]
}

I've already got the column name translation code:

r = s.replace(/\%/g, 'Perc')
.replace(/^[0-9A-Z]/g, function (x) {
  return x.toLowerCase();
}).replace(/[\(\)\s]/g, '');

Before I dive into this with a bunch of forEach loops, I was wondering if there was a super quick way to transform this. I'm open to using libraries such as Underscore.

Answer 1

There is no easy way, and this is really not that complex of an operation, even using for loops. I don't know why you would want to use regex to do this.

I would start with reading out the column values into a numerically indexed array.

So something like:

var sourceData = JSON.parse(yourJSONstring);
var columns = sourceData.columns[0].values[0].data;

Now you have a convenient way to start building your desired object. You can use the columns array created above to provide property key labels in your final object.

var sourceRows = sourceData.rows;
var finalData = {
    "projects": []
};
// iterate through rows and write to object
for (i = 0; i < sourceRows.length; i++) {
    var sourceRow = sourceRows[i].values.data;
    // load data from row in finalData object
    for (j = 0; j < sourceRow.length; j++) {
        finalData.projects[i][columns[j]] = sourceRow[j];
    }
}

That should do the trick for you.

Answer 2

function translate(str) {
    return str.replace(/\%/g, 'Perc')
        .replace(/^[0-9A-Z]/g, function (x) {
            return x.toLowerCase();
        })
        .replace(/[\(\)\s]/g, '');
}

function newFormat(obj) {

    // grab the column names
    var colNames = obj.columns[0].values[0].data;

    // create a new temporary array
    var out = [];
    var rows = obj.rows;

    // loop over the rows
    rows.forEach(function (row) {
        var record = row.values[0].data;

        // create a new object, loop over the existing array elements
        // and add them to the object using the column names as keys
        var newRec = {};
        for (var i = 0, l = record.length; i < l; i++) {
            newRec[translate(colNames[i])] = record[i];
        }

        // push the new object to the array
        out.push(newRec);
    });

    // return the final object
    return { projects: out };
}

DEMO